Database Administrators are vigilant on the backup and restore processes and use the Monitor Transaction log for the same. SQL Server Transaction Log shipping’s prime steps are Transaction Log backup, copy and restore. The answer to solving Disaster Recovery (DR) at the database level can be achieved by SQL Server Transaction Log shipping.
Log shipping is just an automated method of doing a FULL backup, copying it to the secondary server and restoring it in NO-RECOVERY state, and then making sequential log-backups from the primary server, copying them to the secondary server and restoring them. The log shipping jobs always save the history and status of log shipping operations. The primary server always keeps the history and state of the backup process, whereas the history and state of the copy and restore operations are stored at the secondary server.
In the msdb, SQL Server itself sets up the alert in case of failure of any step or if any step is navigating towards the pre-defined boundary. In the primary and secondary database of the SQL Server instance, the alert configuration will reside in the tables of the msdb database. In the log shipping for primary database, log_shipping_monitor_primary table is there, and for the secondary database table, named log_shipping_monitor_secondary is there.
Table log_shipping_monitor_primary is used for each individual primary database in the msdb to store log shipping monitor details. With the use of this table, failure alert for the activity at the primary database will be set up. Different columns are used to denote whether the alert is configured or not using a Boolean (true/false) field to monitor transaction log status. For example, a threshold_alert_enabled column is used for monitoring perspective, whereas a backup_threshold column represents the polling time to alert if no backup occurs within the value defined with the column.
For each secondary database in the msdb, log shipping monitor details are being stored by log_shipping_monitor_secondary. This table also provides the facility to store failure alert for the copy and restore activity at the secondary database end. Different meaning is associated with every individual column of this table to monitor transaction log status.
- restore_threshold column is for an alert if no restore occurs within (n) minutes
- threshold_alert_enabled column is with the Boolean value (true/false) to represent that alert is configured or not
- last_copied_file column related to the destination location for the copy operation with last copied file name for the subscriber database
- last_copied_date column related to the last copy operation timing for the subscriber database
- last_copied_date_utc column related to the last copy operation UTC timing for the subscriber database
- last_restored_file column related to the last restored file name with the folder directory path for the subscriber database
- last_restored_date column related to the last transaction log backup restoration time
- last_restored_date column related to the last transaction log backup restoration time
- last_restored_date_utc column related to the last transaction log backup restoration time in the UTC time zone
Why do we need to monitor Transaction Log Shipping?
Let’s consider a scenario where log backups and copies are running smoothly without any problems, but one of your secondary fails. We are not aware of this because we are deliberately not monitoring the secondary. Your log shipping restore alerts are based on the secondary server. If you notice that a couple of days later, but by then, it’s too late because the LSN chain is already broken with deleting the (n) days later backup at primary and you have to restart log shipping from scratch.
The best way to monitor the multiple Log shipping connections in SQL Server is through separate monitoring instance. If the monitoring instance is in Express Edition, then you don’t have to worry about licensing. After setting up log shipping and thereafter in the future, when we want to change the monitoring instance or add one, all you need to know is you need to reconfigure your log shipping. The advantage is that you can use the monitoring instance for multiple log shipping and have the view of all the primary/secondary database status in one location.
The health updates of Transaction log shipping on SQL Server report and third party monitor tools as well can be checked by the user. We also get help to monitor the Transaction Log Shipping process through SQL Server DMV. To check the backup and restore stats of Transaction Log backup, we will have a much better direction over the T-SQL script. In SSMS Basic report with Backup, Copy and Restore status are always available. The users get great help in the form of Transaction Log Shipping Status reports, when users are not much aware of the Backup and Restore related DMVs.
Monitor Transaction Log shipping with SSMS
SQL Server instance -> Reports -> Standard Reports -> Transaction Log Shipping Status
In this example, the Primary database is pub_db, whereas the subscriber database is sub_db in the log shipping. Along with the health details report (Transaction Log Shipping Status) is generated in the SSMS as under. Database Administrator gets every comprehensive detail in this report.
With the help of a report generated by SSMS, Database administrators can’t keep track of the number of health check-ups for different activities. Therefore, we use third-party tools for database monitoring. Even, T-SQL statement gives the user insight into every information listed below.
Monitor Transaction Log shipping using T-SQL
Monitor transaction log and Log shipping failures can be regulated for the smooth operation by statistics; backup history helps in analyzing the delays at the publisher’s end, restore history at the subscriber end. We have T-SQL statements as under for the backup and restore with row filter by database name. LSN of the transaction log backup can be handy for analyzing the statistics. The unique LSN, which is generated by the publisher end is associated with each backup set and based on the LSN reference, the user can find the last backup is restored or not at the subscriber end?
Get last Backup of database with LSN (Publisher Side):
|
1 2 3 4 5 |
SELECT d.name, b.* FROM master.sys.sysdatabases d LEFT OUTER JOIN msdb..backupset b ON b.database_name = d.name AND b.type = 'L' where d.name = ? ORDER BY backup_finish_date DESC |
Get details of last restored transaction log Backup (Subscriber Side):
|
1 2 3 4 5 |
SELECT b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn, a.* FROM msdb..restorehistory a INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id WHERE a.destination_database_name = ? ORDER BY restore_date DESC |
Here, First LSN is an LSN reference of last-second generated database backup and LAST LSN is an lsn reference of last generated database backup.
SQL Server system procedures to get details of Log Shipping health
sp_help_log_shipping_monitor procedure helps to get log shipping health details of the primary and secondary database in the SQL Server instance to monitor Transaction Log.
|
1 |
EXEC sp_help_log_shipping_monitor |
sp_help_log_shipping_monitor_primary procedure helps to get log shipping health details of the particular primary database to monitor Transaction Log. Here, the primary database server and primary database name will be the input parameter.
|
1 |
EXEC sp_help_log_shipping_monitor_primary @primary_server, @primary_database |
sp_help_log_shipping_monitor_secondary procedure helps to get log shipping health details of a particular secondary database to monitor Transaction Log. Here, the secondary database server and secondary database name will be the input parameter.
|
1 |
EXEC sp_help_log_shipping_monitor_secondary @secondary_server, @secondary_database |
sp_help_log_shipping_primary_database procedure helps to get log shipping configuration and health details of the primary database by the primary database name and primary database reference from the msdb.dbo.log_shipping_primary_databases table.
|
1 |
EXEC sp_help_log_shipping_primary_database @database, @primary_id |
sp_help_log_shipping_secondary_database to get log shipping configuration and health details of the secondary database by the secondary database name and secondary database reference from the msdb.dbo.log_shipping_secondary table.
|
1 |
EXEC sp_help_log_shipping_secondary_database @secondary_database, @secondary_id |
sp_help_log_shipping_primary_secondary to get log shipping configuration details of the primary database and secondary database with the log shipping health details of the primary database by the primary database name as an input parameter. Data will be fetched from the msdb.dbo.log_shipping_primary_databases & msdb.dbo.log_shipping_primary_secondaries table.
|
1 |
EXEC sp_help_log_shipping_primary_secondary @primary_database |
sp_help_log_shipping_secondary_database to get log shipping configuration details of the secondary database and secondary database with the log shipping health details of the secondary database by the secondary database name and secondary database reference as an input parameter. Data will be fetched from the msdb.dbo.log_shipping_secondary & msdb.dbo.log_shipping_secondary_databases table.
|
1 |
EXEC sp_help_log_shipping_secondary_database @secondary_database, @secondary_id |
SQL Server Log shipping Error list
The T-SQL query stated below is useful for tracking Log shipping errors. For both publisher end and subscriber end, log_shipping_monitor_error_detail table will be available with error details. This table can help immensely with error troubleshooting. If log shipping is broken with any one of the job steps, fail.
|
1 2 |
SELECT CASE agent_type WHEN 1 THEN 'Backup' WHEN 2 THEN 'Copy' WHEN 3 THEN 'Restore' END as agent_type, * from msdb..log_shipping_monitor_error_detail |
Log shipping error recovery is a bit difficult for what we see here. A user needs to apprehend and monitor many things at the publisher and subscriber end. Even for the user, transaction log database size is also important because, for the Disaster Recovery solution, Log shipping is used. If the transaction is too long and taking more time to get copied at the destination side (subscriber end), then there may be a delay in the restoration, and if any restore activity does not happen in (n) minutes, then it should raise an alert to the user.
There can be many more challenges for the log shipping configuration, such as Network issues, Folder access permission issues if any changes occur in the directory permission, Shared folder directory changes, SQL Server agent service issue (because all things are done by SQL jobs), etc… You don’t have to take any additional log backups, once you configure a database for log shipping. Backup, copy, and restore job automatically performs the event; log shipping will start failing if the LOG CHAIN is disturbed.
Conclusion
As mentioned earlier, monitoring the database servers is one of the important tasks for DBAs, and we explored the transaction log shipping feature in this article and how we can monitor the Transaction Log process using T-SQL.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020