SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur.
- Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
- Supports limited read-only access to secondary databases (during the interval between restore jobs) in standby mode.
- Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
- Doesn’t affect performance of primary server.
- Easy setup.
- No automated failover
- Cannot synchronize faster than once a minute
- Failover is more complicated than mirroring
- Harder to view status of log shipping
Terms and Definitions
- Primary Server: The instance of SQL Server that is your production server.
- Primary Database: The database on the primary server that you want to back up to another server. All administration of the log shipping configuration through SQL Server Management Studio is performed from the primary database.
- Secondary Server: The instance of SQL Server where you want to keep a warm standby copy of your primary database.
- Secondary Database: The warm standby copy of the primary database. The secondary database may be in either the RECOVERING state or the STANDBY state, which leaves the database available for limited read-only access.
- Monitor Server: An optional instance of SQL Server that tracks all of the details of log shipping, including:
- When the transaction log on the primary database was last backed up.
- When the secondary servers last copied and restored the backup files.
- Information about any backup failure alerts.
The primary and secondary server instances send their own history and status to the monitor server instance.
Backup Job: A SQL Server Agent job that performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. When log shipping is enabled, the job category “Log Shipping Backup” is created on the primary server instance.
Copy Job: A SQL Server Agent job that copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. When log shipping is enabled on a database, the job category “Log Shipping Copy” is created on each secondary server in a log shipping configuration.
Restore Job: A SQL Server Agent job that restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. When log shipping is enabled on a database, the job category “Log Shipping Restore” is created on the secondary server instance.
Alert Job: A SQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold. When log shipping is enabled on a database, job category “Log Shipping Alert” is created on the monitor server instance.
Log Shipping Overview
Log shipping consists of three operations:
- Back up the transaction log at the primary server instance.
- Copy the transaction log file to the secondary server instance.
- Restore the log backup on the secondary server instance.
The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.
A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.
You can use a secondary database for reporting purposes.
In addition, you can configure alerts for your log shipping configuration.
Implementing Log Shipping by using SSMS
- The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.
- Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if you back up your transaction logs to the directory c:datatlogs, you could create the primaryservertlogs share of that directory.
The log-shipping stored procedures require membership in the sysadmin fixed server role.
In this illustration, I am going to implement log shipping feature in two SQL Server instances on my laptop as below:
SAEED-PCMSSQLSERVER as Primary Server
SAEED-PCSAEEDSQL as Secondary Server
All setting for log shipping will be configured in primary server.
1.Right click the database you want to use as your primary database in the log shipping configuration, and then click Properties.
2.Under Select a page, click Transaction Log Shipping. Select the Enable this as a primary database in a log shipping configuration check box. Under Transaction log backups, click Backup Settings.
3.In the Network path to the backup folder box, type the network path to the share you created for the transaction log backup folder. If the backup folder is located on the primary server, type the local path to the backup folder in the If the backup folder is located on the primary server, type a local path to the folder box. (If the backup folder is not on the primary server, you can leave this box empty.)
4.Under Secondary server instances and databases, click Add.
5.Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.
In the Secondary Database box, choose a database from the list or type the name of the database you want to create. On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
6.On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into which the transaction logs backups should be copied. This folder is often located on the secondary server.
7.On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.
8.On the Database Properties dialog box, click OK to begin the configuration process.
Failover Log Shipping
To failover log shipping follow steps below:
- In primary server, disable LSBackup job related to desired primary database.
- In secondary server, right click on LSCopy and LSRestore jobs related to desired secondary database and start job at step in order.
- In secondary server, disable all jobs in step2.
- In primary server, take tail log backup with NORECOVERY option.
- In secondary server, restore tail log backup with RECOVERY option.
- In secondary server, configure log shipping.
- In both servers delete all those disabled jobs.
I hope this blog has been helpful for you. Stay tuned for more informative blog posts.