Monday, September 8, 2008

Writing Applications That Benefit From Database Mirroring

One of the most useful high-availability features introduced in SQL Server 2005 is without doubt database mirroring. Some of the features that stand out when you compare this technology with database clustering include:

  • Failover time can be sub-second depending on the configuration you are using. We are talking much faster than a cluster failover
  • You have the option of using synchronous or asynchronous commits across the two servers:
    • When you use synchronous mirroring, control doesn't return to the client until a commit happens on both the principal and mirror databases
    • When you use asynchronous, SQL Server returns to the client in the usual fashion and commits on the mirror when it can. Of course this can lead to some potential for data loss in the event of a failover
  • The buffer cache on the mirror server is kept "hot". That is to say that recently used pages on the principal server are also present in the buffer cache on the mirror server. The benefit here is that the redo time will be shortened in the circumstance that a failover occurs. Also users are less likely to notice a performance hit.
  • You can use database snapshots on the mirror database for reporting purposes
  • You can implement mirroring with cheap hardware.
  • Easier to configure than log shipping

So far I have seen quite a few implementations where customers are using asynchronous mirroring with manual failover. Using the witness role is yet to gain popularity based on my experience.

Wiring up the Client

Perhaps the main drawback with mirroring when compared to clustering is that the client needs a little extra intelligence when it comes reconnecting after a failover. The diagram below (from MSDN) shows how the client connects initially.

Take a look at the connection string below. You will notice that there is an extra bit which specifies the IP address and port of the Failover Partner.

"Server=,1433; Failover Partner=,1234; Database=MirrorTest; Network=dbmssocn; Integrated Security=SSPI";

The SQL Native Client or .NET Data Provider for SQL Server will initially try to connect to the server specified in the Server part of the connection string. If it can't connect, it will try the failover partner name. If the failover partner is in fact the current principal server, then the client recognises this and downloads the server/instance name of the current mirror server and stores it in memory, overwriting the value provided by the connection string.

Of course if the principal database is offline, and the mirror is yet to be promoted then the client will fail to connect. This situation is highly likely in the event that a witness server is not being used to facilitate automatic failover.

Client Behaviour during a Failover

If a client is connected when a failover takes place, the client unfortunately has to be smart enough to attempt to reconnect. This is a bit of a drawback for legacy applications, which might need a restart.

I wrote a few lines of C# to see how long a manual failover would knock my test client off the air for.

public string GetData()

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT @@SERVERNAME", conn);

return DateTime.Now.ToString("hh:mm:ss") + ": " + ds.Tables[0].Rows[0][0].ToString();

catch (Exception ex)
string retVal = "Connection Failed. Retrying Connection...";

conn = new
conn.ConnectionString = connstring;

catch( Exception x )
retVal = "No Connection...";
return retVal;

I just set up a timer that called this method every 100 milliseconds, and it outputs something like the following:

Then I issued the following command on my principal server. (Note you have to be in the master database to issue this command):


And viola! My client reconnects to the new principal server within 100ms

Database mirroring is an exciting technology, and I would encourage people to consider using it as an underlying high-availability platform when building applications.

No comments: