In a database mirroring system, you can access the database running on the mirror server by using a read-only connection. This functionality is useful to offload reporting or other operations that require read-only access to this database.

Any attempt to change the mirror database results in an error, which is the same behavior as when a database is started as read-only using the -r option. You can perform operations on temporary tables.

Queries that are executed against the mirror database can place locks, depending on the isolation level specified. If locks interfere with operations being applied from the primary server, then the connections holding the locks have their transactions rolled back and any open cursors for those connections are closed. Applications running at isolation level 0 do not add row locks, but still acquire schema locks. If the schema locks interfere with operations being applied from the primary server, the transaction on the mirror database is just rolled back.

Applications that require a consistent view of the database (and so cannot use isolation level 0) should consider using snapshot isolation. To do so, the allow_snapshot_isolation option must be set to On. This option takes effect on both the primary server and the mirror server, so the costs associated with snapshot isolation must be considered.

Connections to the mirror database are affected by transactions against the primary server, since those operations are then processed and applied by the mirror server. There can be a small delay between the time an update on the primary server is committed and the time that the update is available on the mirror server. Normally this delay is short, but you should keep this in mind when you are accessing the database running on the mirror server.

Connections to the mirror database are maintained if failover occurs and the mirror server becomes the primary server. After failover, a connection can make changes to the database. Query the value of the ReadOnly database property to determine whether the database you are connected to is updatable:

SELECT DB_PROPERTY( 'ReadOnly' );

Connections on a copy-node or mirror database are dropped or canceled in some cases when these connections prevent the transaction log from being applied. For example, if a connection is using a procedure that the transaction log is trying to alter or drop, then the connection that is blocking the transaction log from being applied is dropped, and a message is printed to the server console.

 See also