Sql-server – Take one replica offline from an availability group

availability-groupssql serversql-server-2016

I have 2 replicas running SQL Server 2016 Enterprise Edition in an AlwaysOn Availability Group.

One replica is primary and other replica is secondary and readable.

I have a business requirement that should allow one database replica to be taken offline and back online after a period of a few minutes by a custom developed APM monitor.

The database replica to be taken offline can be primary or secondary.

The database replica has to be take offline and online via transact SQL

When the database replica is offline it should not be possible for it to execute requests. All existing connections must be moved to the other database replica or fail, so that the client can reconnect using the IP listener to the other replica.

I can do a suspend or a planned manual failover but neither seems to support what I need.

The suspend & manual failover only works on secondary database replicas.

The suspend allows existing connections to execute requests.

The manual failover just switches the primary to secondary role and vice versa.

How do I do this ?

Best Answer

The database replica to be taken offline can be primary or secondary.

The database taken offline should always be the secondary.

The database replica has to be take offline and online via transact SQL

That's possible.

When the database replica is offline it should not be possible for it to execute requests. All existing connections must be moved to the other database replica or fail, so that the client can reconnect using the IP listener to the other replica.

Basically here's how it would go:

  1. If the database replica that needs to be taken offline is a primary, check for the current secondary to be synchronized.
  2. Set the database to be "taken down" as a non-readable secondary.
  3. If the database replica was a primary, failover.
  4. Do any other items you'd like here, such as removing it from the AG, etc.
Related Question