Author: Sanjay Mishra
Reviewers: Prem Mehra, Alexei Khalyako, Kun Cheng, Mike Ruthruff, Thomas Kejser, Sunil Agarwal
To enable read committed snapshot isolation (RCSI) on a database, one needs to execute the following command:
If you execute the above command on a database that is acting as database mirroring (DBM) principal, you will get an error like the following:
No surprise here. This is as expected. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. The database mirroring session is a connection in the database, and therefore, one can’t execute the command to enable RCSI on a database involved in a DBM session.
So, how to achieve this? Follow these simple steps:
Note that after executing the above steps, if you query the is_read_committed_snapshot_on column of the sys.databases catalog view on the principal server, you will see the value 1 (which means “ON”). However, if you query the same catalog view on the mirror server, you will see the value 0. This is not a concern. This value in the catalog view of the mirror will be reflected appropriately after the first DBM failover.