Step By Step Configuration of Log Shipping in SQL Server

What is SQL Server Log Shipping ?

Log shipping in SQL Server provides a disaster recovery solution at the database level. A log-shipping configuration involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. Enabling Log shipping will automatically transfer transaction log files and restore it across SQL servers. In the article, we will be discussing step by step procedure for configuring Log Shipping in SQL Server.

Log Shipping is a basic level SQL Server high availability solution that provides automated backup and restore process by creating another copy of SQL Database for failover. Feature of Log Shipping is available in all SQL Server Editions except the Express edition. It is method of copying a database backup and subsequent transaction log backups from source server and restoring transaction log backups on one or more destination servers.

Configuring Log Shipping in SQL Server – Prerequisites

There are some minimum requirements for configuring Log Shipping in SQL Server. First, one must have sysadmin rights and permissions on the Server to set up the log shipping. The prerequisites for setting up Log shipping are as follows:

• The version of SQL Server must be at least 2005 or later versions
• Works on standard, workgroup or Enterprise SQL Server editions
• Same case sensitivity settings should be present in the servers that in involved in log shipping
• Primary database must be set in full recovery or bulk-logged recovery model. Using Simple recovery model will stop the Log shipping to function
• Create a shared folder to copy transaction log files& make it available to secondary server
• The SQL Server Agent Service should be configured properly

Enable Log Shipping using SQL Server Management Studio

The steps for configuring log shipping in SQL Server using SQL Server Management Studio are-

1. 1. Open SQL server Management Studio & Connect to primary server ‘WINDOWS2’

1. 1. Select the database and right-click to view the properties. Database properties window will be opened. Click on Options to ensure that the recovery model is set to full or bulk-logged model.

1. 1. From the Properties windows, select Transaction Log Shipping option and tick the checkbox of Enable this as primary database in a log shipping configuration

1. 1. To configure & schedule transaction log backup, select Backup settings. We can create backup either on a network specifying network path or local system specifying local folder path. We will type the name of backup job and schedule the backup. Click OK.

1. 1. We will now configure secondary instance and database. Click on Addbutton and add multiple servers if desired for setting one or more server log shipping.

1. 1. After selecting the secondary server and connecting to the database, it will be displayed in secondary databases column. After clicking on Add, secondary database settings window will be opened. We can change the settings in three places.

      Initialize Secondary Database

      There are two options available to restore backup into secondary database. We can either generate a full backup of primary database and restore it to secondary server or use existing backup of primary database and restore it on secondary server. We have selected the first option.

      

      Copy files

      We will define the destination folder for copied T-Log backup files creating the copy job and schedule job.

      

      Restore Transaction Log

      We will specify the database restoring state information and restore schedule after creating job on secondary server. Selecting Standby Mode will make Secondary database in read only mode and user can only read the data.

1. 1. We can use Log Shipping Monitor service that will give us alert if there is any failure. It is an optional feature. Tick the checkbox of use a monitor service instance
      
   2. Click on Settings after checking the box for Monitor service. Connect the server instance Windows\secondary and Click OK.

1. 1. We can see the Log Shipping is successfully configured in SQL Server.

Conclusion

In the above article, we have thoroughly discuss the SQL Server log shipping, which is used as a disaster recovery solution. Configuring Log shipping in SQL Server is not difficult if we follow the step by step procedure given in the article. We have explain the steps to configure log shipping in SQL server using SQL Server Management Studio (SSMS). Even though it can also be configured using Transact-SQLs, most of users prefer using SSMS. Log shipping is an important feature in SQL Server that is easy to set up and maintain.