SQL Server Log Shipping

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 is a process of copying the transaction log back up from a primary server to a secondary server and restoring on 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

SQL1.jpg

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 full recovery model or bulk-logged recovery model for the transaction Log Shipping.

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.

Monitor 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 "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 "Enable this as a primary database in a Log Shipping configuration "check box.

SQL2.jpg


SQL3.jpg

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

SQL4.jpg

SQL5.jpg

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

SQL6.jpg

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.

SQL7.jpg

Note: we can add multiple servers if we want to setup 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.

SQL8.jpg

Click on Connect.

SQL9.jpg

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".

SQL10.jpg

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.

SQL11.jpg

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

SQL12.jpg

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:

SQL13.jpg

SQL14.jpg

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 minute(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 restore job as per your desires.

SQL15.jpg

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.

SQL16.jpg

Note: Log Shipping monitoring configuration is optional.

SQL17.jpg

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

SQL18.jpg

SQL19.jpg

SQL20.jpg

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

SQL21.jpg

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

SQL22.jpg

SQL23.jpg

Copy and restore jobs history:

SQL24.jpg

SQL25.jpg

Monitor job history:

SQL26.jpg

Disable Log Shipping configuration:

SQL27.jpg

Click the yes button:

SQL28.jpg

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

SQL29.jpg