Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1072
Part VI Enterprise Data Management
■ The switch between primary and warm standby server is not transparent. A series of steps
must be manually executed by the DBA on the warm standby server, and front-end appli-
cation connections must redirect the data source and reconnect to the warm standby
server.
■ Once the primary server is repaired, returning to the original configuration may require
manual DBA intervention.
If these issues are acceptable, log shipping to a warm standby server can be an excellent safeguard
against downtime.
Best Practice
I
deally, the primary server and the warm standby server should be in different locations so that a disaster
in one location will not affect the other. In addition, log shipping can place a large demand on a network
every few minutes while the transaction logs are being moved. If the two servers can be connected with
a private high-speed network, log shipping can take place without affecting other network users and the
bandwidth they require.
Defining Log Shipping
In SQL Server 2000, log shipping was available only in Developer and Enterprise Editions. Starting with
SQL Server 2005, log shipping is available in Workgroup, Standard, Developer, and Enterprise Editions.
Developer Edition can be used only for development purposes and not for production.
Microsoft provides a simple-to-use log shipping wizard to create a maintenance plan to back up, copy,
and restore the transaction log from the primary server to the warm standby server every few minutes.
Log shipping has built-in monitoring that makes it very easy to maintain and troubleshoot.
Log shipping normally involves three SQL Servers: a primary server, a warm standby server, and a mon-
itor server, as shown in Figure 46-1.
■ The primary or source server is the main production SQL Server to which clients connect. This
server contains the log shipping database. The initial full database backup and subsequent
transaction log backups are taken on this server. This server should be a high-quality server
with redundant disk drives.
■ The warm standby server is the backup SQL Server, otherwise known as the secondary server.If

the source server fails, it becomes the primary server. This server should be capable of meeting
the minimum performance requirements during a short-term crisis. If your business does not
allow any performance degradation, then the warm standby server should be similar to the
primary server.
1072
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1073
Log Shipping 46
FIGURE 46-1
Typical log shipping configuration
Monitor Server
Primary Server Warm Standby Server
Backup
Transaction Log
Copy Transaction Log Backup
Restore
Transaction Log
■ The monitor server polls both the primary server and the warm standby server by keeping
track of what files have been sent where, generating an alert when the two are out of sync. A
single monitor server can monitor multiple log shipping configurations. The monitor server
is optional. If a monitor server is not used, the primary and warm standby servers store the
monitoring information.
Best Practice
T
he monitor server can be an instance on the destination server, but locating the monitor server on the
source server would be a self-defeating plan. If the source server physically failed, the monitor server
would also fail. The best practice is to assign a monitor server to its own hardware to avoid disrupting
monitoring in the event that the primary or warm standby server is lost.
Each primary server database can have only one log shipping plan, and each plan can ship only one
database. However, a plan may ship to multiple secondary servers.

1073
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1074
Part VI Enterprise Data Management
Configuring log shipping
Log shipping can be configured using one of two methods: either by using SQL Server Management Stu-
dio or by using system stored procedures.
Pre-log shipping configuration
With either method of configuration, the following prerequisites need to be completed before configur-
ing log shipping:
■ Disk space needs to be created and shared. This network share is used by the backup job on
the primary server to store the transaction log backups. Grant read and write permissions on
the network share to the SQL Server service account on the primary server, and read permis-
sions to the proxy account for the copy job (usually the SQL Server Agent service account) on
the secondary server.
■ The destination folder needs to be created on the secondary servers. The copy job on the
secondary server copies the transaction log backups from the network share to the destination
folder on the secondary server. The load job then restores these transaction log backups from
the destination folder. The SQL Server service accounts on the secondary server need to have
read and write permissions on this folder.
■ The recovery model of the log shipping database must be set to full or bulk-logged.
■ The edition of the SQL Server 2008 participating in log shipping needs to be Enterprise,
Standard, or Workgroup Edition.
■ If the primary and secondary servers are on different domains, then set up two-way trusts
between the domains. If this is not possible, you can also use network pass-through security.
With network pass-through security, the SQL Service accounts for all the SQL Servers partic-
ipating in log shipping use the same network account and the same password, and enough
permission to complete the log shipping tasks.
■ If you have a very large database, then I recommend taking a full database backup, copying
it to the secondary server, and restoring it on the secondary server with

NORECOVERY or
STANDBY to put it into a state that will allow restoring the transaction logs. NORECOVERY
mode will not allow any database access to the secondary database, whereas STANDBY mode
allows read-only access to the secondary database.
Configuring log shipping using Management Studio
The following steps create a log shipping configuration using SQL Server Management Studio:
1. In the Object Explorer on the primary server in SQL Server Management Studio, right-click on
the database that will be log shipped and review the database properties.
2. On the Options page, verify that the recovery model is either Full or Bulk-logged and not
Simple.
3. On the Transaction Log Shipping page, shown in Figure 46-2, check the box that enables log
shipping configuration.
1074
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1075
Log Shipping 46
FIGURE 46-2
Enabling the primary database for log shipping
4. Configure the backup settings as shown in Figure 46-3 by clicking the Backup Settings button.
Enter the network share where the transaction log backups will be stored before being copied
to the secondary server. If the backup folder is local to the primary server, then enter the local
folder path too.
A network share that is not located on the primary server will better protect the transac-
tion logs in case of a hardware failure on the primary server.
5. Enter an amount of time after which the transaction log backup files should be deleted. For
example, if the files should be deleted after one day, then the ‘‘Delete files older than’’ option
should be set to one day.
1075
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1076

Part VI Enterprise Data Management
FIGURE 46-3
Configuring transaction log backup settings for log shipping
6. Enter an amount of time that the server should wait to send an alert if no new transac-
tion log files are found. For example, if the server has not seen a transaction log backup
in the past one hour, then the ‘‘Alert if no backup occurs within’’ option should be set to
one hour.
The longer the length of the alert time, the higher the risk. With a long alert setting, a
transaction log backup failure will result in a larger amount of data loss.
7. Schedule the job that will back up the transaction log by setting the job’s name, time, and
frequency by clicking the Schedule button. A shorter duration between transaction log backups
will minimize the amount of data that could be lost. By default, the transaction log is backed
1076
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1077
Log Shipping 46
up every 15 minutes. The default works for most environments but some of my clients have
changed it to 5 minutes to minimize data loss. The frequency of transaction log backups
usually is determined by several factors, including service-level agreements, speed of your disk
subsystem, and transaction log size.
Make sure that the only transaction log backup that occurs is scheduled through the Trans-
action Log Shipping page. Otherwise, all the data changes will not be propagated to the
secondary servers and log shipping will break.
If you have a very powerful server with plenty of resources, you may be tempted to change the
transaction log backup frequency to every 1 minute or less. SQL Server 2008 lets you schedule the
frequency to every 10 seconds. While it is possible that you may need this in your environment,
remember that this creates hundreds of transaction log backups. If you have to restore your database
using backups, then you will have to restore the full database backup followed by all the transaction
log backups in order; and if one of the transaction log backups is bad, then the restore will stop at
that point.

8. SQL Server 2008 Enterprise Edition supports backup compression. You can control the
backup compression by clicking the ‘‘Set backup compression’’ drop-down box on the Trans-
action Log Backup Settings page. By default, ‘‘Use the default server setting’’ option is selected.
This uses the default server-level compression. You can bypass the server-level default by
selecting the ‘‘Compress Backup’’ option or you can choose not to compress the backup by
selecting the ‘‘Do not compress backup’’ option.
The performance increase achieved from backup compression comes at the expense of
CPU usage. If you have a CPU-bound SQL Server, then you may not want to compress the
backup. Thorough testing is recommended to determine the impact of the backup compression, as the
CPU increase can impact other operations.
9. Add the secondary servers to the transaction log configuration by clicking the Add button
under the secondary instance’s window. Multiple secondary instances can be added here by
repeating steps 9 through 16.
10. On the Secondary Database Settings screen, shown in Figure 46-4, connect to the server that
will be the secondary server and enter the database name for the secondary database. If the
database is not there it will be created.
11. Initialize the secondary database by selecting either the option to have log shipping cre-
ate a full database backup and restore it on the secondary server or the option to have
it use the last known backup. If you select to use the last backup that was created, the
name of the directory in which the backup is located needs to be supplied. To create the
data and log files on non-default folder locations on the secondary server, click the but-
ton ‘‘Restore Options’’ and enter the local folder path on the secondary server where you
want the data and log files to exist. The previous two options are best suited for smaller
databases. If you have a very large database, it is recommended that you bypass the wiz-
ard and manually take a backup of the database, copying it to the secondary database and
restoring it. If you take this approach, select the third option, ‘‘No, the secondary database is
initialized.’’
1077
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1078

Part VI Enterprise Data Management
FIGURE 46-4
Configuring the secondary server database for log shipping
12. The Copy Files tab, shown in Figure 46-5, configures the copy job on the secondary server
that copies the transaction log backups from the network share to the destination local folder
on the secondary server. This tab also has a setting that enables files to be deleted after a
designated amount of time.
13. On the Copy Files tab, enter the local folder on the secondary server to which the transaction
log files will be copied. The proxy account for the copy job (usually the SQL Server Agent
service) on the secondary server must have read and write permissions on this folder.
14. On the Restore Transaction Log tab, shown in Figure 46-6, choose either No recovery mode or
Standby mode. Standby mode allows access to the secondary server for read-only operations.
Select this mode if you want to use log shipping for reporting. If the standby mode is selected,
the option to have the user connection killed during the transaction log restore is available.
If you do not choose to disconnect the users, the transaction log backups will fail and the
secondary server will lag behind. The No recovery mode option will not allow any database
access to the secondary database. This option is usually selected when log shipping is used for
disaster recovery or high-availability scenarios.
1078
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1079
Log Shipping 46
FIGURE 46-5
Configuring the copy job on the secondary server
To use log shipping as a reporting solution you need to select Standby mode in the Restore
Transaction Log tab. The restore job needs exclusive access to the database and will fail
if users are running reports. You may select the option to disconnect the users in the database when
restore runs, but this means that longer-running reports may never complete. For example, if you have
a restore job that runs every 15 minutes and you have a report that takes 25 minutes to complete, that
report will always be killed by log shipping every 15 minutes.

Conversely, you may configure the restore job to occur every few hours, but in that case the secondary
server will lag behind the primary server. Because log shipping allows multiple secondary servers, you
may have two secondary servers. Set one to no recovery mode and schedule the restore job to run every
15 minutes or earlier, and set the recovery mode to standby mode on the second secondary server.
Then, schedule the restore job to run every few hours. This way you will have two copies of your
primary database and can use log shipping for a high-availability and reporting solution. If near real-time
data is required for reporting, I recommend using transactional replication.
1079
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1080
Part VI Enterprise Data Management
FIGURE 46-6
Configuring the restore transaction l og job on the secondary server
15. On the Restore Transaction Log tab, the option for delaying a restore and alerting is avail-
able as well. This configuration option enables all the transaction log backups to be held
until the end of the business day or to apply the transaction logs as soon as they are
received.
16. The option for more granularities on restores and when they are applied are set in the Restore
job by clicking the Schedule button. By default, transaction log backups are restored every 15
minutes on the secondary server.
17. Click OK to complete the secondary database setup, and return to the database’s Properties
tab.
1080
www.getcoolebook.com
Nielsen c46.tex V4 - 07/21/2009 3:17pm Page 1081
Log Shipping 46
18. Once the secondary database configuration has been completed, a monitor server can be con-
figured on the primary database’s Properties page by checking ‘‘Use a monitor server instance’’
and clicking the Settings button as shown in Figure 46-7.
As noted previously, adding a monitor server is optional. But if you do not add a monitor

server now you cannot add it later. And if you add a monitor server now, it cannot be
changed without removing log shipping first.
FIGURE 46-7
Configuring the monitor server for log shipping
1081
www.getcoolebook.com