Determine number of rows that need to sync between merge replication subscriber and publisher
-
I am currently working with a merge replication environment that consists of a large central server with multiple filtered subscriber servers. The replication scenario is pull/push (work happens on subscriber) with 8 publications and 2 of those being filtered. We are currently working through the go-live for the system that will include a staggered implementation of the subscribers at geographically dispersed sites over several months.
They team that is deploying the subscription servers will have to travel long distances to deploy servers at the remote locations. While the teams are traveling to the site in vehicles the servers will of course be off-line and will be falling behind in production transactions, the time off line can be up to 10 business days. When the servers are on-line again at the remote locations they will become aware of the distributor and then pull down any data delta that has occurred while they were off-line. BTW, we have tested this and it works.
I would like to build a status query that can determine how many more rows need to be merged with the subscription server. I have looked at a query that looks at distribution.msmerge_history, but basically it is only telling what the last message is from the merge agent. I have also been looking at the msmerge_contents table. This looks promising but I have been unable to determine how to get the information I need from the GUIDs in this table. Remember some of these publications are filtered so I would need to be able to determine which rows are going to the subscriber in question and which are not.
Does anyone have a good suggestion on how to determine how many more rows are left to merge between a distributor and a subscriber by querying either the distributor or subscriber?
Thanks,
Chris
-
Chris,
I was actually told my Microsoft that it isn't possible to determine how many rows are left in the queue to be sent to a subscriber. I somehow don't believe that is true. I think you can tell my determining which generations a subscriber has received from the publisher. But I think it gets kind of tricky.
Anyway, here is a query that will tell you how many rows are in each generation for each publication. You can figure out how many rows your subscriber might receive by factoring the number of days it has been offline and changing the DATEADD function value. This is not 100% accurate since you are dealing with filtered publications. But it will give you a general idea.
SELECT sp.name as publication_name, count(mc.tablenick) as number_of_rows
FROM msmerge_contents mc
join msmerge_genhistory mg
on mc.generation = mg.generation
join sysmergearticles sa
on sa.nickname = mg.art_nick
join sysmergepublications sp
on sp.pubid = sa.pubid
WHERE mg.coldate > dateadd(dd, -3, getdate())
GROUP BY sp.name
ORDER BY sp.name
You are probably already aware that you will need to have the servers in place within 14 days or the subscriptions will become invalid. Unless you have changed the setting on the publisher.
Hope this helps.
-Jason
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply