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 is acting as the primary server and the mirror server. If you want to be able to connect to the database running on the mirror server, include the -sm server option when you start the database server. This allows connections to find the mirror server by providing a server name that is used to access the read-only mirror database. The server name specified by the -sm option is only active when the database server is acting as mirror for the database. Typically, you would specify the -sm option for both database servers because you do not know which server is acting as the primary or the mirror server. For example, the -sm option instructs the database server to use mysamplemirror as an alternate server name when connecting to the database running on the mirror server:

dbsrv11 -n myserver satest.db sample.db -sn mysampleprimary 
-sm mysamplemirror
-xp "partner=( ENG=server2;LINKS=TCPIP( PORT=2637;TIMEOUT=1 ) );auth=abc;
arbiter=( ENG=arbiter;LINKS=TCPIP;( PORT=2639;TIMEOUT=1 ) );mode=sync"

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

• -sm database option
• ReadOnly property: Database properties