Know How to Monitor Log Shipping in SQL Server

Log shipping in SQL Server is the process of enabling an automatic backup of the complete database and transaction files of the primary server and then restoring the backup on a secondary server. Being a high-availability solution, it increases the database availability of SQL Server by creating a backup server that can replace the primary server in case of data disasters.

Log Shipping involves 3 servers:

1. Primary or the source server
2. Secondary or destination server
3. Monitor Server

Log Shipping in SQL Server allows sending of transactional log backups from primary instances residing on the primary server to one or more secondary instances residing on the secondary server. These transactional log backups are applied to each instance of the secondary database individually.

It is to be noted that Log shipping in SQL Server can also be configured without the installation of monitor server. In such a case, the history and the status of all the backup operations are stored on the primary server and the history and restore status is stored on the secondary server. However, when the remote monitor server is installed, this information also gets stored on it. It is also responsible for raising alerts whenever the backup process is not performed as per the schedule.

In case the monitor server has not been specified, the primary and secondary servers monitor the backup and restore operations, and raise the alert when an error is encountered.

Key Note for Monitoring Log Shipping in SQL Server

For monitoring the configuration of log shipping in SQL Server, the monitor server needs to be added at the time of enabling log shipping. In case the monitor server is added later on, the existing log shipping configuration must be removed and the new setup including monitor serves must be installed. In addition to this, any alterations in the monitor server can only be enabled after removing log shipping.

History Tables in SQL Server Log Shipping

The monitoring history tables of log shipping comprise metadata that gets stored on the monitor server. Moreover, primary and secondary server-specific information is also stored locally. For monitoring the status of the monitoring sessions, these history tables are brought in use. In order to check the status of log shipping, status and history of backup job, copy and restore functions need to be viewed.

Some of the tables that need to be checked for monitoring the log shipping in SQL Server are:

1. log_shipping_monitor_alert

This table stores the alert job ID.

2. log_shipping_monitor_error_detail

This table stores the details of the log shipping transactions. In addition to this, the users can also access the table for accessing errors in a session.

3. log_shipping_monitor_history_detail

This table gives the details of the history of the log shipping agents. Details regarding an agent session can also be viewed in this table.

4. log_shipping_monitor_primary

5. One monitor record for primary database of each log shipping configuration is stored in this table. This includes the information like the details of the last backup and the last restored file.

6. log_shipping_monitor_secondary

This table stores one monitor record of the secondary database like the last backup file and the last restored file that are used in monitoring.

Conclusion

Monitoring log shipping in SQL Server enables the administrator to monitor the entire configuration of the entire environment. In addition to the history tables, additional parameters like stored procedures enable to monitor log shipping in SQL Server.