Database Reference
In-Depth Information
Figure 31-8.
Log Shipping
Log shipping does not protect against data loss. Log backups are done on schedule and, if the transaction log on
the primary server was corrupted, you would lose all changes since the last log backup.
Log shipping is often used together with other high availability technologies. One of the common scenarios is
using it with a Failover Cluster Instance, shipping the log to the secondary servers in remote off-site locations.
This provides geo-redundancy for the data tier in the systems at a low implementation cost.
Log shipping is also useful in scenarios when you purposely do not want to have up-to-date data on the
secondary servers. This could help to recover data from accidental deletions on the primary server.
There is no automatic failover support with log shipping. Manual failover consists of a few steps. First, you need
to disconnect users from the database and, perhaps, switch the database to
RESTRICTED_USER
or
SINGLE_USER
mode
to avoid client connections during the failover process. Next, you need to backup the remaining part of the log on
the primary server. It might be beneficial to use the
NORECOVERY
option during backup if you expect to fail back to the
primary server later. Finally, you should apply all remaining log backups on the secondary server, and recover the
database to bring it online. Obviously, you should also change the connection strings to point to the new server.
Secondary servers keep the database in RESTORING state, preventing clients from accessing it. You can work
around this by using the
STANDBY
option, which gives you read-only access to the database. However, clients will lose
connectivity during the time when log backups are restored. You should also consider the SQL Server licensing model,
which requires you to purchase another license when the server is used for anything but supporting high availability.
You should design a log shipping strategy and backup schedule in a way that allows you to avoid a backlog when
log backups are transmitted over the network and restored slower than they generated.
