2

Newbie question. Using SQL 2012. I have AlwaysOn setup for 3 servers. Server A is my primary, my replicas are Servers B and C. I have 5 DBs on Server A, all of which are replicated to both replicas. I want to pull just DB1 off of Server C and leave it replicated between Server A and Server B. Doable? If I pull DB1 off of my primary and then try to add it back in, just replicating to Server B, it is grayed out. The only way to add DB1 back into the mix is by replicating to both of my replicas B and C...not what I want to do.

1

I want to pull just DB1 off of Server C and leave it replicated between Server A and Server B. Doable?

Yes this is doable. For your scenario if you want to remove the database from availability replica you must do that from secondary replica. So you have to login into secondary replica from which you need to remove database. Expand availability database, right click on it and select remove secondary database. You can choose multiple databases to remove at one go. More information in BOL document

If you want to do using TSQL which I prefer just login into secondary replica from which you want to remove the database and run

ALTER DATABASE db_name SET HADR OFF;  
GO  

When a secondary database is removed, it is no longer joined to the availability group and all information about the removed secondary database is discarded by the availability group. The removed secondary database is placed in the RESTORING state.

If I pull DB1 off of my primary and then try to add it back in, just replicating to Server B, it is grayed out.

If you remove from primary replica means you are completely removing it from AOAG. You have to remove it from secondary replica as sated above.

| improve this answer | |
  • The only caveat is that the AG will show as unhealthy and the database replica for the DS1 database will show unhealthy. – Sean Gallardy - Retired User Sep 6 '16 at 14:58
  • That answers my question. Thanks for the help. – Gdog Sep 6 '16 at 17:30

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.