SQL Server – an overview of Log Shipping
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:
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.