Deploy SQL Server Log Shipping to Create a Warm Standby Server

By Steven Wort, Ross LoForte, Brian Knight on January 27, 2013


A warm standby server involves creating a full backup and periodic transaction log backups at the primary server, and then applying those backups, in sequence, to the standby server. The standby server is left in a read-only state between restores. When the standby server needs to be made available for use, any outstanding transaction log backups, including the backup of the active transaction log from the primary server, are applied to the standby server and the database is recovered. A common log-shipping scenario is creating a warm standby server whereby the log-shipping secondary server is located close to the primary server. If the primary server goes down for planned or unplanned downtime, the secondary server takes over and maintains business continuity. Then, the DBA may choose to failback to the primary server when the primary server becomes available.

It is simple to configure a warm standby server with log shipping because it uses the dependable transaction log backup, operating system copy file, and transaction log restore. In most warm standby scenarios, you should configure the log-shipping jobs to execute at a shorter interval to maintain the secondary server closely in sync with the primary server, to reduce the amount of time to switch roles, and to reduce data loss. Additionally, to further limit data loss, if the active portion of the primary server’s transaction log is available, the secondary server would be restored to the point in time of the failed primary server.

However, in some situations, the active portion of the transaction log may not be available when the storage where the transaction log resided on is not accessible, or some transaction log files that were in transit may not have made it to the secondary server, causing some data loss. In a typical role-switch scenario, you would recover all in-transit transaction logs and the active portion of the transaction log before recovering the secondary server. Users would also need to be redirected because log shipping, unlike Windows failover clustering, has no automatic user redirect.

Sometimes, when performing a failback, log shipping is used in place of Windows failover clustering because it is a less expensive solution; for example, clustering requires a shared disk system that an organization may not own. Log shipping does not have such hardware requirements, so an organization may already own hardware that is not failover — cluster-compatible that can be used for log shipping. Moreover, in log shipping, the primary and secondary databases exist on separate servers. This is a shared-nothing environment. Windows failover clustering uses one shared disk system with a single copy of your database on the shared disk, which could become corrupted.

NOTE

Unlike clustering, log shipping failover is always a manual process. This means you must initiate and monitor the status of the failover process and update the client application’s connection strings to the new primary. When you use Windows Clustering for SQL Server, the monitoring and failover is done automatically. When you determine that you must failover to the secondary log shipping server, the execution of the steps can be manual or automated. If you choose automation, then you must create scripts that do work on your behalf. Examples of these scripts are covered later.

Another difference between log shipping and clustering is that clustering protects the whole SQL Server instance. In failover clustering, all databases on the instance are included in the solution. Log shipping is done per database. You can even log ship from one database to another on the same server. If you need high availability for all the databases on a server, you can achieve it using either clustering or log shipping. If you choose log shipping, you have to set it up for each database on the server. If you want some, but not all, of the databases on a server to be highly available, log shipping is the best choice.

Related Posts

Leave a Reply