Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Adding a log shipping monitor

By Luke Campbell,

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!

Total article views: 4399 | Views in the last 30 days: 27
 
Related Articles
FORUM

Changing Role Between Primary and Secondary Servers in SQL Server 2005 Log Shipping:

Order of Failover and Role Switch between Primary and Secondary

FORUM

log shipping primary server

log shipping primary server

FORUM

Log Shipping Secondary Server Failed

Log Shipping Secondary Server machine went down

FORUM

How to merge SQL 2005 database files

primary and secondary database file

SCRIPT

Log Shipping - How you can convert the secondary database into primar

This topic explains the log shipping if the primary database is suspected or corrupted and how you c...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones