Forum Discussion
Unable to enable RCSI
We created our Azure SQL database few years ago and at that time RCSI was not a default setting. I am trying to enable RCSI now but unsuccessful.
Database details:
- We have Azure SQL database (single database) in East US.
- A read replica (Geo-replication) was created against it.
Steps identified from documentation to enable RCSI:
- ALTER DATABASE [DatabaseName] REMOVE SECONDARY ON SERVER [SecondaryServerName];
- ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
- ALTER DATABASE [DatabaseName] SET READ_COMMITTED_SNAPSHOT ON;
- ALTER DATABASE [DatabaseName] SET MULTI_USER;
- ALTER DATABASE [DatabaseName] ADD SECONDARY ON SERVER [SecondaryServerName];
Second script to set single user returns below error. I am unable to proceed after this. Any help to resolve the problem and enable RCSI is appreciated.
Msg 1468, Level 16, State 1, Line 1
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.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Yes, the replication was removed and reflected in the portal.
Instead of trying all the above steps, I just executed below one statement and RCSI is successfully enabled in primary database and successfully synced with secondary database as well.
ALTER DATABASE [DatabaseName] SET READ_COMMITTED_SNAPSHOT on WITH ROLLBACK IMMEDIATE
2 Replies
Yes, the replication was removed and reflected in the portal.
Instead of trying all the above steps, I just executed below one statement and RCSI is successfully enabled in primary database and successfully synced with secondary database as well.
ALTER DATABASE [DatabaseName] SET READ_COMMITTED_SNAPSHOT on WITH ROLLBACK IMMEDIATE
If you execute the REMOVE SECONDARY command, does the Geo-Replication settings in the Azure portal reflect that the replication link has been successfully terminated for the primary database?
It seems that when attempting to switch the primary database to SINGLE_USER mode, the service still perceives the replica as active, causing the operation to fail due to this constraint.
