What is a “virtual” Subscription in Transactional Replication

Share this Post

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  ******/

SELECT [artid]

      ,[srvid]

      ,[dest_db]

      ,[subscription_type]

      ,[distribution_jobid]

  FROM [AdventureWorksLT].[dbo].[syssubscriptions]

 

artid       srvid  dest_db                    subscription

     _type       distribution_jobid

———– —— ————————– ———— ——————

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.


Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.