SQLServerCentral Article

Adding a log shipping monitor

,

Have you ever spent time setting up your log shipping servers and configuring the necessary backup directories, backup schedules, and restore schedules only to remember, after the fact, that you needed to add the log shipping monitor?  It was a bit disappointing to find the following note on Microsoft's website

To monitor a log shipping configuration, you must add the monitor server when you enable log shipping. If you add a monitor server later, you must remove the log shipping configuration and then replace it with a new configuration that includes a monitor server.

What I hope to show here is how this can be done without removing and rebuilding our log shipping configuration.  Here's our test setup and what we'll be accomplishing.

  1. Three SQL Server 2008 instances with SP1 applied.  Primary, Secondary, and our monitoring instance.  (Primary - SQL2008LSDEMO\LS1, Secondary - SQL2008LSDEMO\LS2 and Monitor - SQL2008LSDEMO\LS3)
  2. Log shipping setup using the Adventureworks2008R2 sample database found here
  3. Describe the parameters of both msdb.dbo.sp_processlogshippingmonitorprimary and msdb.dbo.sp_processlogshippingmonitorsecondary.
  4. Verify the monitor is working correctly.

Setting up log shipping (I won't be covering every detail here but just a quick run through.)

1.  Change the recovery mode for the AdventureWorks2008R2 database to full and then take a full backup.

2.  To add our monitor we'll need to collect information from the log_shipping_monitor_primary system table found in the MSDB database using the following query.  This will need to run on the PRIMARY server in the log shipping configuration.

USE MSDB
GO
SELECT
 primary_id,
 primary_server,
 primary_database,
 backup_threshold,
 threshold_alert,
 threshold_alert_enabled,
 history_retention_period
FROM msdb.dbo.log_shipping_monitor_primary 
WHERE primary_database = 'AdventureWorks2008R2'  --DB you're working with

3.  Next we'll need to collect information from the log_shipping_monitor_secondary system table found in the MSDB database using the following query.  This will need to run on the SECONDARY server in the log shipping configuration.

USE MSDB
GO
SELECT
 secondary_id,
 secondary_server,
 secondary_database,
 restore_threshold,
 threshold_alert,
 threshold_alert_enabled,
 history_retention_period
FROM msdb.dbo.log_shipping_monitor_secondary 
WHERE primary_database = 'AdventureWorks2008R2' 
AND primary_server = 'SQL2008LSDemo\LS1'

4.  The next query will need to be executed on the new monitoring instance.  Here is where we'll use our collected values from the previous two queries.

USE MSDB
GO
--Add the primary to the monitor
EXEC msdb.dbo.sp_processlogshippingmonitorprimary
 @mode = 1
,@primary_id = '6D35B8B0-74A7-49D5-9C73-88F620E8414D'
,@primary_server = N'SQL2008LSDEMO\LS1'
,@monitor_server = N'SQL2008LSDEMO\LS3' --Intended monitor server
,@monitor_server_security_mode = 1
,@primary_database = N'AdventureWorks2008R2'
,@backup_threshold = 5
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
--Add the secondary to the monitor
EXEC msdb.dbo.sp_processlogshippingmonitorsecondary
 @mode = 1
,@secondary_server = N'SQL2008LSDEMO\LS2'
,@secondary_database = N'AdventureWorks2008R2'
,@secondary_id = '134B1AC3-2B4E-4D39-B779-BEAFEBEED67C'
,@primary_server = N'SQL2008LSDEMO\LS1'
,@primary_database = N'AdventureWorks2008R2'
,@restore_threshold = 5
,@threshold_alert = 14421
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@monitor_server = N'SQL2008LSDEMO\LS3' --Intended monitor server
,@monitor_server_security_mode = 1

 5.  We’ll pause here for a description of each parameter.

sp_processLogShippingMonitorPrimary

Parameter

Description

@mode

Restore mode of the secondary database.

0 = Restore log with NORECOVERY. 

1 = restore log with STANDBY

@primary_id

ID of the primary database for the log shipping configuration.

@primary_server

Name of the primary instance in the log shipping configuration.

@monitor_server

Name of the monitoring instance in the log shipping configuration.

@monitor_server_security_mode

The security mode used to connect to the monitor server.

1 = Windows Authentication

0 = SQL Server Authentication

@primary_database

Name of the primary database for the log shipping configuration

@backup_threshold

Length of time, in minutes, after the last backup before a threshold alert is raised.

@threshold_alert

The alert to be raised when the backup threshold is exceeded.

@threshold_alert_enabled

1 = enabled, 0 = disabled

@history_retention_period

Length of time in minutes in which the history will be retained.

sp_processLogShippingMonitorSecondary

Parameter

Description

@mode

Restore mode of the secondary database.

0 = Restore log with NORECOVERY. 

1 = restore log with STANDBY

@secondary_id

ID of the secondary database for the log shipping configuration.

@secondary_server

Name of the secondary instance in the log shipping configuration.

@monitor_server

Name of the monitoring instance in the log shipping configuration.

@primary_server

Name of the primary instance in the log shipping configuration.

@primary_database

Name of the primary database for the log shipping configuration

@monitor_server_security_mode

The security mode used to connect to the monitor server.

1 = Windows Authentication

0 = SQL Server Authentication

@primary_database

Name of the primary database for the log shipping configuration

@backup_threshold

Length of time, in minutes, after the last backup before a threshold alert is raised.

@threshold_alert

The alert to be raised when the restore threshold is exceeded.

@threshold_alert_enabled

1 = enabled, 0 = disabled

@history_retention_period

Length of time in minutes in which the history will be retained.

6.  After running this query check to ensure the LSAlert job was created on the monitoring instance.

7.  Now that we have the new monitoring instance setup we can update the primary and secondary instances with the monitor server name.  Run the following query on the primary instance.

USE MSDB
GO
UPDATE msdb.dbo.log_shipping_primary_databases
 SET monitor_server = 'SQL2008LSDEMO\LS3'
, user_specified_monitor = 1
 WHERE primary_id = '6D35B8B0-74A7-49D5-9C73-88F620E8414D' 
                    --ID of primary database

8. Run this query on the secondary instance.

USE MSDB
GO
UPDATE msdb.dbo.log_shipping_secondary
 SET monitor_server = 'SQL2008LSDEMO\LS3'
 , user_specified_monitor = 1
 WHERE secondary_id = '134B1AC3-2B4E-4D39-B779-BEAFEBEED67C' 
                      --ID of secondary database

 9. Verify the log shipping monitor is now enabled from the database properties page.

10.  Verify that the monitor is receiving updates by reviewing the Transaction Log Shipping Status Report on the new monitor. 

 

11.  Once verified, the old LSAlert jobs on the primary and secondary instances can be deleted.

Thanks for reading!

Rate

4.91 (11)

You rated this post out of 5. Change rating

Share

Share

Rate

4.91 (11)

You rated this post out of 5. Change rating