Replication on Always On

Question

I have two SQL Servers 2014 Enterprise Edition (SERVER A, and SERVER B) designed for Always On Availability Group. There are 5 databases in the AG.

I have been told that we need to replicate one of the databases (Database A) from the AOAG. I set up a transactional replication for database A, pointing to listeners which shouldn't cause any issue if a failover happened. Tested, worked just fine.

I'm thinking to create two distribution databases, each on a different server. The reason is, I'm trying to be redundant. If Distribution Server A is gone for any reason, I should be able to replicate database A from Distribution Server B. Is it possible, any alternative to this?

Here is what my design is for Replication on AOAG:

• Distribution Server A (contains Distribution database) - SQL Server 2014
• Two subscribers - SV1 and SV2 - SQL Server 2008R2 Standard
• Publishers (SERVER A and B - from AOAG) - SQL Server 2014

What I'm trying to do: Add one more distribution server (B).

• Distribution Server A (contains Distribution database)
• Distribution Server B (contains Distribution database1) - SQL Server 2014
• Two subscribers - SV1 and sV2 - SQL Server 2008R2 Standard
• Publishers (SERVER A and B - from AOAG)

What I see as a challenge:

Can't configure remote Distribution server for possible publishers. Help/ideas are welcome!

Answer 1

You can't have two Distribution databases. Put the distribution database in a Failover Cluster Instance that way it is highly available.

Maybe one day MS will allow the distribution database to be in an AG. I hear you can have an FCI on a cluster that also has AG (having both on the same cluster but completely distinct instances - so two standalone instances with an AG and one FCI), though I've never done it, and probably wouldn't recommend it.

Clustering is how you make the distribution database highly available. So if High Availability is important for your organization and you need replication, then you need to learn Failover Clustering and setup an FCI.