How to Implement Exchange like availability monitoring of internal SQL Server

Question

We have an internal app(Thick Client) that relies on our central SQL server. The app is a Desktop app that allows the users to work in "Offline" mode (e.g. Outlook). What I need to accomplish is a way to accurately tell if SQL is available or not.

What I have so far:

I currently use the following method -->

 internal static void CheckSQLAvailability()
    {
        using (TcpClient tcpc = new TcpClient())
        {
            try
            {
                tcpc.Connect(Settings.Default.LiveSQLServer, Settings.Default.LiveSQLServerPort);
                IsSQLAvailable = true;                    
            }
            catch
            {
                IsSQLAvailable = false;
            }
        }
    }

I am not crazy about this approach for the following reasons.

• Prone to false Negatives
• Needs to be "manually" called
• Seems "smelly" (the try/catch)

I had thought to use a timer and just call this every X(3??) minutes and also, if a negative result, try a second time to reduce the false negatives.

There is a similar question here -->Detecting if SQL server is running but it differs from mine in these ways:

• I am only checking 1 server
• I am looking for a reactive way versus proactive

So in the end, is there a more elegant way to do this? It would all be "in-network" detection.

P.S. To offer some background as requested in an answer below: My app is a Basic CRUD app that can connect to our Central SQL Server or a local SQLExpress Server. I have a Merge Replication Module that keeps them in Sync and the DAL is bound to a User.Setting value. I can, already, manually flip them from Central to Local and back. I just want to implement a way to have it automatically do this. I have a NetworkChangeDetection class that works quite well but, obviously, does not detect the Remote SQL's.

Answer 1

Consider what the Windows Cluster monitor does for a SQL Server Cluster resource: it actually connects and runs a dummy query (SELECT @@version). This indicates that the SQL is running, is actively listening for requests, and is able to run a request and return a result. For the clustering monitor the response to this query is the 'heartbeat' of the server and if it fails to get a response, for whatever reason, it may initiate a cluster failover.

Only connecting to TCP has several drawbaks in my opinion:

• it omits non-TCP protocols like local shared memory (LPC) or remote net pipes (SMB)
• it requires hard codded TCP port number as opposed to let the instance port listening auto-discovery do its work (SQL Browser and friends)
• it only establishes that the OS level socket can be established, it does not validate that the SQL Server itself is in a runnable state (non-yielding scheduler might block network IO requests acceptance, scheduler overload and worker starvation may do the same, memory resource exhaustion etc etc).

Unfortunately there is no way to get a notification from SQL Server itself of saying 'hey, I'm active, won't you send some requests?'. I don't know all the details of your fat client ('thick app'), but perhaps you should investigate a different metaphor: clients do all work locally, on SQL Express instances, and these instances synchronize the data when the server is available. Service Broker was designed specifically with this connection retry mode and it would hide the server availability due to its asynchronous loosely coupled programming API.