1

We want to setup Mirroring High Performance Asynchronous. Server A Principal contains database which mirrors to ServerB. Mirroring does not allow read-queries unless snapshot is created.

1) If we utilize snapshot for reporting queries, and high volume OLTP transactions are sent from Server A to Server B, Will Report queries create locking/blocking contention, which will prevent Server B Mirror from receiving updates?

2) If so, can I place the Server B Mirror database in Read-Committed Snapshot Isolation (RCSI), to prevent locking/blocking (while ServerA is in Read committed) ? Or does Mirror Database always have to be in same transaction isolation level as Principal ?

Improve this question

1 Answer 1

Reset to default
1

1) If we utilize snapshot for reporting queries, and high volume OLTP transactions are sent from Server A to Server B, Will Report queries create locking/blocking contention, which will prevent Server B Mirror from receiving updates?

It will depend. If you are reading a page that has been changed since snapshot was created you are reading the page from sparse file. In that case there is no contention. But if you are reading from a page that did not change since snapshot was created you are reading from source database file. Which may cause contention at the file level since both the source database and the snapshot will be accessing the same MDF.

These are two good articles you might find helpful.

  1. Database Snapshots by Basit Aalishan Masood-Al-Farooq
  2. SQL Server 2005 Snapshots by Andrew Calvett.

2) If so, can I place the Server B Mirror database in Read-Committed Snapshot Isolation (RCSI), to prevent locking/blocking (while ServerA is in Read committed) ? Or does Mirror Database always have to be in same transaction isolation level as Principal ?

You cannot do that. As Mirror Database is in restoring state, you cannot make any change. Allowing different configuration goes against the principal or database mirroring where it guaranteed to have same configuration and data so roles of Principal and Mirror can be switched at any time.

Not related to your question but you might find this useful especially the comment in fact, you can’t execute this on the mirror, because the database is in the restoring state.

How to Enable RCSI for a Database with Database Mirroring by Sanjay Mishra

Improve this answer
0

Your Answer

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