LOG Shipping

In my previous post, I am giving a short note related to Log Shipping. Some of my reader asks me to write some details related to it. So, in this article I am trying to summarize the concept of Log Shipping and with this I provide hands on configurations related to it. Hope you enjoy this article.

What is that?

The log shipping is the process of automating the backup of database and transaction log file from one server and restoring them to another server. The Enterprise Edition of SQL Server Only supports the Log Shipping.

Benefits of Log Shipping

It gives us the disaster recovery solutions for a single primary database and one or more secondary database. Each of that is the separate instance of the SQL server.

In the interval between restore jobs the second database supports read-only access.

Allows user-specified delay between when the primary server backup the log of the primary database and when secondary server must restore the log backup.

Terminology

Before starting the log shipping we have to understand some terminology mentioned bellow.

Primary Server

The instance of the SQL Server that is your production server.

Primary Database

The database of the primary server that you want to backup to another server.

Secondary Server

The standby copy of the primary database. The second database may be in either RECOVERING state or the STANDBY state, which leaves the database available for limited read-only access.

Monitor Server

An optional instance of the SQL server, that tracks all of the details of log shipping.

Backup Job

The SQL Server Agent job that performs the backup operations.

Copy Job

A SQL server agent job that copies the backup file from primary server to destination on the secondary server and log history on secondary server and monitor server.

Restore Job

A SQL Server Agent job that restore the copied backup file to the secondary database. It logs history on the local server and monitor server, and deletes old files and old history information.

Alert Job

A SQL Server Agent jobs that raises alerts from primary and secondary database when backup and restore operations does not complete successfully within as specified threshold.

Log Shipping Operations

It consists of three operations

1.     Backup the transaction log at the primary server instance.

2.     Copy the transactions files into secondary server instance (may be multiple).

3.    Restore the log backup on the secondary server instance (may be multiple).

The following figure illustrates the log shipping configurations.

The deployment process of Log shipping is mentioned bellow

We can enable the log shipping by these simple following steps.

1.    First choose the Server for Primary, Secondary and Optional Monitor server.

2.    SQL Server 2008 and letter version supports the Backup compressions. When configuring the Log Shipping configuration we can control he backup compression behavior.

3.    Create file share for the Transaction log backup. We have to prepare a server that is not the part of log shipping configurations. To maximize the availability  of the primary server MS recommends that the we must put the backup share on the separate host computer.

4.    Choose the Backup schedule for the Primary database Server.

5.    Create the Folder of the secondary server into which the transaction log backup will be copied.

6.    Configure the One or More Secondary server and Secondary Database.

7.    Optionally configure the monitor server.

8.    You must have the sysadmin on each server instance to enable the log shipping.

Steps to configure Log Shipping

1.    The database must be Full or Bulk-logged recovery model.

SELECT name, recovery_model_desc

FROM sys.databases WHERE name = 'My_DB'

USE [master]

GO

ALTER DATABASE [My_DB] SET RECOVERY FULL WITH NO_WAIT

GO

2.    In the SQL server management studio right click the selected database and select the properties. Then select the Transaction Log Shipping page. Then check the "Enable this as primary database in a log shipping configurations".

3.    The next is to configure and schedule a transaction log backup by clicking "Backup Settings…"
If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 Enterprise edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

4.     In this step we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.

Initialize Secondary Database tab

In this step you can specify how to create the data on the secondary server. We have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

Copy Files Tab

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

Restore Transaction Log Tab

Here we have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.

5.     In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional. Click on Settings… button which will take you to the "Log Shipping Monitor Settings" screen. Click on Connect …button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

6.    Click OK to finish it.

Hope you like it.

Posted by: MR.JOYDEEP DAS