When you use Readable Secondary Replicas in an SQL Server Availability Group, SQL Server will also use internally Read Committed Snapshot Isolation. When fail-over occurs to a readable secondary replica does the secondary database isolation mode change back to READ COMMITTED as the primary or does it stay Read Committed Snapshot Isolation internally? Finally, if the database isolation mode stayed the same; would it be fair to say if a database supports an Always-On Readable Secondary Replica, then it also has to support RCSI as well?
2
-
This one is pretty easy to test by yourself. Configure a database in an AG without RCSI and fail it over. Then look at the setting on the primary DB (or try it by running an open transaction that would lock a row and try to read it from another session)– Dominique BoucherApr 14, 2020 at 17:21
-
I get that, I just wanted to throw it out there, and see if anyone knew off the top of there head– Mike FullerApr 14, 2020 at 17:24
Add a comment
|
1 Answer
Yes, when failover occurs the secondary changes back to read committed if that's how the DB is configured on the primary.
The developers are the only ones that can tell you if your application supports RCSI. It probably does, but if they designed it such that it depends on blocking to ensure things are done in the correct order, then it doesn't. Many third-party applications do not document whether it is supported when it really is, so step one is to contact the vendor support and ask and go from there.
answered Apr 14, 2020 at 17:58
