AWS RDS Sql Server READ_COMMITTED_SNAPSHOT

All we need is an easy explanation of the problem, so here it is.

We’re currently running Multi-AZ SQL SERVER (Mirroring) and would like to turn READ_COMMITTED_SNAPSHOT on. It doesn’t look like this option is available in the parameter group or rdsadmin stored procedured, and executing ALTER DATABASE [database1] SET READ_COMMITTED_SNAPSHOTthrows an error that it is in a mirroring relationship. Any recommendations on how to accomplish this? We’ve tried restoring the database to test turning the partner since we’re not sure how RDS responds, but all the restores are coming online as Always-On instead of mirroring so we haven’t been able to test.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Here is a procedure from Microsoft on how to achieve what you are asking.
Your issue is that the only session that can exist in the database when changing it to RCSI is the Alter DATABASE statement. However, the mirroring session exists, so there 2 sessions connected to the database. You only want a single session to exist.

You need to break mirroring , make the change to RCSI and then re-enable mirroring. Don’t take my word for it. Review the Microsoft link below

MS tech net article

Method 2

This worked fine in AWS mirroring implementation. I didn’t have to turn the partner back on, as the RDS service automatically adds the partner back within about 30 seconds.

— stop mirroring

alter database {dbname} set partner off
GO

— restrict users

ALTER DATABASE {dbname} SET RESTRICTED_USER WITH ROLLBACK AFTER 30 SECONDS
GO

— Enable RCSI

ALTER DATABASE {dbname} SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
GO

— Allow connections to be established once again

ALTER DATABASE {dbname} SET MULTI_USER
GO

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply