Sql-server – If a SQL Server Replication Distributor and Subscriber are on the same server, should a PUSH or PULL subsciption be used

database-replicationsql serversql-server-2012transactional-replication

Thanks in advance for any help.

I'm setting up a new Microsoft SQL Server replication and I have the Distributor and Subscriber running on the same server. The Publisher is on a remote server (as it is a production database and MS recommends that for high volumes, the Distributor should be remote).

I don't know much about the inner workings of PUSH vs PULL subscriptions, but my gut tells me that a PUSH subscription would be less resource intensive because (1) the Distributor is already remote, so this shouldn't negatively effect the Publisher and (2) pushing the transactions from the Distributor to the Subscriber is more efficient than the Subscriber polling the Distribution database.

Does any one have any resources or insight into PUSH vs PULL which would recommend one over the other? Is there really going to be that big of a difference in performance / reliability / security?

Best Answer

I'd personally set it up as a push because it's slightly easier to administer.

For the purposes of this discussion, the difference between a push and a pull is where the distribution agent gets run. For a push, it gets run at the distributor. For a pull, it gets run at the subscriber.

However, since those are the same here, it doesn't matter. However, to set up a pull subscription, you need to add one more stored procedure call in the mix when you're configuring replication (i.e. sp_addpullsubscription).

Related Solutions

Sql-server – SQL 2008 publisher -> SQL 2000 subscriber: Is a pull subscription possible for merge replication

Are you able to use Query Analyzer on the SQL 2000 server to connect to the SQL 2008 server? If that doesn't work nothing else will.

Start with the basics. Is the SQL Browser running on the SQL 2008 server? Can you ping the SQL 2008 server from the SQL 2000 server? Can you access the TCP port that the SQL 2008 instance is listening on?

Sql-server – Sql Server 2005 Replication: How to setup subscription with a sql script

Figured it out. When generating the subscription directly from management studio, it automatically starts the snapshot agent. However, the script that management studio generates for the subscription does not start the snapshot agent. Adding the following line to the generated script makes things work:

EXEC sp_startpublication_snapshot @publication = N'InstitutionPublication'

So my full script for the subscriber after modification is:

-----------------BEGIN: Script to be run at Publisher 'SERVER\INSTANCE'-----------------
-----------------BEGIN: Script to be run at Publisher 'SERVER\INSTANCE'-----------------
use [Profile]
exec sp_addsubscription @publication = N'PublicationName', @subscriber = N'server\instance', @destination_db = N'destinationDatabaseName', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PublicationName', @subscriber = N'server\instance', @subscriber_db = N'destinationDatabaseName', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20091104, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'SERVER\INSTANCE'-----------------

EXEC sp_startpublication_snapshot @publication = N'PublicationName';
GO

Best Answer

Related Solutions

Sql-server – SQL 2008 publisher -> SQL 2000 subscriber: Is a pull subscription possible for merge replication

Sql-server – Sql Server 2005 Replication: How to setup subscription with a sql script

Related Topic