Monitoring SQL Server Log Shipping with sqlcmd
By: Daniel Farina | Updated: 2018-01-03 | Comments (1) | Related: More > Log Shipping
With the release of SQL Server 2017, Microsoft now offers SQL Server on Linux architectures where almost everything is done using the console. This implies a challenge for us where we have spent most of our careers using graphical interfaces like SQL Server Management Studio (SSMS). We know that we can use sqlcmd to execute scripts and SQL statements from the command line, but when the result set has many columns the results are hard to read.
In this tip I will cover a solution that can help you to monitor log shipping from the command line using sqlcmd.
Log shipping is the most basic technology that SQL Server offers for high availability and has been around since its first versions. It is based on backup and restore operations that are scheduled to be executed from the SQL Server Agent service. One of the advantages of log shipping is that you can have as many replicas as you want. On the other hand, its limitation is that those copies are read only if the restored database is set to standby or in a restoring state.
In the days before Always On Availability Groups were released, log shipping was the only high availability solution available that allowed read only replicas. Not even database mirroring, which has been deprecated, allowed this. Having a read only replica is useful in cases when an application needs to create reports on a transactional database because it allows you to offload the reporting queries to the read only database and avoid performance issues like blocking.
Another interesting feature of log shipping is that it isn’t tied to a specific operating system version. As a matter of fact you can configure log shipping between two servers A and B running Windows 2016 and Windows 2012 respectively. The only limiting factor is the database compatibility level. That is something that you cannot do with Always On. Also, you can configure log shipping between a Windows and a Linux server, this is something that can only be done with log shipping.
Using sqlcmd to Monitor Log Shipping
Sqlcmd is a very versatile tool, but it has an issue, we don’t have a horizontal scroll bar. Of course, this is not a fault of sqlcmd, but console applications are unable to scroll horizontally and sqlcmd doesn’t escape this limitation. This is the motive that forces us to find a way to execute queries we already know to monitor log shipping with a little bit of ingenuity.
The solution I propose consists on rewriting those queries to fit the screen. Take for example the tip Different ways to monitor Log Shipping for SQL Server databases. In that tip the author mentions three system stored procedures that allow us to monitor log shipping and get detailed information about its status.
- sp_help_log_shipping_monitor - This procedure returns the log shipping status
- sp_help_log_shipping_monitor_primary - This stored procedure is a wrapper to the log_shipping_monitor_primary table of the msdb database.
- sp_help_log_shipping_monitor_secondary - Just like the previous stored procedure, it is also a wrapper but for the log_shipping_monitor_secondary table of the msdb database.
Instead of rewriting the queries in these stored procedures, I saved the queries to script files so we can re execute them with sqlcmd by using the :r command of the sqlcmd shell. In my previous tips I suggested that it may be wise to save specific queries to files and copy those files to a folder on each new Linux server you deploy.
Adapting Log Shipping Stored Procedures to Use from Console
The following shows how I adapted these log shipping store procedures to be used and be useful from a sqlcmd console.
In order to adapt this stored procedure to fit the screen we can save its results to a temporary table. Then we can display the information using different SELECT statements as follows.
SET NOCOUNT ON create table #LSMonitor ( status bit null ,is_primary bit not null default 0 ,server sysname ,database_name sysname ,time_since_last_backup int null ,last_backup_file nvarchar(500) null ,backup_threshold int null ,is_backup_alert_enabled bit null ,time_since_last_copy int null ,last_copied_file nvarchar(500) null ,time_since_last_restore int null ,last_restored_file nvarchar(500) null ,last_restored_latency int null ,restore_threshold int null ,is_restore_alert_enabled bit null ,primary key (is_primary, server, database_name) ) INSERT INTO #LSMonitor EXEC sys.sp_help_log_shipping_monitor SELECT server , database_name , status , is_primary , time_since_last_backup , last_backup_file FROM #LSMonitor SELECT server , database_name , is_backup_alert_enabled , is_restore_alert_enabled FROM #LSMonitor SELECT server , database_name , backup_threshold , time_since_last_copy , last_copied_file FROM #LSMonitor SELECT server , database_name , time_since_last_restore , last_restored_file FROM #LSMonitor SELECT server , database_name , last_restored_latency , restore_threshold FROM #LSMonitor DROP TABLE #LSMonitor GO
Notice that all of the select statements return the columns server and database_name. This is to maintain an order and aid the reading because they act as a primary key.
I saved the previous script on a on a file named LSMonitor.sql in the folder where I have all my scripts.
On the following screen capture you can see the output of this script when it is being executed on sqlcmd through a ssh connection to a SQL Server database running Log Shipping on Linux.
Since this stored procedure is a wrapper to msdb’s log_shipping_monitor_primary table, I opted to go straight to the source and query that table instead. In this case only four select statements were needed for this script which I named LSMonitorPrimary.sql.
SET NOCOUNT ON SELECT primary_id , primary_server , primary_database , backup_threshold FROM msdb.dbo.log_shipping_monitor_primary SELECT primary_server , primary_database , threshold_alert , threshold_alert_enabled FROM msdb.dbo.log_shipping_monitor_primary SELECT primary_server , primary_database , last_backup_file , history_retention_period FROM msdb.dbo.log_shipping_monitor_primary SELECT primary_server , primary_database , last_backup_date , last_backup_date_utc FROM msdb.dbo.log_shipping_monitor_primary GO
As you can see in the image below the result set of this query fits the console screen and therefore is easy to read when you need to act fast in case of a problem on the primary server.
Just like in the previous case, I also opted to query the msdb table which in this case is log_shipping_monitor_secondary. As you may see in the script below, it took me five SELECT statements to display all the information in such a way that makes it legible. I created a script named LSMonitorSecondary.sql with the following content.
SET NOCOUNT ON SELECT secondary_id , secondary_server , secondary_database , primary_server , primary_database FROM msdb.dbo.log_shipping_monitor_secondary SELECT secondary_id , restore_threshold , threshold_alert , threshold_alert_enabled FROM msdb.dbo.log_shipping_monitor_secondary SELECT secondary_id , last_copied_file , last_copied_date , last_copied_date_utc FROM msdb.dbo.log_shipping_monitor_secondary SELECT secondary_id , last_restored_file , last_restored_date , last_restored_date_utc FROM msdb.dbo.log_shipping_monitor_secondary SELECT secondary_id , last_restored_latency , history_retention_period FROM msdb.dbo.log_shipping_monitor_secondary GO
On the next screen capture you can see the results of executing this script with sqlcmd through a ssh connection.
In case something fails with the log shipping jobs, you will need to query the log_shipping_monitor_error_detail table. This msdb table stores error detail for log shipping jobs for both primary and secondary servers. In this table, each error is logged as a sequence of exceptions. There can be multiple errors (sequences) for each agent session.
Since this table can contain a lot of rows I decided to show only the last five rows, but you can change the script according to your needs. I created a file named LSError.sql with the following content.
SET NOCOUNT ON SELECT TOP ( 5 ) agent_id , agent_type , session_id , sequence_number , database_name , log_time FROM msdb.dbo.log_shipping_monitor_error_detail ORDER BY log_time , agent_id , agent_type , session_id , sequence_number SELECT TOP ( 5 ) agent_type , session_id , sequence_number , message , source , help_url FROM msdb.dbo.log_shipping_monitor_error_detail ORDER BY log_time , agent_id , agent_type , session_id , sequence_number GO
As you can see in the next image, our query fits the console screen giving us fast insight about the problem of our log shipping configuration.
- In case you are new to Log Shipping, the following tip can help you understand the basics: SQL Server Log Shipping.
- Also, if you need a more in depth explanation of Log Shipping, you can take a look at this tutorial: Step By Step SQL Server Log Shipping.
- If you never used sqlcmd, I suggest that you read the following tip: Introduction to SQL Server's sqlcmd utility.
- Also the next two tips may be as a great help for you: Execute SQL Server Script Files with the sqlcmd Utility and Different Ways to Connect to SQL Server Using sqlcmd.
- For more information regarding Log Shipping take a look at SQL Server Log Shipping Tips Category.
Last Updated: 2018-01-03
About the author
View all my tips