0

Question:

Is it possible to add a database to specific replicas of an availability group and not add it to another replica?

Use Case:

A production database is required for operations but does not need to be included in reporting. Add the database to the synchronous replicas of the availability group that are used for high availability, but do not add it to the asynchrounous replica used for reporting.

AG Setup:

Server Mode Failover Readable Purpose
Prod01 Synchronous Automatic Yes High Availability
Prod02 Synchronous Automatic No High Availability
Reporting01 Asynchronous Manual Yes Reporting

My Testing:

I was able to add it to Prod01 and Prod02, but the AG Dashboard throws a warning

In this availability group, at least one secondary replica has a NOT SYNCHRONIZING synchronization state and is not receiving data from the primary replica.

I would assume that automatic failover would occur. I could deal with the warning, but there is probably a better way of doing this.

Environment:

All servers are MS SQL Server 2016 sp2 cu17

2
  • Will the log file just continue to grow if it's not synchronizing on the asynchronous replica?
    – Casey
    Aug 5 '21 at 0:07
  • 5
    You should create a separate AG for this database if it should have different replication Aug 5 '21 at 11:35
4

Is it possible to add a database to specific replicas of an availability group and not add it to another replica?

Directly answering the question, yes it's possible. Giving more insight past the initial true/false answer is you can do it but it's a bad idea. Other than a host of issues that would likely ensue, if the database doesn't need to be in the AG on that replica, then I would suggest multiple AGs where the databases that need to be there are, and the ones that aren't are in a different AG. This can cause other issues but we can't make the business decision on which of those items is more important.

I would assume that automatic failover would occur.

You would assume incorrectly.

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.