Using Database Mirroring
Database mirroring in SQL Server 2005 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 database. In addition, the SqlConnection object supports supplying the name of a failover partner server in the ConnectionString. See "Database Mirroring" in SQL Server Books Online for information on configuring a database for mirroring.
Database mirroring in SQL Server 2005 increases database availability by allowing you to maintain a hot standby server that contains a copy, or mirror database, of the production database, called the principal database. If the principal database on the production server fails, the partner server can promote its database from mirror to principal. For example, ServerA and ServerB are two partner servers, with the principal database residing on ServerA, and the mirror database on ServerB. If ServerA goes offline, the mirror database on ServerB can be manually or automatically switched (or failed over) to the role of principal database.
As changes occur in the principal database, the transaction log is immediately sent to the mirror database and rolled forward to keep the two databases synchronized. Mirroring is implemented per database. Mirror databases can reside on the same server with other databases. The partner servers containing the principal and mirror databases ideally should reside on two different computers, however.
There are two operating modes to choose from when setting up and configuring database mirroring in SQL Server 2005, offering varying levels of availability and performance. In the highest availability mode, the partner server instances synchronize the mirror database with the principal database. In the presence of a third server, the witness server, if the principal database becomes unavailable, the principal role can automatically fail over to the mirror database. To learn more about these configuration options and to set up and configure mirroring, see the topic "Database Mirroring" in SQL Server Books Online.
Mirroring Support in SqlClient
Database mirroring is fully supported in the .NET Data Provider for SQL Server in ADO.NET. The following simplified sequence of events occurs for a SqlConnection object that targets a database configured for mirroring:
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.
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.
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.
Mirroring Restrictions
Database mirroring with successful failover is subject to the following restrictions:
Transparent redirection only works for connections that explicitly specify the initial catalog or database name in the ConnectionString. If the client receives failover information on a connection that doesn't have an explicitly specified initial catalog or database, the failover information is not cached and the application does not attempt to fail over in if the principal server fails.
If a ConnectionString has a value for the failover partner but no value for the initial catalog or database, SqlClient throws an InvalidArgumentException when the string is used to set a SqlConnection object's ConnectionString property or is passed to the SqlConnection constructor.
Mirroring support on the server is configured on a per-database basis. If data manipulation operations are executed against other databases not included in the principal/mirror set, either by using multipart names or by changing the current database, the changes to these other databases do not propagate in the event of failure. No error is generated when data is modified in a database that is not mirrored. The developer must evaluate the possible impact of such operations.
Determining the Failover Partner Server
The .NET Data Provider for SQL Server supports database mirroring in SQL Server 2005 in two connection modes: explicit and implicit. Explicit connection mode allows the developer to supply the name of a failover partner server in the connection string. Implicit connection mode omits the name of the failover partner server in the connection string.
Caching the Partner Server Name
There are two ways that a client application can determine the name of the partner server for a mirror database:
A server name can be specified in the ConnectionString property of a SqlConnection object (explicit connection). If the principal database is unavailable when the client application first connects, the client transparently attempts a connection with the mirrored database on the specified failover partner.
No special action is required (implicit connection). The client obtains the name upon its initial connection. If the principal database is unavailable when the client application connects, then a SqlException will be raised.
Regardless of whether the failover partner server name is specified in the connection string, when a SqlConnection is opened, the client automatically obtains and caches the name of the partner server, if one is configured. This cached name overrides a name specified in the connection string. The failover partner server name supplied in the connection string is used only if the initial connection to the principal database fails.
Specifying the Failover Partner
Use the following syntax in the connection string where PartnerServerName is the name of the partner server:
";Failover Partner=PartnerServerName"
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.
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;