What is a “virtual” Subscription in Transactional Replication
The SQL Replication system table syssubscriptions contains one row per published article per subscriber. Notice the “virtual” subscriber. With the immediate_sync property is set to true, “virtual” subscriptions are created as placeholders for retaining generated snapshot (transactions). These “virtual” subscriptions are activated just like normal subscriptions by the Snapshot agent and are used to track LSN when the Snapshot was started. This informaiton is used by the Distribution database cleanup Agent to ensure when “immediate sync” is enable to keep all transactions since the Snapshot create LSN.
/****** syssubscriptions ******/
artid srvid dest_db subscription
———– —— ————————– ———— ——————
1 –1 virtual 0 0x0000000000000000
2 –1 virtual 0 0x0000000000000000
1 0 AdventureWorksLT_TranSub1 0 0x639914D00C0CE447
2 0 AdventureWorksLT_TranSub1 0 0x639914D00C0CE447
1 0 AdventureWorksLT_TranSub2 1 0xFDEB880A10B22340
2 0 AdventureWorksLT_TranSub2 1 0xFDEB880A10B22340
Subscription_type =0 are PUSH subscriptions
Subscription_type =1 are Pull subscriptions
Other columns of interest but not displayed here include status, timestamp, and nosync_type which are covered in SQL Books Online.