Database Mirroring in SQL Server (ADO.NET)

Database mirroring in SQL Server allows you to keep a copy, or mirror, of a SQL Server 2005 database on a standby server. Mirroring ensures that two separate copies of the data exist at all times, providing high availability and complete data redundancy. The .NET Data Provider for SQL Server provides implicit support for database mirroring, so that the developer does not need to take any action or write any code once it has been configured for a SQL Server 2005 or SQL Server 2008 database. In addition, the SqlConnection object supports an explicit connection mode that allows supplying the name of a failover partner server in the ConnectionString.

The following simplified sequence of events occurs for a SqlConnection object that targets a database configured for mirroring:

1. The client application successfully connects to the principal database, and the server sends back the name of the partner server, which is then cached on the client.

2. If the server containing the principal database fails or connectivity is interrupted, connection and transaction state is lost. The client application attempts to re-establish a connection to the principal database and fails.

3. The client application then transparently attempts to establish a connection to the mirror database on the partner server. If it succeeds, the connection is redirected to the mirror database, which then becomes the new principal database.

Specifying the Failover Partner in the Connection String

If you supply the name of a failover partner server in the connection string, the client will transparently attempt a connection with the failover partner if the principal database is unavailable when the client application first connects.

";Failover Partner=PartnerServerName"

If you omit the name of the failover partner server and the principal database is unavailable when the client application first connects then a SqlException is raised.

When a SqlConnection is successfully opened, the failover partner name is returned by the server and supersedes any values supplied in the connection string.

Retrieving the Current Server Name

In the event of a failover, you can retrieve the name of the server to which the current connection is actually connected by using the DataSource property of a SqlConnection object. The following code fragment retrieves the name of the active server, assuming that the connection variable references an open SqlConnection.

When a failover event occurs and the connection is switched to the mirror server, the DataSource property is updated to reflect the mirror name.

Dim activeServer As String = connection.DataSource

string activeServer = connection.DataSource;

SqlClient Mirroring Behavior

The client always tries to connect to the current principal server. If it fails, it tries the failover partner. If the mirror database has already been switched to the principal role on the partner server, the connection succeeds and the new principal-mirror mapping is sent to the client and cached for the lifetime of the calling AppDomain. It is not stored in persistent storage and is not available for subsequent connections in a different AppDomain or process. However, it is available for subsequent connections within the same AppDomain. Note that another AppDomain or process running on the same or a different computer always has its pool of connections, and those connections are not reset. In that case, if the primary database goes down, each process or AppDomain fails once, and the pool is automatically cleared.

Database Mirroring Resources

For conceptual documentation and information on configuring, deploying and administering mirroring, see the following resources in SQL Server Books Online.

See Also

Other Resources

ADO.NET Managed Providers and DataSet Developer Center