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.
SQL Server – Synchronizing Replication vs AlwaysOn High Availability
high-availabilitysql-server-2016transactional-replication
Related Solutions
Put the distributor in a virtual machine, and use the hypervisor's high availability tools. This lets you survive host hardware failure. OS problems will still cause issues, though, so for an additional level of protection, you can run clusters inside virtualization.
Distribution database should not reside on the servers that are part of AlwaysON availability group that the publishing database is (or will become) a member of.
Replication configuration is coupled to the SQL Server instance where the Distributor is configured; therefore the distribution database cannot be mirrored or replicated.
If you want to provide HA for distribution database, then you have to go for SQL Server Failover cluster. Thats the only option.
Your scenario is as below :
So if you loose server C, the only option to get distribution running on server D is to do a RESTORE.. with KEEP_REPLICATION from a good backup. You can use this script to restore your distribution database (with some changes as per your environment). I would go for a clean install of replication !
Make sure you script out your replication topology whenever your do any changes. You should have handy scripts of both drop and create, so in a disaster situation, you have scripts that will help easily create replication.
Also, since you are using always-ON with T-Rep, I would suggest you to enable TF 1448.
Trace flag 1448 enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled,, the log reader always waits for the synchronous secondary replicas. The log reader will not go beyond the min ack of the synchronous secondary replicas. This trace flag applies to the instance of SQL Server, not just to an availability group, an availability database, or a log reader instance. This trace flag takes effect immediately without a restart. It can be activated ahead of time or when an asynchronous secondary replica fails.
Reference : Configure Replication for AlwaysOn Availability Groups
Related Question
- SQL Server – Understanding High Availability and Always On
- Transactional Replication with Always On via Sql Server 2014
- Sql-server – Replication Subscribers and AlwaysOn Availability Groups (SQL Server)
- Sql-server – Number of data files in tempdb in Availability Group
- AlwaysOn High Availability Take database offline from primary
- Sql-server – Enable Replication/CDC from High Availability Group
- Sql-server – High Availability with Transactional Replication
- Sql-server – How to implement High Availability for all nodes in a sharded database
Best Answer
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.