SQL Server - Log Shipping Overview

For distributed database application environment, it is always required to synchronize different database servers, backup, copy transaction logs, etc. SQL Server 2005 provides an advanced featured 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 applications which can improve the performance and availability of the database.

What is Log Shipping?
Log shipping is used to synchronize the Distributed Database Server. Synchronize the database by copying transaction log, backing up, and 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 is has just synchronize the databases but if the primary server fails, it will not redirect your application to the secondary server. This has to be done manually.

The main functions of log shipping are:

• Backing of the transaction log of the primary database
• Copying the transaction log backup to the secondary servers
• Restoring the transaction log backup on the secondary servers

Log Shipping Prerequisites

• Must have at least two Database Servers or two SQL Server 2005 Database Engine/Instances
• Configuration user should have Admin privilege on the server
• SQL Server Agent Service Configured properly
• Primary database “Recovery Model” must be FULL
• Shared folder (for both primary and secondary servers) for copying the transaction logs.

Components of Log Shipping

For implementing log shipping, we need the following components:

• Primary Database Server: Primary server is main database server of SQL Server database instance, which is being accessed by the application. Primary server contains the primary database.
• Secondary Database Server: Secondary database server is a SQL Server database instance or a different server that contains the backup of primary database. There are possibilities to have multiple secondary servers if needed.
• Monitoring Server: Monitoring server is a SQL Server instance which tracks the log shipping process.

Log Shipping Database Server Configuration

You can start the log shipping by Right click on the primary database > properties > Transaction Log Shipping > Select check box, and configure your settings.

Figure 1: Enable Log Shipping

Further configurations:

Figure 2: Transaction Log Backup Setting for Primary Server

Configure Secondary Server:

Figure 3: Secondary Server Configuration

Post Log shipping activity
After you have configured the log shipping, you’ll assume that there are three main activities/jobs will be created for log shipping and they are “backup job”, “copy backup file job” and “restore job”. You can monitor information about the status of all the log shipping servers. The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server and the history and status of the copy and restore operations are stored at the secondary server. If monitor server has been implemented then this information is also stored on the monitor server.

Followings are the catalog tables which can explain about almost everything related to the configured and running log shipping process:

• Log_shipping_monitor_alert: Stores alert job ID.
• Log_shipping_monitor_error_detail: Stores error details for log shipping jobs.
• Log_shipping_monitor_history_detail: Contains history details of log shipping agent.
• Log_shipping_monitor_primary: Stores one monitor record for the primary database in each log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.
• Log_shipping_monitor_secondary: Stores one monitoring record for each secondary database, including information about the last backup file and last restored file.