Merge Replication System Tables

 

The earlier blog post “How to: Detect the database is merge replicated”, gave a list of the system tables that get created in a merge subscription database. This blog post explains brief description of each of the system tables. There is another blog post “RDA Subscriptions” that describes briefly the RDA system tables.

  __sysMergeSubscriptions – Each row in this table represents a merge subscription. Note that, multiple merge subscriptions can co-exist in a single client database.

__sysMergeArticles – Each row in this table represents a merge subscription article.

__sysMergeSubscriptionProperties – Each row in this table stores merge subscription properties that are on set SqlCeReplication object. For example, Internet URL/Login/Password, Publisher Machine/UserId/Password/Database/Network, Distributor Network/Machine/Database/UserId/Password … etc.

 

List of tables that belong to a particular publication:

SELECT TableName FROM __sysMergeArticles AS sma INNER JOIN __sysMergeSubscriptions AS sms ON sma.SubscriptionId == sms.SubscriptionId AND sms. Publication == @PublicationName.

 

Every merge article/table is tracked by SQL Server Compact engine. Tracking infrastructure creates three system tables. Namely,

__sysTrackedObjects – Each row in this table represents a database object that is tracked. It can be a table, column … etc (Note: Merge replication supports column level tracking)

__sysDeletedRows/__sysRowTrack – Each row in this table represents a deleted row of a tracked table

 

Thanks,

Laxmi Narsimha Rao ORUGANTI