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

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.

Chris Skorlinski
Microsoft SQL Server Escalation Services