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.
Add a comment
|
2 Answers
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
-
Why is this by design ? Because all transactions (reading or writing) that are actually working, must be stopped to read/write into snapshots...– SQLproCommented Jun 2, 2022 at 9:13
-
@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.– Ben ThulCommented Jun 2, 2022 at 14:51
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