Starting with Sql Server 2005, Sql Server provides an interesting high availability option at individual database level, called database mirroring. When configured in high availability mode (principal, mirror, and witness), provided that:

1. Failover Partner information is supplied in the connection string;
2. Application code knows to retry database operations;

then automatic fail over will occur.

This all sounds good, but seeing is believing. So I wrote a simple C# console program to simulate what happens when the principal database fails. This simple program connects to a mirrored database, grabs data, and then prints it out on the console. It is a bit contrived, and I used the somewhat dreaded goto statement to retry connections, but hopefully it demonstrated the point. I tested this on VMWare virtual machines, so I put in 1 second delay.

Here is what I tested while my console program is running:

1. Manual failover through the principal’s property page. The program didn’t skip a beat. Results below:

Haidong
12/4/2008 9:21:32 PM
Haidong
12/4/2008 9:21:33 PM
Haidong
12/4/2008 9:21:34 PM
Haidong
12/4/2008 9:21:35 PM
Haidong
12/4/2008 9:21:36 PM
Haidong
12/4/2008 9:21:37 PM
Haidong
12/4/2008 9:21:38 PM

2. Stopped Sql Server service on the principal. Similar results as above;

3. Yanked the power cable off the principal box. There was a noticeable delay. Notice the one minute delay in results below:

Haidong
12/4/2008 10:00:23 PM
Haidong
12/4/2008 10:00:24 PM
Haidong
12/4/2008 10:00:25 PM
Haidong
12/4/2008 10:01:24 PM
Haidong
12/4/2008 10:01:25 PM
Haidong
12/4/2008 10:01:26 PM
Haidong
12/4/2008 10:01:27 PM
Haidong
12/4/2008 10:01:28 PM
Haidong
12/4/2008 10:01:29 PM

All in all, I am pretty impressed. If you compile and run the program, use Ctrl – c to stop if you think you’ve seen enough. Source code below.
[sourcecode language=”c#”]
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace SqlDbConsole
{
class Program
{
[STAThreadAttribute]
static void Main(string[] args)
{
while (true)
{
RunSimpleSql();
}
}
static void RunSimpleSql()
{
SqlConnection conn = new SqlConnection(“Data Source=PrincipalServer;Failover Partner=MirrorServer;Initial Catalog=MyDb;Integrated Security=SSPI;”);
PointOfRetry:
try
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = “SELECT TOP 1 FirstName from Contact”;
cmd.CommandType = CommandType.Text;

SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read() == true)
{
string s;
s = (string)rdr[“FirstName”];
Console.WriteLine(s);
//Console.WriteLine(“Beijing: {0}”, TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, TimeZoneInfo.Local.Id, “China Standard Time”));
Console.WriteLine(DateTime.Now);
}
Thread.Sleep(1000);
}
catch
{
goto PointOfRetry;
}
}
}
}
[/sourcecode]