T-ransactional Replication With AlwaysOn SQL Server 2016

How to configure AlwayOn in SQL Server 2016 which already contains T-ransactional Replication.
As we know that Distribution Db is not failover (we can't add to AG).
Current Environment:
SQL Server 2016 With T-ransactional Replication
      -->Server1 - Publisher/Distributor -> this will become Primary Replica
      -->Server2 -Subscriber.                   --> This will become Secondary Replica

My understanding is , taking another standalone server(Like Server3- which is not part of AG) then break replication and reconfiugre Server1 as Publisher, Server2 as Subscriber and Server3 as Distributor

Please correct me if i am wrong?
VijayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
Hi,
You definitely need a dedicated Distributor and you have to configure PULL replication over AlwaysOn.
You have to put LogReader Agent on Distributor to connect to Listener of Publisher instead of connecting directly to the database.
So if AG fails over the LogReader can reconnect and continue publishing.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
System databases can't be part of an AG.
You also don't need a 3rd server. Distribution database can stay in the same instance as the Publisher. It just don't be replicated into the Secondary Replica.
VijayAuthor Commented:
Then if i failover primary server (which contians publisher and Distributon database) how to works.
Because after failover Publisher sits on secondary and distribution DB sits on Primary?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Secondary Replica would need to be ready to be the Publisher.
WIth  AG you'll need to prepared all participating nodes to be ready to work as a Primary whenever it needs.
Did you read this MSDN article? It shows what you need to do to have Replication working with an AG solution.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Vijay, please don't abandon your questions.
Do you still need help with this question?
Máté FarkasDatabase Developer and AdministratorCommented:
Vitor, that article writes "The distribution database cannot be placed in an availability group. " This means that you cannot trust in Primary or Secondary servers to be a distributor. So again, you definitely need a 3rd server to configure for distribution.
So Vijay, you are right. You cannot place distribution database on Primary or Secondary server but as Vitor Montalvão mentioned you need to prepare Secondary server for publishing.
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Máté, what that means is that distributor database can't be part of an AG but neither any other system databases can't.
Databases can be stored in a SQL Server instance without being part of an AG. There's no obligation that all databases from a SQL Server instance be part of an AG if there's one configured.
VijayAuthor Commented:
Thank you Mate.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.