SQL 2005 – Log Shipping Monitoring?
I took a look at log shipping in 2005 for the first time the other day and as i was curious to see how the monitor functionality had been implemented in SQL Server Management Studio i went hunting for it. I’ve got to say i was greeted with nothing but disappointment and left feeling that i now have a huge mountain to climb before it can be used in production!
We currently have 15 databases shipped to multiple destinations and in SQL 2000 we monitor and manage from a central monitor server. In Enterprise Manager there is a log shipping monitor node under management and any databases that have failed are marked with a red cross and we can right click and suppress the alerts whilst we investigate and the investigation often starts with a right click to view the relevant history for the database in question. Amongst other things we can also automatically suppress alerts for periods of time.
Now, what did i find in 2005? After a good hunt around i found a report that you get to by going to the summary page at the server level and choose "Transaction Log Shipping status". I then found a single job on the monitor server responsible for generating the alerts for all the databases. As for viewing the histories, its a case of find the jobs or run a query against the relevant monitor tables!
In SQL 2005, log shipping has been wrapped up into its own executable and divorced from maintenance plans. I was expecting to see a number of improvements over SQL 2000 or, as a minimum, be on the same level but i was not prepared to find that out of the box it was going to be far more difficult to use! Why? Because the monitor tool in 2000 was simple and with reasonable instruction anyone could use it. I now feel i need to write at least 1 more report and i need to come up with a way of suppressing individual alerts and introducing suppressions for certain time periods to meet our business needs and ensure its maintainable by people who are not DBA’s.
I just don’t understand why Microsoft have done this!