SQL Server – Monitoring Log-Shipping for Multiple Databases

log-shippingmonitoringsql server

We are setting up log-shipping for multiple databases hosted at different server and while process began, I noticed that only one SQL job is being retained at Monitoring Server.

Our plan was to have one monitoring server which will be monitoring all the replication between Production and DR for databases and we can set-up alert in case any of the replication goes bad.

Kindly suggest how to make multiple jobs possible on the monitoring server.

Best Answer

After spending considerable amount of time in setting up log-shipping for multiple database and trying to figure out why only one alert job gets created - had no clue on why however one thing became clearer as we kept on configuring databases and jobs were getting over-written that even though there is only one job being retained at SQL Server Agent Job level, it does its job for all databases and alerts us in case any of the underlying database replication fails or has some issue in back-up, copy or restoration.

I am not sure why MSSQL doesn't support having multiple jobs created however its doing its intended job internally. I also have been checking "Transaction Log Shipping Status" report can monitor the status of all databases.

I also checked two tables from msdb database at monitoring server and ensured that log-shipping is working as expected.

Thank you for your support. This could be helpful to somebody who is setting up log-shipping for the very first time and wants to use monitoring server.

Related Solutions

Sql-server – What are the advantages of a monitor server for log shipping

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though...

Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary server, so you are none the wiser (server being down then you are not getting restore alerts that things are out of date).

You notice a couple of days later, but by then it's too late and you have to restart log shipping from scratch.

Having a monitor server just helps to keep an eye on things and gives you a warning point not related to the servers involved in log shipping (think of it kind of like the witness server in mirroring, but without the automatic failover)

Sql-server – deactivate log shipping jobs without raising errors

This normally occurs when the log-shipping is not removed cleanly. To remove the orphan entries you have to delete them from msdb logshipping tables.

On Primary :
delete from msdb..log_shipping_monitor_secondary
where secondary_database = '' -- secondary db name goes here 
delete from dbo.log_shipping_secondary
where primary_database='' -- primary db name goes here 

On Secondary:

delete from msdb..log_shipping_monitor_primary
where primary_database='' -- primary db name goes here 

delete from dbo.log_shipping_primary_secondaries
where secondary_database='' -- secondary db name goes here

Best Answer

Related Solutions

Sql-server – What are the advantages of a monitor server for log shipping

Sql-server – deactivate log shipping jobs without raising errors

Related Question