Merge replication using Web Sync

Question

Merge replication using Web Sync

David Kilmartin

SSC-Addicted

Points: 424
More actions
September 15, 2017 at 10:30 am

#339231

I have setup Web sync for merge replication, which was working fine for a while.
However I now have an issue with replication where by it fails after downloading 3100 Chunks.
I cannot do the following , as my server is sql2016
The limit can be raised by setting the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Replication
WebSyncMaxXmlSize DWORD 2000000
I have reduced the Upload & Download batch size to 10 in the Merge profile - but made no difference.
Any suggestions, as to what to try next ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

After you made the registry change, did you restart your SQL Server service? It seems likely to be necessary in order for SQL Server to recognize that the value has changed.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

David Kilmartin SSC-Addicted Points: 424 More actions · Answer 2

Hi Steve

Thanks for the reply.

I created the Registery Key as per picture (Is this correct), I then restarted the Server and tried to replicate.

It failed again at 3100 Chunks. I also dropped the batch size to 5.

I dont think there is a huge amount of data that needs to be replicated -maybe 10,000 records.

The MSDN refers to LARGE Volumes of data - but what is Large eg is 10,000 records large or 100,000 or 1,000,000 ?

Is there any way to check what is in a 'Chunk' as it might give a clue.

Is there anything else I could try ?

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Not sure if you've read everything found here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4945b200-de7d-4c73-8da4-15ea524f05cd/web-sync-error?forum=sqlreplication

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 4

And where you originally posted this problem, did you try ALL that Lin suggested?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

David Kilmartin SSC-Addicted Points: 424 More actions · Answer 5

Thanks actually saw that already..
As far as I can see “Precompute partitions” only works if you use Filtering.....

and

you reinitialize try to use the exchangetype of uploadonly (ExchangeType =1).

You cannot use Uploadonly for Web Sync
🙁

sgmunson SSC Guru Points: 110640 More actions · Answer 6

David Kilmartin - Monday, September 18, 2017 7:14 AM
Thanks actually saw that already..
As far as I can see â€œPrecompute partitionsâ€ only works if you use Filtering.....
and
you reinitialize try to use the exchangetype of uploadonly (ExchangeType =1).
You cannot use Uploadonly for Web Sync
🙁

I was referring to the following part:

We recommend that you start with a value of 10, and then tune based on application needs and performance. Typically, these parameters are specified in an agent profile. For more information about profiles, see Replication Agent Profiles.' For detailed information about this issue, please refer to Replication: Unable to Synchronize subscribers via Web Synchronization.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

David Kilmartin SSC-Addicted Points: 424 More actions · Answer 7

I have followed this - and have now reduced the batch size right down to 1 - and it made no difference.

is there anywhere I can check to ensure that the correct profile is actually been used instead of the default settings ?

Sue_H SSC Guru Points: 90860 More actions · Answer 8

David Kilmartin - Monday, September 18, 2017 7:54 AM
I have followed this - and have now reduced the batch size right down to 1 - and it made no difference.
is there anywhere I can check to ensure that the correct profile is actually been used instead of the default settings ?

The merge agents being used for publications are defined in the table MSmerge_agents in the distribution database. It identifies which agent profile is being used.
The agents profiles are defined in the MSagent_profiles in msdb.

Sue

David Kilmartin SSC-Addicted Points: 424 More actions · Answer 9

thanks Sue

this has confirmed that my amended Profile is actually been sued and made no difference.

still hits error at 3100 Chunks.

Is is possible to reduce the number of 'Chunks' in any way - as I can see teh process eating into memory as each chunk is downloaded.

I have just migrated this Database from a standard Merge replication to a Web Sync environment.

Sue_H SSC Guru Points: 90860 More actions · Answer 10

David Kilmartin - Monday, September 18, 2017 8:41 AM
thanks Sue
this has confirmed that my amended Profile is actually been sued and made no difference.
still hits error at 3100 Chunks.
Is is possible to reduce the number of 'Chunks' in any way - as I can see teh process eating into memory as each chunk is downloaded.
I have just migrated this Database from a standard Merge replication to a Web Sync environment.

Maybe look at generation leveling. This blog discusses this:
Reducing Impact of Large Updates on Merge Replication

And this is related - worth reading to understanding the process:
ReplTip â€“ Troubleshooting sp_msmakegeneration

Sue