Vijay
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Because after failover Publisher sits on secondary and distribution DB sits on Primary?
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.
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.
Vijay, please don't abandon your questions.
Do you still need help with this question?
Do you still need help with this question?
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.
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.
@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.
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.
ASKER
Thank you Mate.
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.