Add Monitor to your existing Log-shipping configuration

Hi All, 

Ever faced a situation where you "forgot"(??) adding a Monitor instance while configuring Logshipping? Well, I had!! These scripts saved my but in those situations.

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




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


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 theSecondary 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.

--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. 

--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.



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

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.