<p>We have a couple of mirrored SQL Server databases.</p>

<p>My first problem - the key problem - is to get a notification when the db fails over. I don't <em>need</em> to know because, erm, its mirrored and so it (almost) all carries on working automagically but it would useful to be advised and I'm currently getting failovers when I don't think I should be so it want to know when they occur (without too much digging) to see if I can determine why.</p>

<p>I have services running that I could fairly easily use to monitor this - so the alternative question would be "How do I programmatically determine which is the principal and which is the mirror" - preferably in a more intelligent fashion than just attempting to connect each in turn (which would mostly work but...).</p>

<p>Thanks, Murph</p>

<p>Addendum: </p>

<p>One of the answers queries why I don't need to know when it fails over - the answer is that we're developing using ADO.NET and that has automatic failover support, all you have to do is add <code>Failover Partner=MIRRORSERVER</code> (where MIRRORSERVER is the name of your mirror server instance) to your connection string and your code will fail over transparently - you may get some errors depending on what connections are active but in our case very few.</p>

[{'answer_id': 29277, 'author': 'Rob Allen', 'author_id': 149, 'author_profile': 'https://Stackoverflow.com/users/149', 'pm_score': 1, 'selected': False, 'text': "<p>If the failover logic is in your application you could write a status screen that shows which box you're connected by writing to a var when the first connection attempt fails.</p>\n\n<p>I think your best bet would be a ping daemon/cron job that checks the status of each box periodically and sends an email if one doesn't respond.  </p>\n"}, {'answer_id': 29296, 'author': 'Dane', 'author_id': 2929, 'author_profile': 'https://Stackoverflow.com/users/2929', 'pm_score': 1, 'selected': False, 'text': '<p>Use something like Host Monitor <a href="http://www.ks-soft.net/hostmon.eng/" rel="nofollow noreferrer">http://www.ks-soft.net/hostmon.eng/</a> to monitor the Event Log for messages related to the failover event, which can send you an alert via email/SMS.</p>\n\n<p>I\'m curious though how you wouldn\'t need to know that the failover happened, because don\'t you have to then update the datasources in your applications to point to the new server that you failed over to?  Mirroring takes place on different hosts (the primary and the mirror), unlike clustering which has multiple nodes that appear to be a single device from the outside.</p>\n\n<p>Also, are you using a witness server in order to automatically fail over from the primary to the mirror?  This is the only way I know of to make it happen automatically, and in my experience, you get a lot of false-positives where network hiccups can fool the mirror and witness into thinking the primary is down when in fact it is not.</p>\n'}, {'answer_id': 37116, 'author': 'Murph', 'author_id': 1070, 'author_profile': 'https://Stackoverflow.com/users/1070', 'pm_score': 3, 'selected': True, 'text': '<p>Right, </p>\n\n<p>The two answers and a little thought got me to something approaching an answer.</p>\n\n<p>First a little more clarification:</p>\n\n<p>The app is written in C# (2.0+) and uses ADO.NET to talk to SQL Server 2005.\nThe 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 <em>does</em> deliver this very nicely.</p>\n\n<p>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.</p>\n\n<p>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</p>\n\n<p>The first column returned by sp_helpserver is the server name. If you run the request at regular intervals saving the <em>previous</em> 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.</p>\n\n<p>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</p>\n\n<pre><code>using System;\nusing System.Data.SqlClient;\n\nnamespace FailoverMonitorConcept\n{\n    class Program\n    {\n        static void Main(string[] args)\n        {\n            string server = args[0];\n            string failover = args[1];\n            string database = args[2];\n\n            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);\n            string sql = "EXEC sp_helpserver";\n\n            SqlConnection dc = new SqlConnection(connStr);\n            SqlCommand cmd = new SqlCommand(sql, dc);\n            Console.WriteLine("Connection string: " + connStr);\n            Console.WriteLine("Press any key to test, press q to quit");\n\n            string priorServerName = "";\n            char key = \' \';\n\n            while(key.ToString().ToLower() != "q")\n            {\n                dc.Open();\n                try\n                {\n                    string serverName = cmd.ExecuteScalar() as string;\n                    Console.WriteLine(DateTime.Now.ToLongTimeString() + " - Server name: " + serverName);\n                    if (priorServerName == "")\n                    {\n                        priorServerName = serverName;\n                    }\n                    else if (priorServerName != serverName)\n                    {\n                        Console.WriteLine("***** SERVER CHANGED *****");\n                        Console.WriteLine("New server: " + serverName);\n                        priorServerName = serverName;\n                    }\n                }\n                catch (System.Data.SqlClient.SqlException ex)\n                {\n                    Console.WriteLine("Error: " + ex.ToString());\n                }\n                finally\n                {\n                    dc.Close();\n                }\n                key = Console.ReadKey(true).KeyChar;\n\n            }\n\n            Console.WriteLine("Finis!");\n\n        }\n    }\n}\n</code></pre>\n\n<p>I wouldn\'t have arrived here without a) asking the question and then b) getting the responses which made me actually <strong>think</strong></p>\n\n<p>Murph</p>\n'}]
['https://Stackoverflow.com/questions/28353', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1070/']