Skip to main content

Posts

Showing posts from August, 2014

How To Change a Database Setting For A Database In A Availability Group

I was in the middle of a deployment and found that the database in the AG had been configured with service_broker enabled.  This setting shouldn't' have been enabled for the database and needed to be disabled. As i found, the setting can't be disabled for a database in a AG group. Following steps were followed to disable the service_broker setting for database.   1.) Removed the database from the AG group        USE [MASTER]; ALTER AVAILABILITY GROUP [ ] REMOVE DATABASE ;       FYI. By this time the rest of the replica's will be out of sync  2.) Change the service broker setting for the database  3.) Add the database to the AG group on the primary server  USE [MASTER]; ALTER AVAILABILITY GROUP [ ] ADD DATABASE ;  4.) Logged on to the secondary database, restored the transaction log from primary with Norecovery 5.) Add the secondary database to the AG group  USE [MASTER];  ALTER DATABASE SET HADR AVAILABILITY GROUP = [ ]; step