We have clients requested us to configure Always on with Replication. Microsoft does not support it. "The distributor should not be a host for any of the current (or intended) replicas of the availability group that the publishing database is (or will become) a member of. " We do not want distributor to be on the same server as Publisher.
Has anyone faced this change? Do you know the limitation for not having distributor as replica of the availability group. What other options do you recommend to have high availability for distributor in transactional replication in Always on Configuration.
Thanks
How to make distributor highly available in Always On Configuration
availability-groupshigh-availabilitytransactional-replication
Related Solutions
The statement you are reading in the Microsoft article says that you should not (and cannot) put a Distribution database inside an Availability Group.
Why?
Because the distribution database is a System database and Availability Groups will only fail over User databases. Therefore you will require another (4th) server in your situation to act as the (remote/scaled out) Distributor.
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
- Transactional Replication with Always On via Sql Server 2014
- SQL Server – Subscriber Availability if Publisher or Distributor is Down
- SQL Server Basic Availability Groups – Use for Publisher, Distributor, and Subscriber Databases
- SQL Server – Set Up Transactional Replication to Always On Availability Group
- Sql-server – Set up new publication on SQL Server 2014 database which is part of an availability group
- Sql-server – Replication with Always On availability groups
Best Answer
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.