Learn more about SQL Server tools

   
   















































Steps to add Log Shipping monitor into an existing SQL Server

MSSQLTips author Jugal Shah By:   |   Read Comments (11)   |   Related Tips: More > Log Shipping
Problem

I have a requirement to add the Log Shipping Monitor for an existing installation. I have heard you can only complete this by rebuilding the Log Shipping infrastructure.  Is that true?  Are there any other options?  In this tip I will explain how we can add the Log Shipping monitor to a SQL Server 2005, 2008, 2008 R2 or 2012 environment without rebuilding the Log Shipping installation.

Solution

The Log Shipping Monitor is used to monitor the overall Log Shipping configuration in your environment. To learn about how to configure Log Shipping please review this tip - Step by Step SQL Server Log-Shipping Setup.  If you forget to add the Log Shipping monitor while configuring the Log Shipping in SQL Server 2005, 2008, 2008 R2 or 2012, the conventional wisdom has been you need to remove Log Shipping and reconfigure it with a monitor server.

SQL Server Log Shipping System Tables

To add the Log Shipping Monitor on the existing Log Shipping setup we will use these system tables in the MSDB database:

  • log_shipping_monitor_primary - Stores one monitor record per primary database in each log shipping configuration.
  • log_shipping_monitor_secondary - Stores one monitor record per secondary database in a log shipping configuration.
  • log_shipping_primary_databases - Stores one record for the primary database in a log shipping configuration.
  • log_shipping_secondary - Stores one record per secondary ID

SQL Server Log Shipping System Store Procedures

SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This can be implemented by running the below undocumented log shipping stored procedures:

  • sp_processlogshippingmonitorprimary
  • sp_processlogshippingmonitorsecondary

Additional Parameters Needed for SQL Server Log Shipping

You must have the information below handy when you add the Log Shipping Monitoring Server in SQL Server 2005, 2008, 2008 R2 or 2012:

  • Primary SQL Server Name and Primary Database Name
  • Secondary SQL Server Name and Secondary Database Name
  • Monitoring SQL Server name

Steps to Add the SQL Server Log Shipping Monitor

In the below scenario, I used these SQL Servers and databases for adding the log shipping monitor:

  • Primary Server - D1
  • Primary Database Name - SQLDBPool
  • Secondary Server - D1\INST2
  • Secondary Database Name - SQLDBPool
  • Monitor Server Name - D1\Monitor
The screen shot below indicates that a "Monitor server instance" has not been setup for the SQLDBPool database.

SQL Server Log Shipping Monitor server instance is not configured

Step 1: Execute the query below on the Primary Server and make sure you are getting the desired results for your environment.  Once the output is confirmed we can use the below Query and specified criteria to prepare the dynamic SQL statement which will be used to add the Log Shipping Monitor.

-- Execute the below script on the Primary Server
declare @databaseName varchar(300)
set @databaseName = 'SQLDBPool'
select *  FROM msdb.dbo.log_shipping_monitor_primary where primary_database = @databaseName

Log Shipping details from the primary SQL Server

Step 2: Execute the below query on the Secondary Server and make sure you are getting the desired results for your environment. Once the output is confirmed we can use the below Query and specified criteria to prepare the dynamic SQL statement which will be used to add the Log Shipping Monitor.

-- Execute the below script on the Secondary Server
declare @databaseName varchar(300)
declare @primaryServer as varchar(300)
set @primaryServer = 'D1' --Mention the Primary Server Name
set @databaseName = 'SQLDBPool' --mention the primary database name
select * from msdb.dbo.log_shipping_monitor_secondary
where primary_database = @databaseName AND primary_server = @primaryServer

Log Shipping details from the secondary SQL Server

Step 3: The next step is to identify the recovery model (i.e. NoRecovery or StandBy) for the database on the Secondary Server which we will use while configuring the Log Shipping Monitor. Execute the below query on the Secondary Server.

--Execute the below script on the secondary server
declare @databaseName varchar(300)
set @databaseName = 'SQLDBPool' --mention the Secondary Database Name
-- 0 = Restore log with NORECOVERY.
-- 1 = Restore log with STANDBY.
select secondary_database,restore_mode 
from msdb.dbo.log_shipping_secondary_databases 
where secondary_database = @databaseName

Step 4: Assign the value to @Mode (from the step 3 output) and @monitorServer (mention monitor instance name) in the below script and execute it on the Primary Server.

-- Run the below script on Primary Server
declare @mode as int
declare @monitorServer varchar(300)
declare @databaseName varchar(300)
set @mode = 1 --mention the output value from step 3
set @monitorServer = 'D1\Monitor' -- mention the monitor server instance name
set @databaseName = 'SQLDBPool' --Mention the database name
select  
' exec msdb.dbo.sp_processlogshippingmonitorprimary ' + CHAR(13) + 
'@mode =  ' + CAST(@mode as varchar) + CHAR(13) +  
',@primary_id = ' + quotename(primary_id,'''') + CHAR(13) +
',@primary_server = ' + quotename(primary_server,'''') + CHAR(13) +
',@monitor_server = ' + quotename(@monitorserver,'''') + CHAR(13) +
',@monitor_server_security_mode =' + '1' + CHAR(13) +
',@primary_database = ' + quotename(primary_database,'''') + CHAR(13) +
',@backup_threshold = ' + cast (backup_threshold as varchar) + CHAR(13) +
',@threshold_alert = ' + cast(threshold_alert as varchar) + CHAR(13) +
',@threshold_alert_enabled = ' + cast(threshold_alert_enabled as varchar) + + CHAR(13) +
',@history_retention_period = ' + cast (history_retention_period as varchar) + CHAR(13)
FROM msdb.dbo.log_shipping_monitor_primary where primary_database = @databaseName

Step 5: Copy the output of the script executed in Step 4 and execute it on the Monitor Server. Output of the Step 4 is shown below.

--Execute the below script on the Monitor Server
exec msdb.dbo.sp_processlogshippingmonitorprimary 
 @mode =  1
,@primary_id = '2288E943-27E1-4542-93D9-18312FF5868C'
,@primary_server = 'D1'
,@monitor_server = 'D1\Monitor'
,@monitor_server_security_mode =1
,@primary_database = 'SQLDBPool'
,@backup_threshold = 60
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 5760

Step 6: Assign the @Mode (from the step 3 output) and @monitorServer (mention monitor instance name) in the below script and execute the script on the Secondary Server.

-- Execute below script on secondary server 
declare @mode as int
declare @primaryServer as varchar(300)
declare @monitorServer varchar(300)
declare @dbName varchar(300)
set @mode = 1
set @primaryServer = 'D1' -- Mention the Primary Server Name
set @monitorServer = 'D1\Monitor' -- Mention the Monitor Server Name
set @dbName = 'SQLDBPool' -- Mention the Primary Database Name
select
'EXEC msdb.dbo.sp_processlogshippingmonitorsecondary ' + CHAR (13) +
 '@mode = ' +  cast(@mode as varchar) + CHAR (13) +
',@secondary_server = ' + quotename(secondary_server,'''')  + CHAR (13) +
',@secondary_database = ' + quotename(secondary_database,'''') + CHAR (13) +
',@secondary_id = ' + quotename(secondary_id,'''') + CHAR (13) +
',@primary_server = ' + quotename(primary_server,'''') + CHAR (13) +
',@primary_database = ' + quotename(primary_database,'''') + CHAR (13) +
',@restore_threshold = '+ cast (restore_threshold as varchar)  + CHAR (13) +
',@threshold_alert = ' + cast(threshold_alert as varchar) + CHAR (13) +
',@threshold_alert_enabled = ' + cast(threshold_alert_enabled as varchar)  + CHAR (13) +
',@history_retention_period = ' + cast(threshold_alert_enabled as varchar)  + CHAR (13) +
',@monitor_server = ' + quotename(@monitorServer,'''') + CHAR (13) +
',@monitor_server_security_mode = ' +  '1' + CHAR (13) 
from msdb.dbo.log_shipping_monitor_secondary
where primary_database = @dbName AND primary_server = @primaryServer

Step 7: Copy the output of the script executed in Step 6 and execute it on the Monitor Server. Output of step 6 is shown below.

--Execute the Below script on the Monitor Server
EXEC msdb.dbo.sp_processlogshippingmonitorsecondary 
@mode = 1
,@secondary_server = 'D1\INST2'
,@secondary_database = 'SQLDBPool'
,@secondary_id = 'B5F6E3B3-5BEC-42C6-B3CC-7935E58F2BAF'
,@primary_server = 'D1'
,@primary_database = 'SQLDBPool'
,@restore_threshold = 45
,@threshold_alert = 14421
,@threshold_alert_enabled = 1
,@history_retention_period = 1
,@monitor_server = 'D1\Monitor'
,@monitor_server_security_mode = 1

Step 8: Once you finished with the execution of Step 5 and Step 7 it will create the Log Shipping Alert job on the Log Shipping Monitor Server as shown below:

SQL Server Log Shipping Alert Job

Step 9: Execute the query below on the Primary Server and it will update the Primary Server Log Shipping table with the Log Shipping Monitoring Server instance name.

-- Script will update monitor Server name into LS system table, 
-- Execute it on the Primary Server
USE MSDB
declare @monitorServer varchar(300)
declare @PrimaryDatabaseName varchar(300)
declare @SQL varchar(4000)
set @monitorServer = 'D1\Monitor' -- mention the monitor server instance name
set @PrimaryDatabaseName = 'SQLDBPool' --mention the primary database name
select @SQL=  'UPDATE msdb.dbo.log_shipping_primary_databases ' 
+ 'SET monitor_server = ' + quotename(@monitorServer,'''') 
+  ', user_specified_monitor = 1 ' + 'WHERE primary_id = ' + quotename(primary_id,'''')
FROM msdb.dbo.log_shipping_monitor_primary where primary_database = @PrimaryDatabaseName
execute (@SQL) 

Step 10: Execute the query below on the Secondary Server and it will update the Secondary Server Log Shipping table with the Log Shipping Monitoring Server instance name.

-- Script will update monitor Server name into LS system table
-- Execute it on the Secondary Server
USE MSDB
declare @monitorServer varchar(300)
declare @PrimaryDatabaseName varchar(300)
declare @PrimaryServerName varchar(300)
declare @SQL varchar(4000)
set @monitorServer = 'D1\Monitor' -- mention the monitor server instance name
set @PrimaryDatabaseName = 'SQLDBPool' --mention the primary database name
set @PrimaryServerName = 'D1' --mention the primary database name
select @SQL=   'UPDATE msdb.dbo.log_shipping_secondary ' 
+ 'SET monitor_server = ' + quotename(@monitorServer,'''') 
+  ', user_specified_monitor = 1 ' + 'WHERE secondary_id = ' + quotename(secondary_id,'''')
FROM msdb.dbo.log_shipping_monitor_secondary 
where primary_database = @PrimaryDatabaseName and primary_server = @PrimaryServerName
execute (@SQL)

Step 11: Verify the Log Shipping Monitoring is enabled by browsing the following: Database Properties > Transaction Log Shipping as shown below:

Verify SQL Server Log Shipping Monitoring is enabled

Step 12: Verify the Transaction Log Shipping Status Report on the Log Shipping Monitoring Server. You can use the Log Shipping Status report by right clicking on the server name in SQL Server Management Studio and navigating to Reports > Standard Reports > Transaction Log Shipping Status.  For more information on SQL Server Log Shipping monitoring check out this tip - Different ways to monitor Log Shipping for SQL Server databases.

Next Steps
  • Document the SQL Server Log Shipping configuration for all servers in your environment.
  • Document and customize the above scripts for adding the Log Shipping Monitor on to existing environment.
  • Create a SQL Server Agent Job to run the log shipping status report stored procedure and send an email with the details on a daily basis.


Last Update: 11/16/2012


About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, July 30, 2014 - 1:49:40 AM - Gayathri Read The Tip

Hi Jugal,

 

Can you please help me to configuring mail alrets when logshipping went wrong? i want to send mail alrets to application owner. am awaiting for your reply. could you share your ideas to me in kgayathri.cs@gmail.com.

 

Thanks in Advance.


Saturday, May 03, 2014 - 5:48:49 AM - brahmaiah Read The Tip

Hi 

This Brahmaiah

Issue : Log shipping error 14421.

How  to resolve 

1.Take full backup and restore in to secondary server 

2. checking the last restoration LSN and restoring log with using the blow command

restore verfiyonly from disk = 'path & LSN number'

3. Manually running the job.

4.Checking the time zones 

5. Reconfiguring the log shipping.

6. To build a new server.

 

 

cloud you please share your ideas step by step. you can contact me on brahmaiah_89@yahoo.com or 9677648972.


Thursday, January 16, 2014 - 2:47:52 PM - Francis McFaul Read The Tip

Hi Jugal,

 

Could you use this method to change an existing monitor server to a different server?  If not is there a better/preferred method to change the monitor server?  We are using SQL Server 2008 R2 Enterprise edition.

 

Thank you.


Friday, May 10, 2013 - 10:47:23 PM - DineshSpecial Read The Tip

Hi Jugal,

Monitor server instance is inactive.How to make active ?


Monday, April 01, 2013 - 8:45:35 PM - Jugal Read The Tip

Thanks


Monday, April 01, 2013 - 3:50:33 PM - Chitrarekha Saha Read The Tip

You are the best!


Saturday, February 23, 2013 - 7:32:27 AM - Chetan Jain Read The Tip

Hi Jugal,

 

I had a similar situation in which I had to add monitor instance.  However I found that we need to perform one more update on secondary instance:

 

update msdb.dbo.log_shipping_secondary 

set monitor_server = @monitor_instance

where primary_database = 'primary_database'

 

Without doing this, the monitor instance was not updating the copy and restore information in the msdb.dbo.log_shipping_monitor_secondary thus reporting a false Lag in Log shipping Latency Report.

 

Thanks,

Chetan


Monday, December 10, 2012 - 1:58:19 PM - Jugal Read The Tip

Luke – Thanks for the post.  It looks like we both tried to address this issue in a similar manner, but we have some different code on a few of the steps.  I also noticed that you mentioned removing the old alert job.  That is a good point in your article.


Sunday, December 09, 2012 - 10:51:41 PM - Luke Campbell Read The Tip

Funny this looks just like an article I wrote earlier this year on SQL Server Central.  http://www.sqlservercentral.com/articles/Log+Shipping/77295/


Wednesday, November 21, 2012 - 1:36:33 AM - vaibhav mishra Read The Tip

Thanks Jugal for such a interesting & useful post..... :)


Monday, November 19, 2012 - 5:40:09 AM - Devi Prasad Read The Tip

Thanks Jugal for sharing this knowledge.. As usual You are the One!




 
Sponsor Information