Right,
The two answers and a little thought got me to something approaching an answer.
First a little more clarification:
The app is written in C# (2.0+) and uses ADO.NET to talk to SQL Server 2005.
The mirror setup is two W2k3 servers hosting the Principal and the Mirror plus a third server hosting an express instance as a monitor. The nice thing about this is a failover is all but transparent to the app using the database, it will throw an error for some connections but fundamentally everything will carry on nicely. Yes we're getting the odd false positive but the whole point is to have the system carry on working with the least amount of fuss and mirror does deliver this very nicely.
Further, the issue is not with serious server failure - that's usually a bit more obvious but with a failover for other reasons (c.f. the false positives above) as we do have a couple of things that can't, for various reasons, fail over and in any case so we can see if we can identify the circumstance where we get false positives.
So, given the above, simply checking the status of the boxes is not quite enough and chasing through the event log is probably overly complex - the answer is, as it turns out, fairly simple: sp_helpserver
The first column returned by sp_helpserver is the server name. If you run the request at regular intervals saving the previous server name and doing a comparison each time you'll be able to identify when a change has taken place and then take the appropriate action.
The following is a console app that demonstrates the principal - although it needs some work (e.g. the connection ought to be non-pooled and new each time) but its enough for now (so I'd then accept this as "the" answer"). Parameters are Principal, Mirror, Database
using System;
using System.Data.SqlClient;
namespace FailoverMonitorConcept
{
class Program
{
static void Main(string[] args)
{
string server = args[0];
string failover = args[1];
string database = args[2];
string connStr = string.Format("Integrated Security=SSPI;Persist Security Info=True;Data Source={0};Failover Partner={1};Packet Size=4096;Initial Catalog={2}", server, failover, database);
string sql = "EXEC sp_helpserver";
SqlConnection dc = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, dc);
Console.WriteLine("Connection string: " + connStr);
Console.WriteLine("Press any key to test, press q to quit");
string priorServerName = "";
char key = ' ';
while(key.ToString().ToLower() != "q")
{
dc.Open();
try
{
string serverName = cmd.ExecuteScalar() as string;
Console.WriteLine(DateTime.Now.ToLongTimeString() + " - Server name: " + serverName);
if (priorServerName == "")
{
priorServerName = serverName;
}
else if (priorServerName != serverName)
{
Console.WriteLine("***** SERVER CHANGED *****");
Console.WriteLine("New server: " + serverName);
priorServerName = serverName;
}
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("Error: " + ex.ToString());
}
finally
{
dc.Close();
}
key = Console.ReadKey(true).KeyChar;
}
Console.WriteLine("Finis!");
}
}
}
I wouldn't have arrived here without a) asking the question and then b) getting the responses which made me actually think
Murph