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.