How do I deploy Synchronous transactional replication? Does it require special hardware? Are there internet bandwidth need specifications for it.

Question

Please guide as to how to deploy Synchronous transactional replication fresh. Is there any setting in SQL Server that can convert current asynchronous transaction replication to synchronous.

Also on infrastructure part; does it needs any special hardware or very high speed bandwidth; our per day load at each location is say approximately 350 entries per location per day.

Answer 1

Transactional replication is always asynchronous. Keep in mind that this is a quite an old feature and part of the design goal was to cover the situations where you have databases around the world, and it was not possible to have people in London to access a database in New York, because of limited network bandwidth. And even if times have changed, this is still one of the benefits of transactional replication, because it permits you to replicate data to another database with minimal impact on the source database.

If you want data to be replicated synchronous, you will need to look for a different technology. You can do this with an availability group. But an availability group sets some limits. You will need a cluster. Well, there are clusterless AGs as well, but they only support asynchronous replicas.

The reason you may implement synchronous replicas is that you want minimise downtime if a node fails, so that you get an automatic failover. But it also comes with a price: you need to wait for the log to be hardened on the replica. Normally, in an AG you have the nodes in the same data centre. You can have them on other parts of town to protect yourself against disasters like the data centre burning down. But you cannot have them cross-continent, but the maximum distance is usually give at 70-100 km. The limit is set by the maximum network speed: the speed of light.

Answer 2

Hi @DM ,

SQL Server supports Transactional, Snapshot and Merge replication. They are inherently asynchronous.
Please see this document about Enhance General Replication Performance: https://learn.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-general-replication-performance?view=sql-server-ver15#server-and-network
Moreover, you can read this about Always On Availability Groups: https://www.sqlshack.com/data-synchronization-in-sql-server-always-on-availability-groups/
Also, read this bog: https://www.brentozar.com/archive/2015/02/alwayson-availability-groups-faq/

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.