AWS RDS Sql Server READ_COMMITTED_SNAPSHOT



  • 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.



  • 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

    https://techcommunity.microsoft.com/t5/datacat/how-to-enable-rcsi-for-a-database-with-database-mirroring/ba-p/305043




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2