AWS RDS Sql Server READ_COMMITTED_SNAPSHOT

Question

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.

Answer 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

• SQLpro: Why is this by design ? Because all transactions (reading or writing) that are actually working, must be stopped to read/write into snapshots...
• Ben Thul: @SQLpro - is that a rhetorical question that you asked and answered? Or a real question to which you're speculating? If the latter, that is (more or less) why.

Answer 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