Log Shipping Configuration - Things to watch out

Recently I came across a situation where a server was configured to log ship. Everything was setup properly and the primary server had security rights to copy transaction log files to the secondary server.

Once configured, log shipping would work for a day and restore the logs to the secondary database as you would expect, but then it will start failing. Based on the error message, it was clear that log shipping chain was being broken somehow.

The person in charge of the database management had disabled differential backups erroneously thinking they were somehow breaking the log chain.

Remember that full backups and differential backups do not break transaction log chain. The only way you can break the log chain is either by switching the database to simple recovery model (which will truncate the log and break the chain) or if you take a backup outside of the log shipping backup process.

Log shipping consists of the following main operations:

• Backup Transaction Log
• Copy it to the secondary destination server (You may configure so that the backup file is created on the secondary server and this step won't be necessary).
• Restore the Transaction Log to the secondary database (on the secondary database).

Log shipping simply creates a transaction log backup from the last log based on LSN and restore must happen in that order, i.e. previous log must be restored first and so on. Naturally, if an outside process takes a transaction log backup, it will break the log chain and any log restores without this backup will fail. The only way to recover would be to find the missing log backup or start all over.

Indeed that was the case here. Even though there wasn't another transaction log backup process, there was a shrink log file job configured to run nightly and as part of this process, the job was first creating a backup and then shrinking the log file.

Transaction log backups will automatically truncate the log file (although it may not happen right away) but file generally won't shrink, so if you are low on disk space it is good practice to shrink the log file periodically, but you shouldn't be taking the backup, if you already have transaction log backup or log shipping configured elsewhere.

To know more about transaction log shipping and how to configure it, check out this post.

Thank you.

Database Recovery Models

In my previous post, we talked about transaction log shipping and that for transaction log shipping, your database must be set to Full or Bulk-Logged recovery model. Recovery models specify the way SQL Server manages log files. There are three types of recovery models..

• Simple Recovery Model
• Full Recovery Model
• Bulk-Logged Recovery Model

Simple Recovery Model

As the name implies, simple recovery model is just that, simple. SQL Server maintains transaction log only until the database reaches a transaction checkpoint. Each time the database reaches a transaction checkpoint, SQL Server truncates the transaction log. Hence, point in time restores are not allowed if you have to restore the database. You can only restore such a database by using full or differential backups. Which means, you will lose any changes made between the latest full / differential backup and the time you restore the database.

Full Recovery Model

In this mode, SQL Server saves the transaction logs until you back them up or until full database backup occurs. As you can imagine, this model allows for point in time database recovery. You can restore the database first by applying the latest full  / differential backup and then applying any transactions up until the time database was operational. Of course you will have to have a transaction log backup process in place to backup transactions periodically.

Bulk Logged Recovery Model


This model is similar to Full Recovery Model except in the way it handles bulk data operations (For example Bulk Insert). This model uses a process called minimal logging for bulk operations which saves significant process time. This model doesn't allow for point in time database restore operation. It is recommended to use this model only for a short period of time when you are performing bulk operations. Best way to implement this is to switch to bulked logged recovery model before performing bulk operations and then switch back to full recovery model.

Transaction Log File Management

Transaction Log Files, if not handled correctly can become a nightmare when they grow to be too large and suddenly there is no hard drive space and your database is down. I have seen situations where transaction log file has taken so much space on the hard drive that it must be truncated, but it cannot be backed up because there is no space on the hard-drive and no network location where the file can be backed up. Then you are forced to truncate the log file without making a backup, leaving you vulnerable until full database back is performed.

It is absolutely essential that you have a transaction log backup process in place if your database recovery model is Full or Bulk-Logged. If you are not backing up transaction logs in full or bulk-logged recovery model, your log is not being truncated, period. Even when you backup transaction log, remember the log file is only truncated after a restore checkpoint has reached.

If you are not backing up transaction logs and don't need point in time restore in case of a disaster, you should switch your recovery model to Simple. Simple recovery model will automatically truncate the transaction log after the checkpoint.

Also, log file truncation does not shrink the file, it simply clears the file to be reused for future transactions. If your file has grown too large and you want to recover some hard drive space, you must shrink the file manually. You can use the following SQL script

DBCC SHRINKFILE(<logName>, 1)
GO
BACKUP LOG <DBName> TO DISK = N'C:\Backup\TestDb.bak'
GO
DBCC SHRINKFILE(<logName>, 1)
GO


SELECT Name FROM sys.database_files WHERE type_desc='LOG'



NOTE: In SQL 2005 you can also use the following script. Although I will not recommend this because it doesn't backup the log and breaks the transaction log chain which means you can not restore to a point in time because not only you have lost transactions, any future backups of the transactions will be useless until the next full or differential backup.

BACKUP LOG <DBName> WITH TRUNCATE_ONLY

You can also set your database to auto-shrink. But I will recommend against this because it could create performance problems.

Under certain situations transaction logs may not truncate even when you are backing up transaction logs. We will discuss some of those situations in our future blog but usually this should not be a concern for most environments.

That's all there is to it. As always, your comments are welcome!

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.