Friday, May 22, 2015

[Replication] changing the Polling Interval (pollinginterval) for a Merge agent via a profile

Issue: complaints that merge replication is too slow - processes are hitting Server B and don't see changes made by Server A in a timely manner.

So: Merge Replication doesn't work like transactional.  It saves the changes, then every 60 seconds (by default) it "Polls" for changes and applies. However, from the surface it doesn't look like that, because the agent runs continuously, and you only occasionally see that it's waiting...

SELECT * FROM distribution.dbo.MSmerge_history

So, when I first went looking at how long the process took, I saw "uploading" and "uploaded", figured that was a good metric to use, but it took a LONG time.  Then I queried the table again. 

Wait, what? 

Well, it appears that when it spins up again, it removes the old record, but (at least on  2008 R2 SP3) the new record retains the same [time].  So my latency calculation couldn't use it.

However, back to the original problem: changing the polling interval.

Tried modifying the job, as per several posts, by adding "-PollingInterval 30" to the end of the job. Nope!
(though this MAY be because I'm using SSMS 2014 on a SSMS 2008R2 box - unsure)

So how else do you change it?  By modifying the replication agent profile.

Bring up Replication Monitor.
Find the relevant publisher
Right-click and choose "Agent Profiles..."
Select the relevant "type".  I did "Merge Agents".

Now you'll see something like this:

Next step, add a new profile.  Click "New" and choose the default so that you keep all the other values the same.  You could change the defaults, but this way it's more obvious something has been done.  On the minus side, you'd have to change new agents to use it. 

Put in a useful name, and change the value of the PollingInterval. I've changed it to 30.

Finally, tell it to apply to the existing (so be careful if you have more than one)

Next step would be figuring out how to modify one agent but not another - a lesson I'll probably have to do another day. 

And if you want to change the default agent (again, so it's obvious that it's not the standard values) for any new agents, click the new Profile as "Default for New".

Hope this helps.

No comments: