Merge Replication System Tables
The earlier blog post “How to: Detect the database is mere 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
