HOWTO: Detect the database is Merge Replicated?

Sometimes an application may want to know whether the database is in good shape in terms of Merge Replication especially when the merge replication is aborted by the user, or due to connection failure or many other reasons. Here are the things application should ideally check if the database is merge replicated correctly and is usable further.

1. Ensure that the following system tables exist

a. __sysMergeSubscriptions

b. __sysMergeArticles

c. __sysTrackedObjects

d. __sysRowTrack

e. __sysDeletedRows

[Note: Tables (c), (d), (e) would be created only when the client side tracking of changes is needed. If all the articles are download only then you may not these tables at all]

2. For each merge subscription in this database,

a. An entry/row should exist in __sysMergeSubscriptions

b. A valid sync partner should exist:

SELECT * FROM __sysTrackedObjects AS sto JOIN __sysMergeSubscriptions AS sms ON sto.N = sms.SyncPartnerId AND sto.T=’P’ AND sto.SV = sms.Publication

3. For each subscribed table,

a. An entry/row should exist in __sysMergeArticles

b. The table should have the following seven system columns:

                                                  i. __sysIG, __sysCG, __sysCD, __sysP1, __sysMC, __sysMCS, __sysSR


Dan Shaver