How to Enable RCSI for a Database with Database Mirroring

  • Article

 

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:

ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON

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:

The operation cannot be performed on database "<db_name>" because it is involved in a database mirroring session.

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:

· Break the DBM session: (ALTER DATABASE <db_name> SET PARTNER OFF).

· Ensure no other connection to the database.

o It is not necessary, but it may be helpful to put the database in the single user mode (ALTER DATABASE <db_name> SET SINGLE USER WITH ROLLBACK IMMEDIATE).

·  On the principal, execute the command to enable RCSI (ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON). You need to execute this command only on the principal, not on the mirror (in fact, you can’t execute this on the mirror, because the database is in the restoring state).

· Re-establish the DBM session: (ALTER DATABASE <db_name> SET PARTNER = ‘…’).

o No need to re-seed the mirror for establishing database mirroring, if no backup was taken during these 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.