Merge Replication with limited or unstable network bandwidth

Question

Merge Replication with limited or unstable network bandwidth

Sunil Ferro 21

Hi,

I have setup SQL Server Merge Replication using 3 SQL Servers (One Publisher, two Subscribers). It is configured to run continuously. All servers have the SQL Server 2016 Standard Edition. For testing purposes, I have used a database with one table. Replication is working from Publisher -> Subscriber and Subscriber -> Publisher. The concern is regarding Subscribers (client's end) having limited/unstable/no network bandwidth at certain times. If data is entered in multiple tables at the Subscriber's end and they have unstable bandwidth then how will Replication manage this? Will it ever insert/update records partially? Is there something that I need to do to handle this? Any suggestions that can be provided will help.

Thank you.

Seeya Xi-MSFT 16,586 Reputation points

2022-04-20T05:45:46.813+00:00

Hi @Sunil Ferro ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

2 answers

Your answer

Seeya Xi-MSFT 16,586 Reputation points

2022-04-20T05:45:46.813+00:00

Hi @Sunil Ferro ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Answer 1

Hi @Sunil Ferro ,

Welcome to Microsoft Q&A!
Consider using the alternate snapshot folder and compressed snapshots on removable media for low-bandwidth networks.
Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and make it easier to transfer snapshot files on removable media.
Compressed snapshots can, in some cases, improve the performance of transferring snapshot files across the network. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files, and by the Distribution Agent or Merge Agent when applying the snapshot files. This may slow down snapshot generation and increase the time it takes to apply a snapshot in some cases. Additionally, compressed snapshots cannot be resumed if a network failure occurs; therefore they are not suitable for unreliable networks. Consider these tradeoffs carefully when using compressed snapshots across a network. For more information, see Modify snapshot options.

If you are using merge replication over an unreliable network, consider using logical records, which ensures related changes are processed as a unit. For more information, see Group Changes to Related Rows with Logical Records.

Here are the related document: Merge Replication
Enhance General Replication Performance

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Sunil Ferro 21 Reputation points

2022-04-20T13:42:39.66+00:00

@Seeya Xi-MSFT thank you for the response. I am new to Replication hence will need time to go through the information you provided. Will let you know if I have any questions.
Seeya Xi-MSFT 16,586 Reputation points

2022-04-22T05:41:30.313+00:00

Good day!

Answer 2

Tom Phillips 17,771

Replication is an "asynchronous" process. The commands are stored in the "distribution" database and then applied on a schedule to the subscribers.

It generally works fine in an environment where the connection is not stable. I would suggest you use a schedule, rather than "continuous" when you set it up. I would suggest 2 mins or 5 mins schedule. The advantage is you will get a log for every run. Otherwise, you never get the log until you stop the job. It will also restart if there is an error which causes it to stop.

In merge replication, there is a possibility of "conflicts". You need to understand conflict resolution. See: https://learn.microsoft.com/en-us/sql/relational-databases/replication/merge/advanced-merge-replication-conflict-resolving-in-logical-record?view=sql-server-ver15

Sunil Ferro 21 Reputation points

2022-04-28T13:08:29.167+00:00

@Tom Phillips Thank you for the suggestion. Will consider it.
Regarding using logical records as suggested by @Seeya Xi-MSFT , there is a Note on the site which says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature...".
Tom Phillips 17,771 Reputation points

2022-04-28T13:49:40.19+00:00
It depends on your application. I would not attempt to use the logical records.

Replication delivers "transactions". A transaction is either applied or not. There is no possibility for a part of the transaction to be applied.

However, this requires your application to use transactions when necessary to make sure the data is consistent.

If you were to do:
INSERT INTO Orders ....
INSERT INTO OrderDetails ....

That is 2 implicit transactions and it is possible the insert into Orders could happen, and the the connection lost. Which results in no OrderDetails, until the next connection . If Orders is updated, and the connection lost, the Orders update will be rolled back, and attempted to be reapplied the next successful connection.

If your application uses this instead:

BEGIN TRAN INSERT INTO Orders .... INSERT INTO OrderDetails .... COMMIT

That is 1 transaction, and it is either complete or not complete. There is not a possibility for Orders to be updated without OrderDetails.

Share via

Merge Replication with limited or unstable network bandwidth

2 answers

Your answer