Monitoring Subscriptions Automatically
-
Hello Everyone-
Yesterday, one of my developers reported that he was experiencing a problem that he suspected was due to replication between our primary and backup servers. It turns out that he was correct.
Upon my investigation, I found that a number of subscriptions were missing. I have no proof of what happened, but I suspect that the subscriptions were removed during the installation of the most recent release.
So anyhow, I have a custom report designed to monitor the primary server. I want to add a new check that will look at each published database and determine if there is a corresponding subscription. If it cannot find a subscription, then it should be noted in my report the next morning. That way in the future when a subscription is removed, I will know within 24 hours and can pinpoint who may be involved.
I am hoping that someone has enough knowledge of the sprocs and system tables related to replication that they could help me come up with a t-sql solution.
Psuedo Code:
Check each database for publication
If database has a publication, check to see if a subscription exists.
If no subscription exists, log database.
Perhaps someone knows of an article where this has been discussed?
Thanks,
Scott
-
I should add that I intend to add this to my report on the publishing server. And that the subscriptions are all push subscriptions created on the publisher.
Regards,
Scott
-
I found that in each published databases there are two tables, sysmergepublications and sysmergesubscriptions. In sysmergepublications, there is one record for each publication for that database. In sysmergesubscriptions, there are two records per subscription. One refers to the subscribing server and for some reason, there is a second "subscription" referring to the publishing server.
My thought is that I could write a query that would check to make sure that for each database that I expect to be replicated there are entries in the sysmergepublications table. For each entry in sysmergepublications, there must be two entries in sysmergesubscriptions. If the above requirements are not met, then there must be a problem so include it in my morning report.
At this point, I think that this logic should work. Anyone have concerns about counting on these two tables (or a link to a good resource on what the contents mean)?
Thanks,
Scott
-
I haven't completed the finished product, but thought that I'd pass along the way that I am implementing my solution.
I have basically used the code from sp_helpmergepublication to come up with my solution. The system sproc returns a field on a publication that indicates whether a subscriber exists. I am creating a lookup table to indicate which databases are expected to published. So, with the use of sp_MSforeachdb I can parse through all of the databases, check if they should be published, check if they are not published and finally check to see that a corresponding subscription exists.
If any unexpected results appear, they will be included in my daily report.
Regards,
Scott
-
How did you make out with this? I need to do the same but I need to find the status of each subscription. I have a number that are in pending status for an unknown reason. This needs to be remedied.
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
You know, it looks like I never implemented it. Although I can't remember the reason, it must be because other things came up.
If you try implementing it, let me know how it turns out.
Regards,
Scott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply