SQL Server – Detecting Push-Subscription Information on Subscriber Side

azure-sql-databasereplicationsql server

I've got a database in Azure SQL. There may be a push-subscription from on-premises database. I have no access to on-premises servers where distribution/publication/subscription is created and configured.

There are many many ways to get information about subscription from dictributor or publisher's side, they are easily googled. At the same time, I did not manage to find a way to detect subscription existence/health at the subscriber's side.

My current way of detection is indirect. Namely, I know the table where new records appear often and just count the number of the records and then again a minute after. This does not help in the night, when no activity in the database, and this is inconvenient due to necessity to wait a minute or more.

What I need is a script that clearly and directly shows me, if the subscription exists, and probably/if possible some additional information:
* if it is active (does push-side work properly at the moment)
* which articles are configured, etc

Best Answer

You have 4 tables For transactional replication (push subscription) at the subscriber's side:

MSreplication_objects

contains one row for each object that is associated with replication in the Subscriber database

MSreplication_subscriptions

contains one row of replication information for each Distribution Agent servicing the local Subscriber database

MSsubscription_agents

is used by Distribution Agent and triggers of updateable subscriptions to track subscription properties

MSsnapshotdeliveryprogress

is used to track files that have been successfully delivered to the Subscriber when a snapshot is being applied

you can use this columns to monitor the status for your replication at the subscriber's side

SELECT login_time,
       last_sync_status,
       last_sync_summary,
       last_sync_time,
       spid 
FROM dbo.MSsubscription_agents

Related Solutions

Sql-server – SQL 2005 Replication – How to get the DA Profile MaxBCP option to work with a Transactional Publication

I'd like to thank Hilary Cotter for providing the answer to my dilemma. His contribution to the SQL Server community is greatly appreciated!

To address the issue we needed to recreate the publication and specify the sp_addpublication argument @sync_method = 'native'.

According to SQL Server 2005, 2008 and 2008 R2 Books Online, the default @sync_method is character for snapshot publications (non-concurrent character mode BCP output) and concurrent_c (concurrent character mode BCP output) for all other publication types. However, in my experience, the actual defaults are native (non-concurrent native mode BCP output) for snapshot publications and concurrent (concurrent native mode BCP output) for all other publication types. This is actually a good thing as the native SQL BCP snapshot format is faster/more efficient to deliver to the subscriber. The character formats are only necessary for publications with non-SQL Server publishers.

Also from SQL Server Books Online: When applying a snapshot that was generated at the Publisher using the concurrent snapshot option, one thread is used, regardless of the number you specify for MaxBcpThreads.

The default in SQL Server 2000 was native and according to SQL Server 2012 Books Online, the default has been changed back to native for all SQL Server publications.

Now that I've recreated the publication using the argument @sync_method = 'native', I can now push multiple BCP files simultaneously to the subscriber, thus taking full advantage of the large pipe between our site and our customer's site.

I just finished blogging on this topic (with more detail) here: http://mattslocumsql.blogspot.com/2012/06/distribution-agent-for-transactional.html

Hilary Cotter's swift response was invaluable to solving my issue. Thank you again, sir!

Sql-server – Transactional Replication snapshot locking Publisher db from beginning

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication  
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal'

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!

Best Answer

Related Solutions

Sql-server – SQL 2005 Replication – How to get the DA Profile MaxBCP option to work with a Transactional Publication

Sql-server – Transactional Replication snapshot locking Publisher db from beginning

Related Question