1

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.

  • It's completely different. Mirroring is more similar to AGs than replication is. – Tara Kizer Jan 17 '18 at 17:09
  • @TaraKizer, thanks for quick reply, I was actually looking what protocol SQL uses for data synchronization in high availability please? – Sajid Jan 17 '18 at 17:13
  • see this - blogs.msdn.microsoft.com/psssql/2013/04/22/… – Kin Shah Jan 17 '18 at 17:15
  • @kin, have read but no mention of what protocol used for data synchronization? – Sajid Jan 17 '18 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.Brownstone Jan 17 '18 at 18:26
3

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.