As discussed in Chapter 14, log shipping is a technology you can use to implement disaster recovery and the scale out of read-only reporting. It works by taking the transaction log backups of a database, copying them to one or more secondary servers, and then restoring them, in order to keep the secondary server(s) synchronized. This chapter demonstrates how to implement log shipping for disaster recovery (DR). You also discover how to monitor and fail over log shipping.
For the purpose of the demonstrations in this chapter, we use a domain, consisting of a domain controller and four stand-alone servers, each with an instance of SQL Server installed. The server/instance names are PRIMARYSERVERPROSQLADMIN, DRSERVERPROSQLDR, REPORTSERVERPROSQLREPORTS, and MONITORSERVERPROSQLMONITOR, respectively.
Implementing Log Shipping for DR
Creating the Database to Be Log Shipped
For the purpose of this demonstration, we would like to configure disaster recovery for the Chapter16 database so that we have an RPO (recovery point objective) of ten minutes. We will also implement a ten-minute load delay on the DR server. This means that if an application team notifies us immediately of an incident that has led to data loss—for example, a user accidently deletes rows from a table—then we are able to rectify the issue by using the data on the DR server before the log that contains the erroneous transaction is restored.
GUI Configuration
We can now use the Backup Settings button to display the Transaction Log Backup Settings screen. On this screen, we enter the UNC (Universal Naming Convention) to the share that will be used for storing the log backups that log shipping takes. Because this share is actually configured on our primary server, we also enter the local path in the field below. The account that will be used to run the backup job needs to be granted read and change permissions on this share. By default, this will be the SQL Server service account, but for more granular security, it is possible to configure log shipping jobs to run under a proxy account, which reduces the security footprint of the implementation. Proxy accounts are discussed in Chapter 23.
We then configure how long we want our backup files to be retained before they are deleted. The value that you select for this depends on your enterprise’s requirements, but if your backup files are offloaded to tape, then you should make the files available long enough to allow the enterprise backup job to run, and you should potentially build in enough time for it to fail and then succeed on the following cycle. You should also consider your requirements for ad hoc restores. For example, if a project notices a data issue and requests a restore, you want to be able to retrieve the relevant backups from local disk, if possible. Therefore, consider how long you should give projects to notice an issue and request a restore before SQL Server removes the local copy of the backups. Backup strategies are discussed further in Chapter 13.
You should also specify how soon you want an alert to be generated if no log backup occurs. To be notified of any backup failure, you can configure the value to be a minute longer than your backup schedule. In some environments, however, it may be acceptable to miss a few backups. In such an instance, you may set the value to a larger interval so that you are not flooded with failed backup alerts during maintenance windows and other such situations.
The Set Backup Compression drop-down determines if backup compression should be used, to reduce network traffic. The default is to take the configuration from the instance, but you can override this by specifically choosing to use it, or not use it, for the backups taken by the log shipping job. The Transaction Log Backup Settings screen is illustrated in Figure 16-2.
Your decision of whether or not you should use compression depends on the resource constraints of the server. If you are experiencing network bandwidth constraints, you will likely want to implement compression. If your server is CPU bound, however, then you may not be able to spare the extra cycles that compression uses.
After returning to the Transaction Log Shipping page, we can use the Add button to configure the secondary server(s) for our Log Shipping topology. Using this button causes the Secondary Database Settings page to display. This page consists of three tabs. The first of these is the Initialize Secondary Database tab, which is displayed in Figure 16-4.
On this tab, we configure how we want to initialize our secondary database. We can preinitialize our databases by taking a full backup of the database and then manually restoring them to the secondary server using the NORECOVERY option. In this kind of instance, we would select the No, the secondary database is initialized option.
If we already have a full backup available, then we can place it in a file share that the SQL Server service account has read and modify permissions on, and then use the Yes, restore an existing backup of the primary database into the secondary database option and specify the location of the backup file.
On the Copy Files tab, illustrated in Figure 16-5, we configure the job that is responsible for copying the transaction log files from the primary server to the secondary server(s). First, we specify the share on the secondary server to which we will copy the transaction logs. The account that runs the copy job must be configured with read and modify permissions on the share. Just like the backup job, this job defaults to running under the context of the SQL Server service account, but you can also configure it to run under a proxy account.
On the Restore Transaction Log tab, we configure the job that is responsible for restoring the backups on the secondary server. The most important option on this screen is what database state we choose when restoring. Selecting the No recovery mode option is the applicable choice for a DR server. This is because if you choose Standby mode, uncommitted transactions are saved to a Transaction Undo File, which means the database can be brought online in read-only mode (as discussed in Chapter 14). However, this action increases the recovery time, because these transactions then need to be reapplied before the redo phase of the next restore.
Once back on the Transaction Log Shipping page, we need to decide if we want to implement a monitor server. This option allows us to configure an instance, which acts as a centralized point for monitoring our Log Shipping topology. This is an important decision to make at this point, because after configuration is complete, there is no official way to add a monitor server to the topology without tearing down and reconfigured log shipping.
It is technically possible to force in a monitor server at a later time, but the process involves manually updating log shipping metadata tables in MSDB. Therefore, it is not recommended, or supported.
T-SQL Configuration
sp_add_log_shipping_primary_database Parameters
Parameter | Description |
---|---|
@database | The name of the database for which you are configuring log shipping. |
@backup_directory | The local path to the backup folder. |
@backup_share | The network path to the backup folder. |
@backup_job_name | The name to use for the job that backs up the log. |
@backup_retention_period | The duration that log backups should be kept for, specified in minutes. |
@monitor_server | The server/instance name of the monitor server. |
@monitor_server_Security_mode | The authentication mode to use to connect to the monitor server. 0 is SQL authentication and 1 is Windows authentication. |
@monitor_server_login | The account used to connect to the monitor server (only use if SQL authentication is specified). |
@monitor_server_password | The password of the account used to connect to the monitor server (only use if SQL authentication is specified). |
@backup_threshold | The amount of time that can elapse, without a log backup being taken, before an alert is triggered. |
@threshold_alert | The alert to be raised if the backup threshold is exceeded. |
@threshold_alert_enabled | Specifies if an alert should be fired. 0 disables the alert; 1 enables it. |
@history_retention_period | The duration for which the log backup job history will be retained, specified in minutes. |
@backup_job_id | An OUTPUT parameter that specifies the GUID of the backup job that is created by the procedure. |
@primary_id | An OUTPUT parameter that specifies the ID of the primary database. |
@backup_compression | Specifies if backup compression should be used. 0 means disabled, 1 means enabled, and 2 means use the instance’s default configuration. |
Listing 16-2 demonstrates how we can use the sp_add_log_shipping_primary_database procedure to configure Chapter16 for log shipping. This script uses the @backup_job_id output parameter to pass the job’s GUID into the sp_update_job stored procedure. It also uses the sp_add_schedule and sp_attach_schedule system stored procedures to create the job schedule and attach it to the job. Because configuring log shipping involves connecting to multiple instances, we have added a connection to the primary instance. This means that we should run the script in SQLCMD mode.
sp_update_job, sp_add_schedule, and sp_attach_schedule are system stored procedures used to manipulate SQL Server Agent objects. A full discussion of SQL Server Agent can be found in Chapter 23.
Sp_add_log_shipping_primary_database
sp_add_log_shipping_primary_secondary Parameters
Parameter | Description |
---|---|
@primary_database | The name of the primary database |
@secondary_server | The server/instance of the secondary server |
@secondary_database | The name of the database on the secondary server |
Sp_add_log_shipping_primary_secondary
sp_add_log_shipping_secondary_primary Parameters
Parameter | Description |
---|---|
@primary_server | The server/instance name of the primary server. |
@primary_database | The name of the primary database. |
@backup_source_directory | The folder that the log backups are copied from. |
@backup_destination_directory | The folder that the log backups are copied to. |
@copy_job_name | The name that is given to the SQL Server Agent job used to copy the transaction logs. |
@restore_job_name | The name that is given to the SQL Server Agent job used to restore the transaction logs. |
@file_retention_period | The duration for which log backup history should be retained, specified in minutes. |
@monitor_server | The server/instance name of the monitor server. |
@monitor_server_security_mode | The authentication mode to be used to connect to the monitor server. 0 is SQL authentication and 1 is Windows authentication. |
@monitor_server_login | The account used to connect to the monitor server (only use if SQL authentication is specified). |
@monitor_server_password | The password of the account used to connect to the monitor server (only use if SQL authentication is specified). |
@copy_job_id | OUTPUT parameter that specifies the GUID of the job that has been created to copy the transaction logs. |
@restore_job_id | OUTPUT parameter that specifies the GUID of the job that has been created to restore the transaction logs. |
@secondary_id | An OUTPUT parameter that specifies the ID of secondary database. |
Sp_add_log_shipping_secondary_primary
sp_add_log_shipping_secondary_database Parameters
Parameter | Description |
---|---|
@secondary_database | The name of the secondary database. |
@primary_server | The server/instance of the primary server. |
@primary_database | The name of the primary database. |
@restore_delay | Specifies the load delay, in minutes. |
@restore_all | When set to 1, the restore job restores all available log backups. When set to 0, the restore job only applies a single log backup. |
@restore_mode | Specifies the backup mode for the restore job to use. 1 means STANDBY and 0 means NORECOVERY. |
@disconnect_users | Determines if users should be disconnected from the database while transaction log backups are being applied. 1 means that they are and 0 means that they are not. Only applies when restoring logs in STANDBY mode. |
@block_size | Specifies the block size for the backup device, in bytes. |
@buffer_count | Specifies the total number of memory buffers that can be used by a restore operation. |
@max_transfer_size | Specifies the maximum size of the request that can be sent to the backup device, in bytes. |
@restore_threshold | The amount of time that can elapse, without a restore being applied, before an alert is generated; specified in minutes. |
@threshold_alert | The alert to be raised if the restore threshold is exceeded. |
@threshold_alert_enabled | Specifies if the alert is enabled. 1 means that it is enabled and 0 means that it is disabled. |
@history_retention_period | The retention period of the restore history, specified in minutes. |
@Ignoreremotemonitor | An undocumented parameter that partially controls how the internal log shipping database journal is updated. |
Sp_add_log_shipping_secondary_database
sp_processlogshippingmonitorsecondary
Parameter | Description |
---|---|
@mode | The recovery mode to use for the database. 0 indicates NORECOVERY and 1 indicates STANDBY. |
@secondary_server | The server/instance of the secondary server. |
@secondary_database | The name of the secondary database. |
@secondary_id | The ID of the secondary server. |
@primary_server | The server/instance of the primary server. |
@monitor_server | The server/instance of the monitor server. |
@monitor_server_security_mode | The authentication mode used to connect to the monitor server. |
@primary_database | The name of the primary database. |
@restore_threshold | The amount of time that can elapse without a restore being applied before an alert is triggered; specified in minutes. |
@threshold_alert | The alert that fires if the alert restore threshold is exceeded. |
@threshold_alert_enabled | Specifies if the alert is enabled or disabled. |
@last_coppied_file | The file name of the last log backup to be copied to the secondary server. |
@last_coppied_date | The date and time of the last time a log was copied to the secondary server. |
@last_coppied_date_utc | The date and time of the last time a log was copied to the secondary server, converted to UTC (Coordinated Universal Time). |
@last_restored_file | The file name of the last transaction log backup to be restored on the secondary server. |
@last_restored_date | The date and time of the last time a log was restored on the secondary server. |
@last_restored_date_utc | The date and time of the last time a log was restored on the secondary server, converted to UTC. |
@last_restored_latency | The elapsed time between the last log backup on the primary and its corresponding restore operation completing on the secondary. |
@history_rentention_period | The duration that the history is retained, specified in minutes. |
Sp_ processlogshippingmonitorsecondary
Log Shipping Maintenance
After you configure log shipping, you still have ongoing maintenance tasks to perform, such as failing over to the secondary server, if you need to, and switching the primary and secondary roles. These topics are discussed in the following sections. We also discuss how to use the monitor server to monitor the log shipping environment.
Failing Over Log Shipping
Backing Up the Tail End of the Log
Applying Transaction Logs
Switching Roles
Disabling Log Shipping Jobs
The next step is to reconfigure log shipping on the new primary server. When you do this, configure the following:
Ensure that you use the same backup share that you used for the original primary server.
Ensure that when you add the secondary database, you specify the database that was originally the primary database.
Specify the synchronization No, the secondary database is initialized option.
Reconfiguring Log Shipping
Reconfiguring Monitoring
Because we have now created the backup, copy, and restore jobs on both servers, switching the roles after subsequent failovers is much more straightforward. From now on, after we have failed over, we can switch roles by simply disabling the backup job on the original primary server and the copy and restore jobs on the secondary server, and then enabling the backup job on the new primary server and the copy and restore jobs on the new secondary server.
Monitoring
The most important aspect of monitoring your Log Shipping topology is ensuring that the backups are occurring on the primary and being restored on the secondary. For this reason, when we configure log shipping in this chapter, we tweak the acceptable thresholds for backups and restores, and Server Agent Alerts are created on the monitor server. Before these alerts are useful, however, we need to configure them with an operator to notify.
You should configure the Log Shipping Secondary Server Alert in the same way you configured the Log Shipping Primary Server Alert. The secondary server alert works in the same way, except that it is monitoring for Error 14421 instead of 14420. Error 14421 indicates that a transaction log has not been restored to the secondary server within the threshold period.
The log shipping report can be run from SQL Server Management Studio, and when you run it on the monitor server, it displays the status of the primary server and each secondary server. When run on the primary server, it shows the status of each database based on the backup jobs and includes a line for each secondary. When run on the DR server, it shows the status of each database based on the restore jobs. You can access the report by invoking the context menu of the instance and drilling through Reports ➤ Standard Reports, before selecting the Transaction Log Shipping Status report.
Summary
Log shipping is a technology that you can use to implement DR for databases. It synchronizes data by backing up the transaction log of the primary database, copying it to a secondary server, and then restoring it. If the log is restored with STANDBY, then uncommitted transactions are stored in a Transaction Undo File and you can reapply them before subsequent backups. This means that you can bring the database online in read-only mode for reporting. If the logs are restored with NORECOVERY, however, then the servers are ready for a DR invocation, but the databases are in an offline state.
Failing over a database to a secondary server involves backing up the tail end of the transaction log and then applying any outstanding log backups to the secondary database, before finally bringing the database online by issuing the final restore with RECOVERY. If you wish to switch the server roles, then you need to disable the current log shipping jobs, reconfigure log shipping so that the secondary server is now the primary, and then reconfigure monitoring. After subsequent failovers, however, switching the roles becomes easier, because you are able to simply disable and enable the appropriate SQL Server Agent jobs used by log shipping.
To monitor the health of your Log Shipping topology, you should configure the log shipping alerts and add an operator who will be notified if the alert fires. The alert for the primary server is monitoring for Error 14420, which means that the backup threshold has been exceeded. The alert for the secondary server(s) monitors for Error 14421, which indicates that the restore threshold has been exceeded.
A log shipping report is available; it returns data about the primary databases, the secondary databases, or all servers in the topology, depending on whether it is invoked from the primary server, the secondary server, or the monitor server, respectively. The same information can be obtained from the sp_help_log_shipping_monitor stored procedure.