views:

62

answers:

1

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.

+5  A: 

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.

Remus Rusanu
This is the only way to tell, reliably, if sql server is in fact up and running. If you are simply connecting to 1433, the SQL Browser service might be available, but the server itself isn't responding for some reason.
Chris Lively
@Remus: Allow me to paraphrase your answer for my own understanding. I should have a method that connects and runs a dummy query. If it fails to run(??) or returns a certain result I am NOT Connected otherwise I am? You are saying I will still be using a `Timer` to poll my server and it sounds like a `Try/Catch` as well. I will just be replacing my `TcpClient.Connect()` call with my Dummy Query. Am I correct?
Refracted Paladin
@Remus: When you say 'different metaphor', could you elaborate? From that point on I do not understand what you are trying to say to me. Thank you for your time.
Refracted Paladin
By different metaphor I'm referring to architect the application to work offline explicitly. Have the application work *always* offline and leave the task of synchronizing the local data with the central data to dedicated components, like Service Broker, or Sync Framework, or Replication (each one would be better fit to a specific scenario).
Remus Rusanu
And yes, you are correct about replacing TcpClient.Connect with a dummy SqlCommand.ExecuteQuery
Remus Rusanu
@Remus: It is interesting that you say that. That is my current architecture. Everyone works "offline" with there SQL Express instance and a Merge Replication Module is run through code to keep it in sync with the central DB. I am moving away from that. What we have is users who are "connected" 80% of the time. We also have Teams of users when they go out in the field so there are issues with them being in sync. Thirdly, we have staff who NEVER go out in the field. By switching to a model where they are primarily connected seems to solve many, many problems for me.
Refracted Paladin