asked
How to know push/pull method of subscriptions in replication?
Hi, We are using SQL 2005, Using Snapshot replication in our Server. In Subscriptions Some databases are using PUSH method and some databases are used PULL method in replication My question is I want to know which databases are used in PULL method in subscriptions and which databases are used PUSH method in subscriptions of replication? Is any script or GUI available in SQL? find the attachment for your ref: ![alt text][1] [1]: /storage/temp/1494-push+and+pull+subscriptions+in+replication.jpg
Hi, there are a few ways to see if a subscription is a push or a pull. Push means that the agent will run on the distributor side of the replication topology, while pull will run on the subscriber side. 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][1] 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 [1]: /storage/temp/1495-subscribers.png
I think this should do something from the top level if you want to look at all databases (ran in SQL 2014) USE [distribution]; GO SELECT [pub].[publisher_db] , [pub].[publication] [Publication] , [art].[article] [Article] , [s].[srvname] AS [Destination_Server] , [sub].[subscriber_db] AS [Destination_Database] , CASE WHEN [sub].[subscription_type] = 1 THEN 'Pull' ELSE 'Push' END AS [SubscriptionType] FROM [dbo].[MSpublications] AS [pub] INNER JOIN [dbo].[MSarticles] AS [art] ON [art].[publication_id] = [pub].[publication_id] INNER JOIN [dbo].[MSsubscriptions] AS [sub] ON [sub].[article_id] = [art].[article_id] INNER JOIN [dbo].[sysservers] AS [s] ON [sub].[subscriber_id] = [s].[srvid];
16 People are following this question.
