Change The Isolation Level Of An Availability Group Database

I recently needed to change the Transaction Isolation Level of one of our databases in an Availability Group.  As it turns out, this is not as straight forward as you might think.  Normally all you would have to do is run an ALTER DATABASE statement, but having the database in an Availability Group adds another layer of complexity.

For starters, just trying to run the ALTER DATABASE statement against the database on your primary Availability Group server will result in the following error:

Msg 5070, Level 16, State 2
Database state cannot be changed while other users are using the database ‘TestDB’
Msg 5069, Level 16, State 1
ALTER DATABASE statement failed.

Ok, so users are connected to our database.  We just need to put it in single user mode to make our change, right?  WRONG!  Trying to run SET SINGLE_USER results in the following error:

Msg 1468, Level 16, State 1
The operation cannot be performed on database “TestDB” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1
ALTER DATABASE statement failed.

Well, isn’t that lovely.   Some operations are not allowed on a database that is participating in an availability group.  Great.

So, how do we change the Transaction Isolation Level?  After a little trial and error (and a lot of Googling), I was able to make the change using the following solution.

1. Drop the database from the Availability Group – Yep, that’s right, you have to completely remove the database from the Availability Group, which means that this database will not be available on your Primary or Secondary servers, and will be unavailable to your applications.  (So, you’ll have to schedule some down time to do this)

2. Put the database in Single User Mode – This isn’t always required, though it may be, depending on what Transaction Isolation Level you are wanting to set.  In may case, I was enabling the READ_COMMITTED_SNAPSHOT option, and in order to do that only the connection executing the ALTER DATABASE command is allowed in the database.  Plus, even though the database isn’t in the Availability group at the moment, users can still still connect directly to it on the server instead of accessing it via the Availability Group Listener.

3. Modify the Transaction Isolation Level – This is my code for enabling Read Committed Snapshot Isolation, be sure to change it to whatever Isolation Level you need.

4. Turn Multi User back on

5. Add the database back to the Availability Group

So, at this point, your database should be back in the Availability Group, and everything on your Primary server should look good.

But wait!  We’re not done!  None of the copies of our database on our Secondary servers are happy.  We need to let them know that our Primary is back online and that they can start synchronizing again.  This is actually very easy to do, simply run this ALTER statement on every one of your Secondaries that need to sync back up with the Primary.

1. Reconnect the Secondary database to the Availability Group so it starts syncing again

Once that is complete, you should be done!  It may seem like a lot of work at first, but in reality most of those statements should execute pretty quickly.

Now, there’s one thing I want to point out to save you some trouble.  Be sure to disable any transaction log backup jobs for this database while it is out of the Availability Group.  If you don’t, you may be doing some database restores.  Trust me, I know.  It happened to me.  A transaction log backup occurred on my database on my Secondary server while I had it out of the Availability Group.  This resulted in my Secondary refusing to sync back up with my Primary when I tried to add it back to the Availability Group, because the LSNs were not matching.  To fix this, I had to take a FULL backup of my Primary database, and a LOG backup, then restore them both over my Secondary database using the WITH NORECOVERY option.  This allowed the database to then sync with my Primary.

Once your Secondaries are re-synced, then you can start your transaction log backup job again, and you should be finished.