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

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 1

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

Answer 2

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: