How to Perform SQL Server Log Shipping

By : Apr 02, 2002

The Need for Standby Servers

In a perfect world we wouldn't need standby servers for our SQL Servers. Our hardware would never fail, NT Server 4.0 or Windows 2000 would never blue screen, SQL Server would never stop running, and our applications would never balk.

In a partially perfect work, we could afford very expensive clustered SQL Servers that automatically failover our wounded and dead production SQL Servers, reducing our stress and keeping our users very happy.

But for most of us, the closest thing we can afford to implement when it comes to SQL Server failover are standby servers that we have to manually fail over. And even some of us can't afford this. But for this article, I am going to assume that you can afford a standby server.

The concept of standby servers is not a new one. It has been around a long time and been used by many DBAs. Traditionally, using a standby server for failover has involved manually making database and log backups on the production server and then restoring them to the standby server on a regular basis. This way, should the production server fail, then users could access the standby server instead, and downtime and data loss would be minimized.

This article is about log shipping, a refined variation of the traditional manual standby failover server process. Its two major benefits over the traditional methods is that it automates most of the manual work and helps to reduce potential data loss even more.

 

What is Log Shipping

Essentially, log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that is will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in "synch". Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, its not really that easy, but it comes close if you put enough effort into your log shipping setup.

 

Benefits of Log Shipping

While I have already talked about some of the benefits of log shipping, let's take a more comprehensive look:

• Log shipping doesn't require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server. Just keep in mind that if you do need to fail over, that this server will have to handle not one, but two loads. I like to make my standby server a development server. This way, I keep my developers off the production server, but don't put too much work load on the standby server.

• Once log shipping has been implemented, it is relatively easy to maintain.

• Assuming you have implemented log shipping correctly, it is very reliable.

• The manual failover process is generally very short, typically 15 minutes or less.

• Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed.

• Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.

 

Problems with Log Shipping

Let's face it, log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints. Some of the problems with log shipping include:

• Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs.

• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.

• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.

• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.

• When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.

 

Log Shipping Overview

Before we get into the details of how to implement log shipping, let's take a look at the big picture. Essentially, here's what you need to do in order to implement log shipping:

• Ensure you have the necessary hardware and software properly prepared to implement log shipping.

• Synchronize the SQL Server login IDs between the production and standby servers.

• Create two backup devices. One will be used for your database backups and the other will be used for your transaction log backups.

• On the production server, create a linked server to your standby server.

• On the standby servers, create two stored procedures. One stored procedure will be used to restore the database. The other stored procedure will be used to restore transaction logs.

• On the production server, create two SQL Server jobs that will be used to perform. the database and transaction log backups. Each job will include multiple steps with scripts that will perform. the backups, copy the files from the production server to the standby server, and fire the remote stored procedures used to restore the database and log files.

• Start and test the log shipping process.

• Devise and test the failover process.

• Monitor the log shipping process.

Obviously I have left out a lot of details, but at least now you know where we are headed.