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.
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.
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.
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.
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.
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.
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.
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.
Finally press the Schedule button to configure the restore job frequency. Please note that this job runs on the secondary server as well.
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
And on the secondary database you will see
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.