[RESOLVED] Quickly determine if the SQL is reachable

[dlscott56]

I'm working on a program that has to continue functioning even if the SQL database isn't available. If the user has sufficient rights they can continue on using manually entered values.

The way I'm doing this it takes a very long time to fail before this can happen. Is there a way to quickly, or continually, determine if the SQL database is available so that I can avoid trying the stored procedure if it isn't?

    
    Dim tblSteps As DataTable = New DataTable
    Dim StepsBindingSource As New BindingSource

    Private Sub cmdExecuteSproc(ByVal SO_Number As String, ByVal Prev_SO_Number As String)
        Dim spSteps As New SqlCommand("sproc_shop_order_get_recipe1", cnPMSQL)
        spSteps.Parameters.AddWithValue("@int_shop_order", SO_Number)
        spSteps.Parameters.AddWithValue("@int_prev_so", Prev_SO_Number)
        spSteps.CommandType = CommandType.StoredProcedure

        Try
            Dim StepsAdapter As SqlDataAdapter = New SqlDataAdapter(spSteps)
            tblSteps.Clear()
            StepsAdapter.Fill(tblSteps)
            StepsBindingSource.DataSource = tblSteps

        Catch ex As Exception
            MessageBox.Show("Database not available" & Environment.NewLine & ex.Message)
        End Try
    End Sub

[chris128]

Not really I'm afraid. You can do a few things but nothing is going to tell you beyond any doubt whether or not the database is available other than actually trying to execute an SQL statement against the server. Things you can do are:
-- Ping the server that the SQL Server instance is on - but if you get a reply then this only indicates that the server is online, not that the SQL services are running correctly on it or if your database is accepting connections etc
-- Try to connect to the server on the TCP port that SQL uses - this would be slightly better than a ping in that it would at least tell you that the SQL services are running and listening for connections but it doesnt tell you anything more than that. You could get a successful connection and easily still run into problems when you actually tried to execute an SQL statement.

I guess if your problem is that it takes a long time for the command to timeout before you get an error then you could try just shortening the timeout by modifying the CommandTimeout property of your SqlCommand object. I think the default is 30 seconds, so I'm guessing you could shorten this considerably if you are on a decent network and know that unless something was really wrong then your commands would never take anywhere near that long to execute. Worth reading this note on the MSDN documentation for the CommandTimeout property as well:

This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

[dlscott56]

Thank you Chris. This will be on some industrial equipment in the same physical location as the SQL server so the network is good and very reliable. Unless we lose a switch or hub somewhere or the actual server running SQL Server goes down we should get fast response. So changing the CommandTimeout as well as use of ping may be enough to make it manageable.

Thanks for the response. Very helpful.

[dlscott56]

Hmmm, changing the CommandTime out for the SqlCommand object didn't change the amount of time it hangs. By the way it's hanging up at the StepsAdapter.Fill(tblSteps) portion of the code.

[jmcilhinney]

Note that there are two timeouts associated with data access. There's a timeout on the connection and then, once the connection is made, there's a timeout on the command. If you're failing to make a connection then the command timeout is meaningless because you'll never get to execute the command. I believe that both timeouts are 30 seconds by default. Try changing the ConnectionTimeout property of your SqlConnection.

[dlscott56]

Ok, found the SqlConnection Connection Timeout property to be read only after an instance is created but settable through the connection string. The default is 15 seconds. Default Commandtimeout for the SqlCommand is 30 seconds.

So there seem to be 3 things that need to be done to improve the delay for a failed attempt at running a stored procedure.

1. If the network or server is unavailable then neither of these timeout values matter? The delay in trying to find the server is controlled by some other network timeout property? Running ping could help with this one to avoid the attempted connection at all.

2. If the network and server are up and running but the SQL Server is not running on it the Connection Timeout comes into play? Setting this to the lowest possible value for my particular situation would help here.

3. Network, server, SQL Server running but some problem exists that won't allow or delays the running of the stored procedure is where the Command Timeout helps? Again setting it to the lowest safe value to avoid prematurely timing out.

I'll have to wait and run some tests on this while at work, maybe tomorrow, but can put the code in place to try and test out all 3 scenarios.

[chris128]

Well I would assume the Connection Timeout comes into play even if the server/network is completely down. I think you could get away with setting it to 5 seconds really because unless your server/network has some serious issues then the SQL server should easily accept the connection within that time - it probably takes less than a second when all is working properly.

[dlscott56]

What I found was that if I couldn't find the server (changed the IP in the connection string to something out of our subnet) it would hang up for as much as 40 seconds. Although this time seemed to vary. Even with the connection timeout set to 5 seconds. If I set the IP to a valid address but one without SQL Server installed on it my program would timeout based on the connection timeout value of 5 seconds.

So I added a boolean set using the ping command to periodically test the network path to the server. I check this and don't attempt the SQL stored procedure if false, using my alternate method for getting required data from the authorized user.

There's probably a better, more efficient, way to do this but it seems to work ok for reducing the delay in the event of a problem.