1

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!

4

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.

  • @Tara..can you give me suggestion about how to configure transnational replication in different location without VPN.. my subscriber is also part of Alwayon availability group in sql server 2014. – Gulrez Khan Aug 8 '17 at 9:30

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.