• Madeira Team

Log Shipping – As easy as 1, 2, 3

Today I want to talk about Log Shipping.

Log Shipping is an easy way to implement a high availability solution. It uses SQL Server’s transaction log to synchronize a stand by server. The configuration of the log shipping is really easy to set up as I’m about to show.

Basic requirements:

  1. The Primary Server must be in “Full” or “Bulk-Logged” recovery model.

  2. The Secondary Server must be from the same version as the primary (or later) and can be either in “Standby” or “No Recovery” mode. When using the Standby mode, the server can be used for reporting purposes, but it won’t be accessible during the log shipping process.

  3. A third monitor server can be used but this is optional.

Step 1 – Configuring the Primary Server:

Right click on the DB you wish to log ship and go to “Tasks -> Ship Transaction Logs…”. Click on “Enable this as a primary database in a log shipping configuration” check box and press on the “Backup Settings…” button.


Database-Properties-1

The “Transaction Log Backup Settings” window will open. In it you will have to specify the path to the folder where the transaction log backups will be saved. Here you can also determine the frequency of transaction log backups (the default is every 15 minutes).


log shipping Transaction-Log-Backup-Settings

Step 2 – Configuring the Secondary Server:

After the primary server is configured, it’s time to configure the secondary server by pressing the “Add” button. More than one secondary server can be defined to create multiple standby servers.


database-Properties-2

First, you’ll have to connect to the secondary server.


Secondary-Database-Settings1

In the “Initialize Secondary Database” tab you can choose the way you wish to initialize the secondary database (this is pretty self-explanatory).

In the “Copy Files” tab you’ll need to specify the folder where you wish to save the log backups shipped from the primary server, and the frequency of copying between the servers.


Copy-Files

In the “Restore Transaction Log” tab you can select the mode you want the secondary server to be in, and determine how often the transaction log will be restored.

As explained in the beginning, choosing the “Standby” mode will allow users to access the secondary DB. This may prevent the log from being restored and fail the log shipping process. When choosing this mode, it’s highly recommended to enable the “Disconnect users” option.


log shipping Restore-Transaction-Log

Step 3 – Monitor Server (Optional):

After configuring both servers, you can configure a third, monitor server.

The monitor server keeps track of when the transaction log on the primary database was last backed up, when the secondary servers last copied and restored the backup files, and information about any backup failure alerts.

A single monitor server can monitor multiple log shipping configurations.


log shipping Monitor

You can also generate a script for all of the configurations made to reuse on other servers.

That’s it!!

You’re all done. After pressing “OK” you should see the following window, and the log shipping is up and running.


log shipping DONE

Created objects:

You might have guessed that the log shipping solution is actually implemented using a series of jobs.

On the primary server you’ll find a job responsible for backing up the log file as specified in the first step.

On the secondary server you’ll find two jobs. The first is responsible for copying the file from the primary to the secondary server, and the second is responsible for restoring the log file as defined in the second step.

On the monitor server you’ll find a job responsible for alerting when no restore has occurred.

You should also find the original log backups on the folder you specified on the primary server, and the copied ones on the secondary.

Fail Over:

There’s no automatic fail over when using log shipping.

Performing the manual fail over involves two simple steps

  1. Restoring the secondary server with recovery