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.
The Primary Server must be in “Full” or “Bulk-Logged” recovery model.
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.
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.
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).
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.
First, you’ll have to connect to the secondary server.
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.
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.
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.
You can also generate a script for all of the configurations made to reuse on other servers.
You’re all done. After pressing “OK” you should see the following window, and the log shipping is up and running.
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.
There’s no automatic fail over when using log shipping.
Performing the manual fail over involves two simple steps
Restoring the secondary server with recovery
Disable all the related jobs
Pros and Cons:
I’ll start with the good stuff. It’s easy! The default configuration shouldn’t take more than a few minutes to set up. In addition, the standby server can be used for reporting purposes and can be configured on the same instance.
The bad news is the frequent backups add overhead to SQL Server’s operation, and the copying of the backup files takes its toll on the disk subsystem. Another drawback is that fail over must be done manually. If an automatic fail over is important, mirroring might be a more suitable solution.