Log shipping was first introduced in SQL
Server 2000, although many DBAs implemented it before then using a
series of custom jobs. Put simply, log shipping automates the backup,
copy, and restore of a source database's transaction logs to one or
more secondary server instances seeded with a full backup of the source
database restored in recovering mode.
A
commonly referenced backup best practice is to restore backups on a
regular basis to ensure they're valid. Until they're restored you can't
be 100 percent sure that they'll work in an emergency restore scenario.
The problem a lot of organizations have with this recommendation is the
time and equipment required to carry it out. Manually copying and
restoring backup files on the off chance that something may be wrong
isn't the most rewarding of tasks, and as a result, it's often shelved
as a good idea for implementation at a later point, once everything
else is done—which of course never happens.
The
best way of implementing this recommendation is to automate it as part
of an infrastructure solution.
As
we covered earlier, transaction logs are a crucial component in the
ability to recover a database to a point in time, and a broken log
chain destroys this ability. Perhaps the best method for automating the
restore of transaction logs, and therefore validating the log chain, is
through the use of transaction log shipping.
Later
in this section we'll walk through the process of establishing a log
shipping session, which will highlight its various components and
advantages. Before we do that, let's examine some of the common log
shipping usage scenarios.
1. Usage scenarios
In
addition to the major benefit of constant, automatic log chain
validation, log shipping provides a number of other benefits, making it
a commonly used solution for a variety of scenarios.
Offsite recovery point
Log
shipping can be used to provide an offsite, up-to-date copy of a
mission-critical database. In the event of total environment
destruction, the offsite copy can quickly restore any remaining logs
and be brought online and available for application usage.
Compared
to database mirroring, log
shipping has a much greater transaction latency. The default frequency
of the log backup, copy, and restore jobs is 15 minutes, so the
secondary database will typically be between 30 and 45 minutes behind
the primary. While at first this may appear to be a weakness of log
shipping, in some instances it presents a stronger solution than
database mirroring.
Consider
a situation in which a table is accidentally (or maliciously) dropped.
In the case of database mirroring, by the time the error was realized,
the change would probably already exist on the mirror. With log
shipping, the restore containing the error can be canceled, or restored
just prior to the error. Think of this as a 7-second broadcast delay.
Reporting solution
As we'll see shortly, when restoring the transaction logs on the secondary database, the standby
option can be invoked. This option enables the secondary database to be
used for read purposes in between log restores. Together with the
ability to schedule the restores, this can enable the database to be
used for reporting purposes for certain periods of the day.
The
major benefit of log shipping when compared to database mirroring is
that a log shipping source can be shipped to multiple destinations. For
example, one destination could be used as a reporting source and
another destination set up as a dedicated failover point. Each can have
its own copy and restore schedules. Further, each destination can be in
different physical locations from the source for further geographical
failure resilience.
Upgrade solution
Log shipping can be set up between one version of SQL Server and the
next. For example, a SQL Server 2005 source database can log ship to a
2008 destination. Such a configuration could be used to minimize the
upgrade time in the following way:
A new server can be installed, configured, and tested as required with SQL Server 2008 loaded and configured.
A full backup of the source 2005 database is made and copied to the new server.
The 2005 database is restored to the new 2008 instance on the new server in recovering mode.
Log shipping is set up between the old and new server instances with transaction logs shipping on a regular basis.
When
the upgrade time arrives, users are disconnected from the 2005
instance, remaining logs shipped to the 2008 instance, and the database
is then recovered with applications reconfigured to point at the new
instance.
Until step
5, the original 2005 instance continues to run with no user
interruption. The only downtime comes in step 5, which will typically
be very short given the small number of transactions to ship across.
The other benefit of this approach is that the original 2005 server
still exists as a rollback point with its data still accurate as at the
time of the last transaction log ship.
This
solution could also be used when upgrading or decommissioning server
hardware, but like database mirroring, log shipping is implemented on a
database-by-database basis, so if a given server has many databases, it
may not be the best approach.
To further
highlight the options and benefits of log shipping, let's walk through
an implementation using the SQL Server Management Studio tool.
2. Setting up and monitoring log shipping
You
establish a log shipping session in SQL Server Management Studio by
right-clicking a database and choosing Tasks > Ship Transaction
Logs. Note that the selected database must be using either the Full or
Bulk_Logged recovery model. As shown in figure 1,
the resulting screen provides the option "Enable this as a primary
database in a log shipping configuration." If the database is already
log shipped, you can deselect this option to remove log shipping from
the database.
After
choosing the option to enable the database for log shipping, click the
Backup Settings button. In the resulting screen, shown in figure 2,
you can set a number of properties, such as the transaction log backup
destination (including a file share accessible from the log shipping
destination server), the log backup retention period, the backup
frequency, and whether to use backup compression.
Once you complete these settings, click OK to return to the screen shown earlier in figure 1.
The next step is repeated for each required log shipping destination.
In our example, we'll do this once for a simple 1:1 primary:secondary
relationship.
Click Add under Secondary
databases to specify a log shipping destination. In the resulting
screen, you can specify a secondary server instance. On the Initialize
Secondary Database tab, you're given three options, as you can see in figure 2.
For
our example, we've chosen the third option, "No, the secondary database
is initialized." We've done this because a full backup of the source
database has already been restored in NORECOVERY mode on the
destination server. Alternatively, we could choose the first option,
which will automate the full backup, copy, and restore process, or the
second option, if we want to restore a preexisting backup.
Next,
click the Copy Files tab, where you can specify where the log backup
files will be copied to and how frequently the copy will take place. As
shown in figure 4, this tab also contains the option to automatically remove copied log backup files after a certain time period.
Finally, click the Restore Transaction Log tab, shown in figure 5,
and select a restore mode and frequency. The default restore mode is No
Recovery Mode, which leaves the database in the recovering status, and
therefore unable to be used. For our example, we've chosen Standby
Mode, along with the option to disconnect users. These options enable
the log shipped database to be used in read-only mode in between log
restores for reporting purposes.
Note
that when using standby mode, you must take into account the restore
frequency. The default restore frequency of 15 minutes is inappropriate
for a reporting solution, as users would be disconnected (if the
disconnect option is chosen) every 15 minutes to enable the log
restore. For our example, we've chosen a 12-hour restore frequency.
This will enable 12 hours of uninterrupted reporting before the next
restore, and assuming users are advised on the restore times, such a
configuration may be quite acceptable.
Regardless
of the restore frequency, the backup and copy jobs have their own
independent schedules. In our example, the backup and copy jobs run
every 15 minutes. If the secondary database is required in an
emergency, the restore process—accessible via a SQL Server Agent job
created by the log shipping setup process—can be manually executed at
any time, which will restore any outstanding transaction logs and thus
bring the secondary server up to date.
Once you complete these steps, click OK to return to the screen shown in figure 1.
The other log shipping component for consideration is the monitoring
instance, which will actively monitor (via SQL Server Agent jobs) the
backup, copy, and restore progress. If any of these events don't run
within the specified intervals, the monitoring server generates alerts,
which can be configured to notify the appropriate DBAs.
|
Although
the primary or secondary log shipping instance can also be the
monitoring instance, using an independent instance for monitoring is
recommended. In such a configuration, the load on the primary and
secondary instances is reduced, and monitoring continues in the event
of failure on either the primary or secondary instance. Further, a
monitoring instance can monitor more than one log shipping
configuration.
|
After
deciding whether to use a monitoring instance, save the log shipping
configuration for later use by clicking the Script Configuration
button. SQL Server implements the backup, copy, restore, and monitoring
steps as SQL Server Agent jobs, requiring this service to be running on
all instances (primary, secondary, and monitoring) to enable log
shipping to function.
Finally, in order to
determine the status of the log shipping session, SQL Server includes a
number of options, one of which is the built-in transaction log
shipping status report, as shown in figure 6.
Access this report by right-clicking on a server involved in a log
shipping role (primary, secondary, or monitor) and select Reports >
Standard Reports > Transaction Log Shipping Status.
Note that this report contains additional information not shown in figure 6, including the filename of the last file backed up, copied, and restored.
If
you're using log shipping to maintain a warm standby server for
failover purposes (as opposed to a pure reporting server solution),
then one of the important tasks is the failover and role reversal
process.
3. Failover and role reversal
If
the primary instance in a log shipping solution fails (or a manual
failover is required for planned maintenance), the failover process is
largely a manual effort, requiring you to back up, copy, and restore
outstanding logs to the secondary instance before configuring log
shipping in the reverse direction. This process can be summarized as
follows:
Disable the log shipping backup, copy, and restore SQL Server Agent jobs.
Manually run the backup, copy, and restore SQL Server Agent jobs to push across any outstanding transaction log backups.
Back up the transaction log on the primary instance using the WITH NORECOVERY
option. This will place the database in the recovering mode, enabling
it to receive logs from the secondary server once the roles are swapped.
Restore the backup from step 3 on the secondary instance using the WITH RECOVERY
option. This will place the secondary database in read/write mode and
make it available for use. At this point, you will need to manually
redirect client connections to the secondary instance.
Set
up log shipping in the reverse direction, ensuring that the "No, the
secondary database is initialized" option is selected, as shown in figure 3
earlier. At this point, there will be a set of SQL Agent jobs for the
new configuration as well as disabled jobs for the old configuration.
Leaving the old disabled jobs in place makes future role changes
easier—you simply disable one set, enable another, and ensure tail log
backups are applied in the appropriate direction. At this point, the
log shipping roles are reversed.
One
of the real benefits of log shipping is its simplicity. The core
components of log shipping are fundamental SQL Server tasks:
transaction log backups and restores. Other than running out of disk
space, there isn't a lot that can go wrong, unless someone makes (and
removes) an additional transaction log backup without the COPY_ONLY
option . That being said, it's
not without its limitations; the primary one is the manual effort
required to change roles and reconnect clients. In that regard,
database mirroring offers a superior solution.
