SQL Server Log Shipping

Introduction

It's easy to set up. A standby database is easy to maintain and troubleshoot. It can be used for reporting purposes and configured for Log Shipping in one or more servers, for instance.

Log Shipping in SQL Server

Log Shipping is a process of copying the transaction log back up from a primary server to a secondary server and restoring it to the secondary server.

The purpose of implementing Log Shipping is to maintain two SQL Server databases synchronized in separate locations for high availability.

Log Shipping operations

Log Shipping consists of the following three operations:

1. Backing up the transaction log of the primary database
2. Copying the transaction log backup to each secondary server
3. Restoring the transaction log backup on the secondary database

Components of Log Shipping

The components of Log Shipping are:

1. Primary server
2. Secondary Server
3. Monitor Server

1. Primary server

It is the main database server and the primary database will be accessible for the applications.

The server database should be either in a full recovery model or a bulk-logged recovery model for the transaction Log Shipping.

2. Secondary Server

The server will have a backup copy of the primary database used for Log Shipping.

The secondary database can be in Standby Mode or No Recovery mode.

3. Monitor the Server

It tracks the Log Shipping process and optional components of Log Shipping.

It contains a set of jobs and sends alerts when the transaction Log Shipping sessions are out of sync.

Benefits of Log Shipping

1. Log Shipping doesn't require expensive hardware or software.
2. Once Log Shipping has been implemented, it is relatively easy to maintain.
3. The manual failover process is generally very short, typically 15 minutes or less.

Problems with Log Shipping

1. Log Shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs.
2. The users will experience some downtime.
3. Some data can be lost, although not always. How much data is lost depends on how often we schedule Log Shipping.

Log Shipping Sample

In this example, I have used three instances of Log Shipping.

Primary Server Instance: Primary server

Secondary Server Instance: secondary server

Monitor Server Instance: monitor server

1. Go to the Primary Instance Management Studio.
2. Point to the Rahul database, Right-click on Rahul and click on Tasks, and then click on Ship Transaction Logs… and enable it by clicking on the "Enable this as a primary database in a Log Shipping configuration "check box.

Or

Point to the Rahul database and right-click on Rahul and click on properties and then click on Transaction Logs shipping and enable it by clicking on the "Enable this as a primary database in a Log Shipping configuration "check box.

Now under the Transaction log backup click "Backup Settings" a pop-up window appears "Transaction Log Backup Settings" under:

Click the schedule option for the Tran log backup and schedule as needed.

When we click on the OK button the preceding window will close, then from the "Database Properties window" click on the button to configure the Secondary Server instance and database.

Note. we can add multiple servers if we want to set up a one-to-many server log-shipping.

A window "Secondary Database settings" will appear now. In this step, we can specify how to create the data on the secondary server. There are three options: create a backup and restore it, use an existing backup, and restore or do nothing because we have manually restored the database and have put it into the correct state to receive additional backups.

Click on Connect.

Enter the Secondary Server Name and its Password to connect to it, so that our secondary database setting shows both the "Secondary Server Instance" and "Secondary Database".

In the Copy Files tab, 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.

Click the schedule option for the copy Tran log backup and schedule as needed.

Click OK

In the Restore Transaction log tab, specify the database restoring state information and restore schedule. This will create the restore job on the secondary server. Click "ok", and we will see that in our "Database Properties" window a secondary Server has been added:

Click Ok.

In the Restore transaction log tab choose STANDBY mode and "disconnect users in dB when restoring backup", because if the users are accessing the dB then the restoration will fail. In "Delay Restoring Backups" if it is 0 minutes (s) then as soon as the copy job completes, the restore job will start immediately without any delay. In "Alert if no restore occurs within" if the restore does not happen for a stipulated amount of time mentioned here then an alert is issued. Also, schedule the restoration job as per your desires.

Now we will configure Log Shipping Monitoring that will notify us in case of any failure, click on "Settings"; a "Log Shipping Monitor Settings" window will be opened.

Note. Log Shipping monitoring configuration is optional.

Click on connect and enter the Username and password of your Primary Server, click "OK"

Again Click "OK" on the Database Properties Window, then you will get the following success result:

All backing up of the database [Rahul] is in the network path folder.

Copy and restore jobs history:

Monitor job history:

Disable Log Shipping configuration:

Click the yes button:

After disabling the Log Shipping configuration on the primary server then the database is restored with recovery in the secondary server.