Is there any way to detect and get information on a push-subscription on subscriber’s side?

Posted on

Question :

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

Answer :

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

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

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

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

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

This is the way I have been doing it, not sure if it would work in Azure though

first you get Aaron Bertrand’s sp_foreachdb replacement for sp_MSforeachdb

then you use it in the script below:

---------------------------------------------------------
-- get all info about subscribers in a server
-- marcello miorelli
-- 23-dec-2017
---------------------------------------------------------
IF object_id('TEMPDB..#RADHE_SP2') IS NOT NULL
    DROP TABLE #RADHE_SP2

    create table #RADHE_SP2 (
        publisher sysname NOT NULL,  
        publisher_db sysname NOT NULL, 
        publication sysname NOT NULL, 
        replication_type int,
        subscription_type int,
        last_updated datetime,
        subscriberd_db sysname,
        update_mode int,
        last_sync_status int,
        last_sync_summary nvarchar(4000),
        last_sync_time datetime)


declare @db_list NVARCHAR(MAX)

SELECT @db_list  = STUFF((
                        SELECT ', ' + name
                         FROM sys.databases d
                        WHERE 1=1
                          and d.[state] = 0
                        FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
--select @db_list

--exec sp_foreachdb @database_list = @db_list
--               ,@command='use ?; print db_name()'   


EXEC sp_foreachdb
@database_list = @db_list,
@command=
'use ?; 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

insert into #RADHE_SP2
exec sp_MSenumsubscriptions

' 

SELECT * FROM #RADHE_SP2

that will give you something like this:
enter image description here

Leave a Reply

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