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?

  • 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) Apr 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 Apr 14, 2020 at 17:24

1 Answer 1

  1. Yes, when failover occurs the secondary changes back to read committed if that's how the DB is configured on the primary.

  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.