SQL Server Log Shipping -- Complete Overview.. Part - VII

Next Chapter »

Scheduling the Log Backup Job

Once this multi-step job is created, the next step is to schedule it. Scheduling this job is more complicated that scheduling the database backup job. There are two key decisions you will have to make. First, you must decide how often you want to do a log backup. Generally, the more often, the better, as less data is subject to loss. I generally perform a log backup every 15 minutes. Another way of saying this is that I am doing log shipping every 15 minutes.

The second aspect of scheduling is to prevent your scheduled log backups from interfering with your database backups. If they get out of sync with each other, the transaction log restores will fail, and you won’t have a current backup of your database on your standby server. To prevent any potential conflict, what I do is schedule the log backup job to stop 15 minutes before the database backup begins, and then to restart 15 minutes after the database backup is run. This way, there should be no scheduling conflicts.

Test, Test, and Test Some More

We are almost done. While all the steps are now in place, the last step is to test everything over a period of several days to be sure the jobs and stored procedures work properly. Since each job keeps its own success and failure history, it is not to hard to debug any job-related problems you run across.

Before you add log shipping to your production server, I suggest you set it up and test it first on two non-production SQL Servers if you can. This will prevent any of your learning mistakes to bring down your production server.

Don’t think that a successful log shipping solution eliminates the need for storing backups to tape. Besides everything I have described in this article, I still back up all of my database and log disk backups to tape on a daily basis. There is no such thing as having too many backups.

Monitor Everything

Once you have your log shipping process in place, be sure to monitor it for success. Be sure to set up job notifications so that you are notified by e-mail or pager if either of your two log shipping jobs fail.

How to Failover to the Standby Server

Once log shipping is successfully implemented, you will want to create your own scripts to implement an actual failover to the standby server, and then you will want to test your process to see that it works successfully.

Since I am not familiar with your specific environment, I cannot provide you an exact set of steps to follow when failing over your production server to your standby server, but what I can do is list some of the most common things you will need to consider. They include:

• Are you able to recover the transaction log on the production server? If so, then you may want to run a script similar to this one in order to back it up:

             BACKUP LOG database_name TO database_name_log_backup_device WITH INIT,
             NO_TRUNCATE

• If you have recovered the transaction log on the production server, you may want to restore in on the standby server using a script similar to this one:

             RESTORE LOG database_name
             FROM DISK = ‘d:sql7logdatabase_name_log_backup_device.bak’
             WITH
             DBO_ONLY,
             STANDBY = ‘d:sql7undo_database_name_log.ldf’

• Whether or not you were able to recover the transaction log or not from the production server, you will need to run a script similar to the one below in order to “recover” the standby server and turn it into your production server.

             RESTORE DATABASE database_name WITH RECOVERY
        EXEC SP_DBOPTION ‘database_name’, ‘read only’, ‘false’
        EXEC SP_DBOPTION ‘database_name’, ‘dbo use only’, ‘false’

• Once your standby server has been failed over, the next step is to re-point your clients to it. How you do this will depend on your clients. As a last resort, you can always rename your standby server to the name of your production server, and change the IP address, but this solution often creates problems of its own. You will just have to test to find the best way to re-point your clients to your SQL Server. 
• Another aspect of the failover process you will want to plan for is how you will make the move back from the standby server to the production server once the production server is working again.

As you can see, setting up log shipping involves a lot of detailed steps. If you want to give it a try, try to allocate the largest part of a couple of days to implement and test it. But once it is going, I think you will find it a great time saver when your production server goes down.