3

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 ?

  • What happens if you just do a SHUTDOWN via TSQL for the node? – LowlyDBA Oct 25 '17 at 20:10
  • When you say replica - do you mean database replica or server replica? – Sean Gallardy Oct 25 '17 at 20:10
  • @LowlyDBA quote from the suspend link " Existing connections on the readable secondary remain usable. New connections to the suspended database on the readable secondary are not allowed until data movement is resumed." – Kelvin Wayne Oct 25 '17 at 20:14
  • @Sean database replica – Kelvin Wayne Oct 25 '17 at 20:15
  • 1
    If you make the secondary non-readable before a failover, that should do it. – Sean Gallardy Oct 25 '17 at 20:23
1

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.

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.