question

sqlqa avatar image
sqlqa 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
replicationsnapshot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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

subscribers.png (3.1 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank You WilliamD. More Appreciated. This is I want. Once AGain thanks
0 Likes 0 ·
+1. Our resident replication guru :)
0 Likes 0 ·
yen89 avatar image
yen89 answered
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];
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.