Free SQL Help for Devs, DBAs, and the Curious

As data professionals, we are constantly monitoring and managing data in various database platforms. In this blog post, we are going to discuss a very important aspect in the world of data management, that is, creating a Log Shipping Monitoring Dashboard using Transact-SQL (T-SQL) and Views. We will focus on SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1 and Azure SQL.

Log shipping is a high-availability option that involves automating the backup of transaction log files on a primary database server, and then restoring them onto a standby server. The key to managing Log Shipping effectively is to have a comprehensive monitoring system. Therefore, building a Log Shipping Monitoring Dashboard becomes crucial for database administrators (DBAs).

Let's start with SQL Server. We will use T-SQL to query the log_shipping_monitor_primary and log_shipping_monitor_secondary system tables to get the status of our log shipping setup. Here is an example of how you can do it:

-- T-SQL code
SELECT 
    p.database_name,
    p.last_backup_file,
    p.last_backup_date,
    s.last_restored_file,
    s.last_restored_date
FROM 
    msdb.dbo.log_shipping_monitor_primary p 
JOIN 
    msdb.dbo.log_shipping_monitor_secondary s ON p.primary_id = s.primary_id
WHERE 
    p.database_name = 'YourDatabaseName';

In MySQL 5.7, we don't have built-in tables for monitoring log shipping. Instead, we can use SHOW MASTER STATUS and SHOW SLAVE STATUS commands to get the status of log shipping. Here is an example:

-- MySQL code
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G;

For DB2 11.1, we can use the db2pd utility to monitor the log shipping status. Here is a simple example:

-- DB2 code
db2pd -d YourDatabaseName -logs

Lastly, for Azure SQL, we can use Catalog Views to monitor the status of log shipping. The sys.dm_operation_status dynamic management view returns information about all restore operations and the sys.dm_db_operation_status dynamic management view returns information about operations at the database level. Here is an example:

-- T-SQL code for Azure SQL
SELECT *
FROM sys.dm_operation_status
WHERE resource_type_desc = 'DATABASE'
AND major_resource_id = 'YourDatabaseName';

In conclusion, a Log Shipping Monitoring Dashboard can be built using various methods depending on the database platform in use. The examples provided above are simple, but they can be customized and enhanced to suit your specific needs. Happy monitoring!