Can you omit a database from one replica of an Availability Group

Question

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

Answer 1

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.