Transaction Log Shipping

Recently I had to setup log shipping for our production environment. Although, this wasn’t entirely new for me, nonetheless this was a good refresher. I decided to go ahead and note the steps involved in configuring transaction log shipping and various available options.

Points to Ponder

Some of you may disagree but I feel these are important considerations, especially in your production environment where high availability is important.

In our environment, we have about 100 databases. There are two clustered environments and we host half databases on one cluster and the other half on the other cluster. Each cluster has 2 SQL Servers.

Our initial thought was to go ahead and setup log shipping in a way that the secondary server for the databases on one cluster will be the other cluster and vice-versa. It just didn’t feel right. Take for example, if your cluster 1 goes down then all load will end up on cluster 2 which may not be feasible or desirable.

So, we decided to setup a third environment just to act as a secondary cluster for both clusters. It may be overkill for some, but it certainly was a worthwhile investment for us.

What is Log Shipping?

Transaction log shipping is basically a way to automatically backup transaction logs, copy them to the secondary server and restore them in the database on the secondary server.

You can actually set it up yourself using SQL server agent and using the following steps…

         Create a SQL Agent Job to backup transaction logs for a specific database.

         As a second step in the same SQL Agent Job create an execute Xp_CmdShell to copy the transaction log files to the secondary server.

         Create a SQL Agent Job on the secondary server to restore the log files to a copy of the same database on the secondary server.  The database on the secondary server obviously has to be in “Restoring” mode.

But SQL Server takes care of all that for you. Using SQL Management Studio, you can simply walk through the steps to setup log shipping in just a few minutes.

Using SQL Management Studio to Setup Log Shipping

Below is a step by step process on how to setup log shipping for a specific database.

Note: Your database recovery model must be full or bulk logged in order to configure transaction log shipping.

         Right click on Database > Properties

         Select Transaction Log Shipping

o  Check enable this as a primary database in a log shipping configuration and then click on Backup Settings.

         Define the network path where you want to backup the transaction log. If the backup path is on the same server, also type the physical path, otherwise leave it blank. You can change settings such as delete files older than 72 hours and alert settings, but generally defaults are good. If you are short on space, you may want to lower the 72 hour threshold.

         You can change the schedule by clicking on the Schedule button. 15 minute default is generally good.

         Click OK and then from the main window click on “Add” button to configure secondary server. Click on connect to connect to the secondary server.

   The three tabs allow you to setup secondary database information and restore location. 
       There are three ways to setup a database on the secondary server.

•             Manually copy the primary database and restore on the secondary server. Remember to keep in Restoring mode.
•       Chose from one of two options on the first tab (see above) to restore the database to the secondary server.

  Second tab allows you to configure the restore location

       You can change the restore schedule by clicking on the Schedule button but default work just as well.

       On the last tab, you can set database recovery mode and alert settings.

      Click on OK and you have configured the transaction log shipping.

      You can optionally setup the Monitor Server, which allows you to monitor your transaction log jobs remotely. This is only useful when you have a separate monitor server, because history and status of the backup log operation is stored on the primary server and that of the restore operation is stored at the secondary server. Remote monitoring server allows the history and status of both copy and restore operations on the remote server. You can also configure alerts to fire if alert service fails.

      That’s all there is to it. One issue that I have seen is the permissions issue. Make sure the user under which SQL agent runs has the read/write permissions on both primary and secondary servers.