SQL Server – Log shipping for Disaster Recovery Solution

What is Log Shipping?

Log Shipping is a basic level SQL Server high-availability technology. It is automated process to send the transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

How Log Shipping working?

Log shipping involves copying a database backup and subsequent transaction log backups from the primary server and restoring the database and transaction log backups on one or more secondary servers.

Prerequisite

• The primary database must use the full or bulk-logged recovery model.
• Shared folder should be created to hold the transaction log backups.
• SQL Server Agent Service must be configured properly.

Restriction:

• Log shipping cannot be used for automatic failover plan

Here we will give you demo – How to Setup the Log Shipping

• Very Step is to make sure your database is in full or bulk-logged recovery model.
• Login to primary server then navigate to database Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box as shown in given snapshot :

• In the next step, we will configure the Backup Setting as shown in given snapshot. Here will mention network path. Also we can mention local folder path if backup folder path is located on the primary server. Once we are completed with this step, backup jobs will be created on primary server.

• Now in the next step we will add the secondary server instance\databases, we can add more than one if we want to log ship to multiple servers

• When we click on the Add Button, new screen will open and here we will configure the secondary server details. In the “Initialize secondary database” tab, there are three options. We have selected the first option as shown in the snapshot, which take the fresh backup of database from primary database and restore it on the secondary database.

• In the “Copy File” Tab, We will mention Destination Shared Folder where the Log Shipping Copy job will copy the Transactional-Log backup files. Once we are completed with this step, copy jobs will be created on Secondary server.

• In the “Restore Transaction Log” tab, we will mention database restore state and restore schedule. Once we are completed with this step, restore jobs will be created on secondary server.

                          No Recovery Mode: Database will be in restore state and cannot read until it’s online.
                          Standby Mode: Database will be in read-only mode.

• In the next step, we will configure the log shipping monitoring; Monitoring can be done from the primary server or secondary server or any other SQL Server instance. We can configure alerts on primary / secondary server if respective jobs fail.

• When we click on the “Setting…” button, new screen will open. Here we will mention the monitoring server details as shown in given snapshot. Once we are completed with this step, alert jobs will be created on primary server.

• Now click “OK” button to finish the Log Shipping configuration and it will show the following screen :

• Click “Close”
• Now you check on your secondary server, database will look as shown below :

Log shipping has been configured successfully !