Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Steps to add Log Shipping monitor into an existing SQL Server

MSSQLTips author Jugal Shah By:   |   Read Comments (9)   |   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




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
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!


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

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


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/


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.


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, April 01, 2013 - 3:50:33 PM - Chitrarekha Saha Read The Tip

You are the best!


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

Thanks


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

Hi Jugal,

Monitor server instance is inactive.How to make active ?


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.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.