Just wondering if SQL Server use different technology for Transactional Replication and Alwayson High Availability or is it same behind the scene. Basically I need to know if SQL Server use same or different technology(protocol/process) for Replication and Synchronizing the secondary replicas in High Availability in SQL Server 2016. Thanks in advance.
5
-
It's completely different. Mirroring is more similar to AGs than replication is.– Tara KizerCommented Jan 17, 2018 at 17:09
-
@TaraKizer, thanks for quick reply, I was actually looking what protocol SQL uses for data synchronization in high availability please?– SajidCommented Jan 17, 2018 at 17:13
-
see this - blogs.msdn.microsoft.com/psssql/2013/04/22/…– Kin ShahCommented Jan 17, 2018 at 17:15
-
@kin, have read but no mention of what protocol used for data synchronization?– SajidCommented Jan 17, 2018 at 17:23
-
Not sure who flagged this as a shopping list question but it would be good if you explained why? I see no reason to close this for that reason.– Mr.BrownstoneCommented Jan 17, 2018 at 18:26
Add a comment
|
2 Answers
Transactional Replication uses the TDS protocol and applies changes using SQL. The connections are established from the Distributor for Push Subscriptions, and from the Subscriber for Pull Subscriptions.
AlwaysOn Availability groups use TCP/IP connection between the replicas using the Database Mirroring Endpoint. The connections are established from the instances hosting the Secondary replicas to the instance hosting the Primary replica. But as the Primary replica can move, every server hosting a replica needs to be able to connect to every other one.
answered Jan 17, 2018 at 17:39
David Browne - MicrosoftDavid Browne - Microsoft
47.6k3 gold badges51 silver badges101 bronze badges
AlwaysOn/Mirroring reads the log on the master server and transfers all the commands to the DR servers where it essentially restores the t-log from a backup.
Replication reads the log and translates the transactions and commands into individual commands to insert/updated/delete one row at a time on the subscribers.
The former is a DR/load balancing tech. The latter for load balancing to spread read only queries away from the master servers.
