Push v. Pull Merge Replication

Chris Skorlinski
Microsoft SQL Server Escalation Services

I hope to answer the question “What is the difference between Push and Pull Merge Replication?” in this blog posting.

As you know Merge Replication is all about merging or blending changes between multiple SQL Servers.  One SQL Server is designated as the PUBLISHER or “master” copy of the database.  Changes made on the Publisher are distributed to 1 or more Subscribing SQL Servers.  Changes made on one or more Subscriber SQL Servers are consolidated on the Publisher.  The ReplMerg.exe Agent included with SQL Server is responsible for moving data from Publisher to Subscribers (download phase), and from Subscribers to Publisher (upload phase). 

IF the ReplMerg.exe executes on the Publisher we say it “pushed” changes to the Subscriber.  IF the ReplMerg.exe is running on the Subscriber it “pulls” changes down from the Publisher.  But wait, you said data goes both ways.  Yes, technically a PUSH TO also PULLs FROM from the Subscriber, and a PULL FROM also PUSHes TO the Publisher.  Maybe this is why I avoid using PULL v. PUSH but instead determine on which server, Publisher or Subscriber, is executing the Merge Agent.

Why do I care where my Merge Agent is running?

Turns out if you’re replicating data over a slow WAN to Subscribers you can get performance improvements running the Merge Agent on the Subscriber. 

Merge connects to Publisher, looks for new changes, then connects to and interrogates the Subscriber.  It runs queries to determine if/what data the subscriber already has, then updates the Subscriber accordingly.  The Subscriber connection is a bit “chatty”.  If this conversation is over a slow WAN, performance would be impacted.

By running the ReplMerg.exe on the Subscriber, the 1st query is made over the WAN to the Publisher to pull changes, then more “chatty” conversation are  locally on the Subscriber.  If you collect Profile Traces or OUTPUT from the Merge Agent you can watch these conversations.  The output below is a bit messy, but notice difference in number of  “Publisher” v. “Subscriber” conversations in the Agent Output below.  Again, goes is to have “Subscriber” queries executing locally instead of over the WAN.

==============================================

Running on Publisher

==============================================

2011-10-06 12:12:54.887 Downloading data changes to the Subscriber

...

2011-10-06 12:12:54.965 OLE DB Publisher 'MyPublisher': {call sys.sp_MSmakegeneration (?, NULL,NULL,NULL,100)}

 

2011-10-06 12:12:55.434 OLE DB Publisher 'MyPublisher': {call sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,1,'F4345E55-25DD-41EC-9122-99394D4E3EB7')}

2011-10-06 12:12:55.450 OLE DB Subscriber 'MySubscriber': {call sys.sp_MSgetmetadatabatch90new(?,?,?)}

2011-10-06 12:12:55.606 OLE DB Subscriber 'MySubscriber': save transaction mergecommitbatch

2011-10-06 12:12:55.684 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_ins_sp_672D49594EC640F8828F05E693C64766] (?,?,?,?,?,?,?,?,'',?,?,?,?,?,?,NULL,?,?,90)}

2011-10-06 12:12:55.793 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_upd_sp_672D49594EC640F8828F05E693C64766] (?,?,2,?,?,?,?, , , , ,?, , , , , , ,90)}

2011-10-06 12:12:55.950 OLE DB Subscriber 'MySubscriber': {call sys.sp_MSgetmetadatabatch90new(?,?,?)}

2011-10-06 12:12:56.106 OLE DB Subscriber 'MySubscriber': save transaction mergecommitbatch

2011-10-06 12:12:56.168 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_ins_sp_672D49594EC640F8828F05E693C64766] (?,?,?,?,?,?,?,?,'',?,?,?,?,?,?,NULL,?,?,90)}

2011-10-06 12:12:56.247 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_upd_sp_672D49594EC640F8828F05E693C64766] (?,?,2,?,?,?,?, , , , ,?, , , , , , ,90)}

2011-10-06 12:12:56.403 OLE DB Subscriber 'MySubscriber': {call sys.sp_MSgetmetadatabatch90new(?,?,?)}

2011-10-06 12:12:56.543 OLE DB Subscriber 'MySubscriber': save transaction mergecommitbatch

...

2011-10-06 12:13:41.792 Downloaded 100 change(s) in 'MyTable' (100 inserts): 100 total

...

2011-10-06 12:16:46.316 Downloaded 100 change(s) in 'MyTable' (100 inserts): 500 total

=============

Download 500 changes in  (12:12:54.887 - 12:16:46.316) 4 minutes

==============================================

Running on Subscriber

==============================================

2011-10-06 11:30:04.301 Downloading data changes to the Subscriber

2011-10-06 11:30:04.536 OLE DB Publisher 'MyPublisher': {call sys.sp_MSmakegeneration (?, NULL,NULL,NULL,100)}

...

2011-10-06 11:30:04.975 OLE DB Publisher 'MyPublisher': {call sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,1,'F4345E55-25DD-41EC-9122-99394D4E3EB7')}

2011-10-06 11:30:05.053 OLE DB Subscriber 'MySubscriber': {call sys.sp_MSgetmetadatabatch90new(?,?,?)}

2011-10-06 11:30:05.053 OLE DB Subscriber 'MySubscriber': save transaction mergecommitbatch

2011-10-06 11:30:05.053 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_ins_sp_672D49594EC640F8828F05E693C64766] (?,?,?,?,?,?,?,?,'',?,?,?,?,?,?,NULL,?,?,90)}

2011-10-06 11:30:05.053 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_upd_sp_672D49594EC640F8828F05E693C64766] (?,?,2,?,?,?,?, , , , ,?, , , , , , ,90)}

2011-10-06 11:30:05.069 OLE DB Subscriber 'MySubscriber': {call sys.sp_MSgetmetadatabatch90new(?,?,?)}

2011-10-06 11:30:05.069 OLE DB Subscriber 'MySubscriber': save transaction mergecommitbatch

2011-10-06 11:30:05.069 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_ins_sp_672D49594EC640F8828F05E693C64766] (?,?,?,?,?,?,?,?,'',?,?,?,?,?,?,NULL,?,?,90)}

2011-10-06 11:30:05.069 OLE DB Subscriber 'MySubscriber': {?=call [dbo].[MSmerge_upd_sp_672D49594EC640F8828F05E693C64766] (?,?,2,?,?,?,?, , , , ,?, , , , , , ,90)}

2011-10-06 11:30:05.069 OLE DB Subscriber 'MySubscriber': {call sys.sp_MSgetmetadatabatch90new(?,?,?)}

2011-10-06 11:30:05.069 OLE DB Subscriber 'MySubscriber': save transaction mergecommitbatch

...

2011-10-06 11:30:06.871 Downloaded 100 change(s) in 'MyTable' (100 inserts): 100 total

...

2011-10-06 11:30:14.831 Downloaded 100 change(s) in 'MyTable' (100 inserts): 500 total

=============

Download 500 changes in ( 11:30:04.301 -  11:30:14.831) 10 seconds

Because the Merge Agent is “talking” more to the Subscriber, we see significant performance improvement when “chatty” conversation is done locally on the Subscriber instead of having that conversation over a slow WAN.

 

Okay, Chris, so what’s the drawback about running Merge Agent on the Subscriber.  Turns out Merge Replication also optimizes traffic when moving large amount of data between the Subscriber to central Publisher (uploading).  This optimization occurs when you are running the Merge Agent on the Publisher.  Running Agent on the Subscriber and uploading large amount of data can’t take advantage of this optimization.

 

Where should the Merge Agent be executing?

 

The central theme in Merge Replication is to make quick connection to the source, then have “chatty” conversation on the Destination.  Good rule of thumb, If most of your data is download, run the Merge Agent on the Subscriber.  If most of your data is uploaded, run the Merge Agent on the Publisher.

 

Through testing you’ll be able to determine which solution best meets your needs.