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

In a mirroring system, you do not necessarily know which database server is acting as the primary server and which one is acting as the mirror server. If you want to be able to connect to the database running on the mirror server, define a mirror server with type MIRROR. This allows connections to find the mirror server by providing a server name that is used to access the read-only mirror database. A server name specified in this way is only active when the database server is acting as mirror for the database. For example, the following statement defines the name mysamplemirror as an alternate server name for use when connecting to the database running on the mirror server:

CREATE MIRROR SERVER mysamplemirror
AS MIRROR
connection_string='SERVER=mysamplemirror;HOST=winxp-1:6871,winxp-2:6872';

Any attempt to make a change to the 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, but events are not fired on the mirror database. Event firing only starts after failover from the primary server to the mirror server takes place. The DatabaseStart and MirrorFailover events fire at that time, if they are defined. For more information, see Understanding system events.

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. You can query the value of the ReadOnly database property to determine whether the database you are connected to is updatable:

SELECT DB_PROPERTY( 'ReadOnly' );

 See also