Implement and failover log shipping

The estimated reading time for this post is 8 minutes

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.

Benefits

  • 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.

Limitations

  • 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.

Once the monitor server has been configured, it cannot be changed without removing log shipping first.

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.

For each alert, you need to specify an alert number. Also, be sure to configure the alert to notify an operator when an alert is raised.

Log Shipping Overview

Log shipping consists of three operations:

  1. Back up the transaction log at the primary server instance.
  2. Copy the transaction log file to the secondary server instance.
  3. 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

Prerequisites

  • 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:\data\tlogs\, you could create the \\primaryserver\tlogs share of that directory.

Security

Permissions

The log-shipping stored procedures require membership in the sysadmin fixed server role.

Steps

In this illustration, I am going to implement log shipping feature in two SQL Server instances on my laptop as below:

SAEED-PC\MSSQLSERVER as Primary Server

SAEED-PC\SAEEDSQL 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.)

If the SQL Server service account on your primary server runs under the local system account, you must create your backup folder on the primary server and specify a local path to that folder.

Configure the Delete files older than and Alert if no backup occurs within parameters.

Note the backup schedule listed in the Schedule box under Backup job. If you want to customize the schedule for your installation, then click Schedule and adjust the SQL Server Agent schedule as needed. SQL Server 2017 supports backup compression. When creating a log shipping configuration, you can control the backup compression behavior of log backups by choosing one of the following options: Use the default server setting, compress backup, or Do not compress backup.

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.

If you choose to have Management Studio initialize the secondary database from a database backup, the data and log files of the secondary database are placed in the same location as the data and log files of the master database. This location is likely to be different than the location of the data and log files of the primary 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.

Note the copy schedule listed in the Schedule box under Copy job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.

7.On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

Standby mode is only an option when the version of the primary and secondary server is the same. When the major version of the secondary server is higher than the primary, only No recovery mode is allowed

If you chose the Standby mode option, choose if you want to disconnect users from the secondary database while the restore operation is underway. If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least. Choose an alert threshold under Alert if no restore occurs within.

Note the restore schedule listed in the Schedule box under Restore job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule. 

To monitor this log shipping configuration, you must add the monitor server now. To add the monitor server later, you would need to remove this log shipping configuration and then replace it with a new configuration that includes a monitor server.

8.On the Database Properties dialog box, click OK to begin the configuration process.

Failover Log Shipping

To failover log shipping follow steps below:

  1. In primary server, disable LSBackup job related to desired primary database.
  2. In secondary server, right click on LSCopy and LSRestore jobs related to desired secondary database and start job at step in order.
  3. In secondary server, disable all jobs in step2.
  4. In primary server, take tail log backup with NORECOVERY option.
  5. In secondary server, restore tail log backup with RECOVERY option.
  6. In secondary server, configure log shipping.
  7. In both servers delete all those disabled jobs.

Take tail log backup in share folder in where both servers have read-write permission.

This time ex secondary database will be primary database and ex primary database will be secondary database. You can use same share folders for job’s destinations.

If you want to verify done process as soon as possible, start jobs in primary and secondary databases in order then refresh databases.

I hope this blog has been helpful for you. Stay tuned for more informative blog posts.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

avatar
1000
  Subscribe  
Notify of