Enabling READ_COMMITTED_SNAPSHOT on MSSQL RDS instance in Multi-AZ AlwaysOn deployment

Question

I want to change my databases to use RCSI mode in order to limit my read locks.

The method usually entail setting the database to "single user" mode, setting the mode, then returning to "multi users":

```
ALTER DATABASE DatabaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName
SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE DatabaseName
SET MULTI_USER;
```

This doesn't work because of the AlwaysOn availability group:

`The operation cannot be performed on database "DatabaseName" 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.`

So my question is, should I just disable the Multi-AZ mode in the RDS options, then transform my database this way, and re-enable it afterward? Is there any service downtime expected setting Multi-AZ off/on (other than when I will use the single_user mode)?

Dunge · Answer

After testing I see it is possible to activate RCSI without switching to single user mode if the database activity is not too heavy by setting the rollback immediate on the operation.

```
ALTER DATABASE DatabaseName
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
```

This does not require to disable the Multi-AZ AlwaysOn mode.

re:Post Agent · Answer

You should not disable Multi-AZ on your RDS SQL Server instance to enable Read-Committed Snapshot Isolation (RCSI). Disabling Multi-AZ would cause significant disruption as it would force RDS to completely rebuild the mirror when you re-enable it, resulting in degraded availability for approximately 30-90 minutes.

Instead, you can enable RCSI without setting the database to SINGLE_USER mode by using the following command for each database:

```
USE master
ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
GO
```

This approach is recommended for EnterpriseOne databases to prevent locking scenarios that affect system performance. Enabling RCSI this way doesn't require putting the database in single-user mode and is compatible with Multi-AZ deployments.

It's important to note that for Multi-AZ deployments, all databases on the primary host are maintained as one atomic unit, and if one database fails over, all databases fail over together to the standby host. Therefore, you should be consistent in your RCSI settings across databases.

After enabling RCSI, you may need to adjust your application configuration to disable SQL query timeout and disable retries with *NOLOCK, as these settings are typically required when RCSI isn't enabled.  
**Sources**  
[Configure RCSI - AWS Prescriptive Guidance](https://docs.aws.amazon.com/prescriptive-guidance/latest/sql-server-optimization-for-enterpriseone/rcsi.html)  
[Determining a recovery model for your Amazon RDS for SQL Server database - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.DatabaseRecovery.html)  
[Microsoft SQL Server Multi-AZ deployment limitations, notes, and recommendations - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerMultiAZ.Recommendations.html)

Enabling READ_COMMITTED_SNAPSHOT on MSSQL RDS instance in Multi-AZ AlwaysOn deployment

Add your answer

Relevant content