Configure Log Shipping on Microsoft SQL 2008 R2

SQL log shipping is the process of automating the backup of a database and transaction log files on a primary (production) database server, and then restoring them onto a standby server. The secondary standby server acts as a backup server on a remote site that is nearly synchronized with the primary one and remains in read-only mode.

Log shipping consists of tree jobs:

  • Backup of transaction logs at the primary sql instance (job at the primary server)
  • Copy of the transaction logs to the secondary instance (job at the secondary server)
  • Restore of the transaction logs on the secondary instance (job at the secondary server)

There are many articles about SQL server log shipping operations, but I want to make an introduction to a future article  describing the method of log shipping including secure ftp uploading on a remote site instead of  server copying.

In this example we will use Microsoft SQL Server 2008 R2 instances and the corresponding SQL server Management Studio. So, open up management studio and right click on the primary database that you need to synchronize.

Management Studio GUI

Management Studio GUI

Select Ship Transaction Logs… option. The wizard opens up and enable the checkbox “Enable this as a primary database in a log shipping configuration”. Then press the “Backup Settings” button.

Supposing that the primary SQL server is called “databasesrv”, on the “Network path to backup folder” input, enter the shared folder path that the transaction logs reside. So, we have created a shared folder on our primary SQL server called “Logshipping” with read permissions and read access rights for the the user who runs the “SQL Agent service” on the secondary server, usually this is a domain user in the same or a trusted domain. This network path is used by the Copy job on the secondary instance as the source path of the transaction logs.

Transaction Log Backup Settings

Transaction Log Backup Settings

On the second input, “If the backup folder is located on the primary server, type a local path to the folder”, enter the local path that the transaction logs are created. Now, the user that runs the “SQL Agent service” on the primary server should have read/write permissions to this folder. This is the destination folder of the transaction log files used by the backup job on the primary instance.

“Delete files older than:” parameter specifies for how long you want transaction logs to remain in your backup folder before being deleted and “Alert if no backup occurs within:” parameter specifies for how long log you want log shipping to wait until raising an alert that no transaction log backup have occurred. The Compression parameter can be  “Compress Backup” when an SQL Server  Enterprise Edition is installed otherwise leave the default setting.

Finally press the Backup Schedule button to configure the frequency the backup job executes.

Backup Schedule

Backup Schedule

Actually here you specify how much synchronized should be your databases. Log shipping size, network delay during copy etc. may limit the frequency parameters you can specify.

After that, press the Ok buttons to return to the first screen of  Log Shipping wizard and press the Add button to specify the secondary instance parameters. First connect to the secondary database instance.

Secondary Database Settings

Secondary Database Settings

Select an existing database or enter the name to create a new one. The bottom pane of the screen configures the initialization of the secondary database. In this example I already have restored the initial backup of  the database in Standby mode, but it is straight forward the other available options. I prefer the Standby mode so the secondary database is read-only, so I can execute select queries on it.

Initialize Secondary Database Tab

Initialize Secondary Database Tab

Press the “Copy Files” tab to configure the copy job parameters. First specify the destination folder on the secondary server, again the user that runs the SQL Agent service on the secondary server should have read/write permissions on this folder. Here you will find later the copied transaction logs from the primary server to the secondary. “Delete files after:” parameter specifies for how long you want transaction logs to remain in your backup folder at the secondary server before being deleted.

Copy Files Tab

Copy Files Tab

Finally press the Schedule button to configure the job frequency. Please note that this job runs on the secondary server, so keep in mind that the clocks of primary and secondary server should be synchronized.

Copy Files Schedule

Copy Files Schedule

As you can see I shifted the time by5 minutes to be sure that the backup job has finished. Press Ok and select the Restore Transaction Log tab. Here I specify the state of the secondary database after the restore, which is Standby mode in my example and disconnect the users since this is a backup database. Also, I do not configure any restore delay.

Restore Transaction Log Tab

Restore Transaction Log Tab

Finally press the Schedule button to configure the restore job frequency. Please note that this job runs on the secondary server as well.

Restore Transaction Log Schedule

Restore Transaction Log Schedule

As you can see I shifted the time by10 minutes to be sure that the copy job has finished. Press Ok until the wizard finish up. I don’t setup a monitor server instance since it needs a 3rd database instance to monitor the log shipping jobs.

Now at the primary database under SQL Agent jobs you will see

Primary Database Jobs

Primary Database Jobs

And on the secondary database you will see

Secondary Database Jobs

Secondary Database Jobs

Finally, if your primary database becomes unavailable and you need to activate your secondary database, do the following:

  • Restore any missing transaction logs on the secondary database
  • Run the following query to get out of the Standby mode:

restore database <database name> with recovery

You may need to trim the frequency parameters on schedule jobs to establish a successful Log Shipping Operation procedure. Last but not least, keep the server clocks synchronized.

Advertisements

One thought on “Configure Log Shipping on Microsoft SQL 2008 R2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s