Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DavidD
Yak Posting Veteran
73 Posts |
 Posted - 2008-11-27 : 23:24:02
|
| Dear all, We are currently looking at the feasibility of replacing our inhouse replication model with SQL Server 2005 merge replication using web synchronization. We have set up a test environment using aroung 40 subscriptions, and at this stage are testing on the LAN (ultimately the subscribers will be on laptops and synchronize via 512kbps wireless cards). Anyway we are getting wildly fluctuating synchronization times for a couple of megs of data (from 3 minutes to an hour). I have run a SQL trace and the main procs being called aresys.sp_Msenumcolumnssys.MSinsertgenhistorysys.MsupdategenhistoryThese procs seem to be getting excecuted over and over again (especially the first one), and taking ages with very little transferring of data. I assume they are some internal metadata checks that SQL undertakes, but why so long and what can I do to speed this up? If anyone knows what SQL Server is up to and why these procs keep getting executed it would be very helpful.Thanks in advanceDavid |
|
|
DavidD
Yak Posting Veteran
73 Posts |
Posted - 2009-05-08 : 02:08:30
|
| I am replying to my topic to help any poor bugger following in my footsteps. We got Micrososft involved and the problem was that SQL Server Replication defaults the merge agent profile to the 'slow link merge agent profile' when you use web synchronization. This means that SQL Server only sends 1 generation per batch - the logic for this being that with a slow and unreliable connection there is less rollback required. THe downside is that it is incerdibly slow, especially when you have multiple subscribers and build up your generation counts. The solution is to go into replication monitor, right-click on the subscription and select 'Agent profile'. Untick the slow link agent profile and either select the default or create your own specifying a much higher number of generations per batch (typically 500-2000). |
 |
|
|
|
|
|
inconsistent replication speeds