Skip navigation
poor mans empty pockets

Poor-Man's Disaster Recovery: Use Log Shipping for SQL Server (at Your Own Risk)

This week, we'll take a closer look at part 2 of our series on application high availability and disaster recovery (HA/DR) for SQL Server: Log shipping for SQL Server implementations.

Related: Application High Availability and Disaster Recovery for SQL Server, Part 1

Log shipping is a low-cost (poor-man) solution that provides disaster recovery protection at the database level. A log shipping configuration includes one primary server and one or more secondary servers. A database is backed up and restored from the primary server to the secondary server. At regularly scheduled intervals, a transaction log backup and restore is performed at the primary and secondary server to keep the servers in sync.

To configure log shipping, you can use the following references:

  1. Log Shipping Deployment How-to Topics (Database Engine)
  2. How to Configure Log Shipping for SQL Server

Although log shipping for SQL Server is not too difficult to setup and configure, it's important to keep a few things in mind:

  • SQL protection is at the database level only. This is good if you only have a few databases you'd want to protect in case of a disaster. If you have many databases and/or you want to provide protection at the instance level, log shipping is not adequate.
  • Data loss can occur. Data is recovered only up to the last transaction backup and restore. Log shipping is a per-schedule and asynchronous solution. The data on the secondary server is never real-time.
  • Failover must be manually initiated on the secondary server.  There is not a monitor agent that coordinates the failover/back. You essentially decide when to bring the database(s) online on the secondary server.
  • A log shipping process is always unidirectional. Reverse log shipping must be configured as a new log shipping process to ship data from the secondary server back to the primary server.  If you require failback for your database, you must set up new log shipping to ship data from the secondary server to the primary server.
  • SQL logins and security must be manually configured on the secondary server. This is the only way to allow users to reconnect and get access to the database after a failover. The primary and secondary servers are unique servers with their own instances and databases. To keep the logins in sync, you can use the article How to transfer logins and passwords between instances of SQL Server to transfer the logins from the primary server to secondary server.
  • Normal maintenance plans must be configured manually on the secondary server for the log shipped database. Each server plays a different role in this log shipping configuration, so the active maintenance plan configured on the primary server must be duplicated on the secondary server. However, the maintenance plan should only be active if the database is actually active on the server.
  • External database dependencies or chaining must be manually configured on the secondary server to achieve "normal" operation after failover. If the database requires a third-party agent solution, the agent solution must also be installed and configured on the secondary server. Licensing fee for the third-party agent could be a challenge here.
  • Applications must be reconfigured to reconnect to the secondary server as a new SQL Server instance after a failover. The primary server and secondary server are two different servers, each with its own hostname. This can be cumbersome if you have many end-user applications to redirect after a failover. This can be a main drawback of not having an immutable connection string.
  • Servers can be locally or geographically dispersed, but. . . Latency can be impactful if the connection between the primary server and the secondary server is over a slow WAN, especially if you have a large database or transaction log backup to ship to the secondary server.
  • Servers can be any class of hardware or software. Any commodity servers can be used for the primary or secondary server. They don't have to be the same class or version. The same applies for software running on these servers. As long as the database is correctly configured for log shipping from primary to secondary server, you have yourself a low-cost disaster recovery for your database.

Log shipping is an easy and ideal solution for off-site reporting or non-real-time disaster recovery at the database level. With a built-in scheduling and retention plan, administering and managing a database log shipping process would be a breeze. But if you need to provide protection for an entire instance of SQL Server or require near-real-time protection for your databases, you should consider other HA/DR solutions.

I hope you find the log shipping information above useful and will join me again in the next installment of the Application High Availability and Disaster Recovery (HA/DR) for SQL Server series, where I will discuss another HA/DR solution.

Do you have a specific question about HA/DR solutions? If so, please let me know and I'll try to provide insight and solutions. Cheers!

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.