Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 30 May 2014

How to know push/pull method of subscriptions in replication?

How to know push/pull method of subscriptions in replication?




Hi,

Publication

Push means that the agent will run on the distributor side of the replication topology, while pull will run on the subscriber side.

subscription 

Push means that the agent will run on the  subscriber side of the replication topology, while pull will run on the side distributor 

there are a few ways to see if a subscription is a push or a pull.

You can see this easily by opening up Object Explorer in SSMS and navigating to the replication folder. Open up "Local Publications" and find the publication you are interested in. All subscriptions are listed below the publication, the icon for each subscription will be either "fully coloured" or "washed out" to show a local subscription agent (push) or a remote subscription agent (pull).
The picture below shows a pull subscription (number 1) and a push subscription (number 2)
Subscriber Overview
You can also see this information in system tables. Something like this script, run on the publication server should provide you with some useful information:

    DECLARE @PublicationName sysname
    SET @PublicationName = 'MyPublication'
     
    SELECT pub.name Publication,
    art.name Article,
    sub.srvname DestinationServer,
    sub.dest_db DestinationDatabase,
    CASE WHEN subscription_type = 1 THEN 'Pull' ELSE 'Push' END AS SubscriptionType
    FROM dbo.syspublications AS pub
    INNER JOIN dbo.sysarticles AS art ON art.pubid = pub.pubid
    INNER JOIN dbo.syssubscriptions AS sub ON art.artid = sub.artid
    WHERE pub.name = @PublicationName