Log Shipping

        For distributed database application environment, it is always required to synchronize different database servers, back up, copy Transaction Logs, etc. If we are going to implement using application we have to put lots of efforts to build up the application. SQL Server 2005 provides an advanced feature called Log Shipping. Log shipping is an Automated Process for backing up, restoring, copying the transaction logs and synchronizing the database for distributed database server application which can improve the application performance and availability of database. In my recent project, I have done some short of experiment on it. I am going to explain it in this article.

Log Shipping is used to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server used SQL Server Job Agents for making those processes automatic. Log Shipping does not involve automatic transfer of server if there is any failure. This means it has just synchronized the databases but if the primary server fails, it will not redirect your application to a secondary server. This has to be done manually.

The main functions of Log Shipping are as follows:

• Backing up the transaction log of the primary database
• Copying the transaction log backup to each secondary server
• Restoring the transaction log backup on the secondary database

Log shipping

• Automated process to ship transaction log backups to another server
• Three jobs to perform the required actions: backup, copy and recover
• Involves three servers/instances: primary, secondary and (optional) monitoring server
• Requires full or bulk logged mode
• Can have multiple secondary copies
• Information about log shipping kept in MSDB

 Log Shipping Options

• Interval – Default is 15 minutes. Consider the impact before using a smaller number
• Delayed log restore – option that allows you to have an older copy, for an “oops” situation
• If secondary is far behind. Consider backup/copy to media/restore, consider partitioning the data.
• Careful – Backup typically goes to a file server share. Agent account needs access to share.
• Careful – Weekend maintenance jobs (like indexing) can make you run out of disk space
• No option to synchronize logins – Consider sp_resolve_logins to import.
• Monitoring – MSDB tables, agent history

Comparing to Mirroring

• Mirroring provides synchronous mode, with no data loss
• Mirroring provides automated failover
• Log shipping allows for multiple copies
• Log shipping allows for delay in applying logs

Components of Log Shipping

For implementing Log Shipping, we need the following components - Primary Database Server, Secondary Database Server, and Monitor Server.

• Primary Database Server: Primary Sever is the Main Database Server or SQL Server Database Engine , which is being accessed by the application. Primary Server contains the Primary Database or Master Database.
  
• Secondary Database Server: Secondary Database Server is a SQL Server Database Engine or a different Server that contains the backup of primary database. We can have multiple secondary severs based on business requirements.
  
• Monitor Server: Monitor Server is a SQL Server Database Engine which Track the Log Shipping process. 

Transaction log file growth

Transaction log expansion may result in the following situations: 

·         A very large transaction log file.

·         Transactions may fail and may start to roll back.

·         Transactions may take a long time to complete.

·         Performance issues may occur. Blocking may occur.

The following are the causes for transaction log growth,

1.    Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.

2.    Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.

3.    Running extremely large transactions like Bulk Insert, Select Into commands.

The following are the proactive measures in order to minimize the unexpected log file growth,

1.    If you do not want point in time recovery of your databases then you can change the recovery model to Simple.

2.    Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.

3.    Configure the automatic expansion of transaction log in terms of MB instead of %.

4.    Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.

5.    You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

When your log file has grown enormously you have 2 options to curtail the growth,

1.    Take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.

2.    You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.

Hence please try to avoid the 2^nd option I mentioned above. Due to the above fact the truncate_only or no_log option is deprecated in SQL 2008.

You need to schedule a backup of the log periodically throughout the day.  I usually discuss the recovery strategy with the owner of the product and get there expectations of service level.  For internet e-commerce application databases, I run a backup to disk every 15 minutes of the logs that are immediately swept off to tape by Net backup.  For other databases that are "less critical" as defined by the owners, I backup the logs every two hours, and they are managed directly by Net backup.  Other databases are in simple recovery mode.  What happens is when you do a full backup or a log backup, the LSN's in the log get marked as safe, and can be then truncated by SQL and the space is reused.  If you aren't doing the Log backups between full backups, you aren't really benefitting from the Full Recovery Model.  At best you can hope to be able to get the tail log file from disk and use it in recovery to a point in time.  However, if your disks are the cause of a crash, you will only have the full back up on tape (hopefully) that you can recover to.