AlwaysOn High Availability Take database offline from primary

availability-groupshigh-availabilityreplication

I have below scenario where i need your help:

I have configured the AlwaysOn High Availability on my server. I have two replicas. Now I have requirement to make one database offline from the primary replica and make it online after some time.

So what happens in this scenario in background?

After making the database from primary replica online, the database from the secondary replica goes into restoring state. And we need to always manually delete the database from secondary server and remove it from AG group and add it again to both in sync again.

Is there any solution for this? If not are there any steps to do the above task from code?

Best Answer

Now I have requirement to make one database offline from the primary replica and make it online after some time.

This is not possible since database is added to AOAG. You cannot take the db offline unless you remove it from AG.

What you can do is ...

  • Disable the login on primary.
  • Enable read_only secondary.
  • Have your application configure to use read intent and read from secondary
  • After your testing is done, enable the login on primary.