SQL Server Log Shipping

SQL Server Log Shipping

To synchronize the database SQL Server the log shipping is used. It is easy to set up, maintain and troubleshoot. It is the process of backing up our database and restoring these backups to another server for failover. To keep the data current and to minimize risk of data loss the process involves using SQL Server transaction log backups.

Main functions of Log Shipping

Logical Design Diagram of Log Shipping

• Backing up the transaction log of the primary database
• Copying the transaction log backup to each secondary server
• Restoring the transaction log backup on the secondary database

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.

Prerequisites

• The database must be using the FULL RECOVERY or BULK LOGGED recovery models.
• On the secondary server, we must have already restored a full database backup with the NORECOVERY or STANDBY options, so that transaction logs may be applied.

Creating the shared folder

First of all we need to create a shared folder on our secondary server, to place the transaction log backups.

Note. that the SQL Server service startup account needs to be a domain user account, and not the local system account, in order to access network shares.

The process of implementing Log Shipping is embedded with the Workgroup, Standard and Enterprise editions of SQL Server. To implement the Log shipping or mirroring right click on any database --> Go to Properties and under Select a page click on Transaction Log Shipping and enable it by clicking on "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 appeared "Transaction Log Backup Settings" as Under:

If we are creating backups on a network share enter the network path or for the local machine we can specify the local folder path and click "OK".

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

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

In 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 Added:

Now we will configure Log Shipping Monitoring which 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 Database Properties Window, then you will get the following success result.

How to bring a log shipping standby database online?

On the failure of Primary database in a log shipping setup, we need to bring the secondary database online for use, which can  be standby or read-only. To make the standby available, break the log shipping and bring secondary database online WITH RECOVERY.

To bring the standby database to the current state of the source database, if possible try to perform a final log backup on the source database, then run the log shipping backup job on the source server and copy the backup file to the log shipping share. If there are files remaining in the log shipping share, then use SQL Backup to restore the logs on the standby database by specifying a wildcard (*.sqb) filename, using the option WITH RECOVERY.

Note. If the source database is damaged, a final log backup may fail. In this case, try appending the NO_TRUNCATE option to the BACKUP LOG command.

If there are no remaining log files to restore, use the following SQL query to bring the database on-line:

"RESTORE DATABASE [PatientInfo] WITH RECOVERY".