How to handle an SQL server being down

Posted on 2007-10-02
Last Modified: 2013-11-23
I've made a program for a client that records live transactions into their MS Sql server database.  When the SQL Server goes down, the program will go into an 'Offline Mode', storing the transactions locally.  I want the program to be able to automatically check if the sql server is back up and (if it is), record these transactions it stored locally.

I tried doing this through a timer, but the program comes to a halt for about 30secs, when it's trying to reconnect to the sql server and the sql server still isn't there.

Any suggestions how I can quickly check if it's available.  I'm using the dbexpress components and just trying to open the sqlconnection again to see if it can re-connect
Question by:monitorwa
    LVL 23

    Expert Comment

    by:Ashish Patel
    The connection timeout parameter might be a big higer, so it seems to you that its taking a long time or is halting. Just set the connectiontimeout to 30 secs ie. 300 mili secs. and try again.
    LVL 15

    Expert Comment

    You will still need a timer to check your connection is active as unfortunately there are no message sent back from servers to tell local connections that it is about to go down, especially if it is a hard disconnect.
    Like asvforce said, reduce your timeout property and use a timer to check the connection is still available, possbily with another connection object that doesnt get used and can just be used for checking purposes.
    LVL 18

    Expert Comment

    What version of Delphi are you using? In any event, all version come with some sort of INternet component or TCP/IP networking components like a TClientSocket or TServerSocket. Delphi 2006 has a TTCPClient and TTCPServer which are very similar. I have not tested this with D2006 but it DOES work on D5 and should give you a start.

    Anyway, MS SQL Server works on TCP port 1433. If it is not running this prot will be absent. If active, the port will be open. So in this example we will see if this port is open or not.

    Again I am using the TCLientSocket. You can put this in a TTimer. Make sure you disable and enable the timer appropriately. You do not have to have the repeat. Your timer could do that. If you do not want the application to stop while checking for a connection, use a threaded timer. The JVCL has them and are freely available components.

      ClientSocket1.Active := False;
      ClientSocket1.Address := Your SQLServerIPAddress;
      ClientSocket1.Port := 1433;
                 Done;  // Boolean value

    Now in the ClientSocketOnconnect event I have

    // and other startup stuff

    In ClientSocket.OnDisconnect I se tmy Done boolean value.
     Done := True;

    Lastly in the ClientSocket.OnError I have
     ErrorCode := 0;
     Done := True;

    The ErrorCode := 0 is so you do not get a runtime error advising you of an error.

    Basically, when you try and open port 1433 on your SQL Server and it comes back OK, you can reconnect. When it errors it is down and you retry until it is up.

    I really hope this got you going in a simpler (maybe not simpler) but a good direction.

    LVL 18

    Accepted Solution

    Just as another try which I mentioned above, try putting your current code from your basic TTimer into a threaded timer. Your application may not stop for the 30 seconds or so.

    Again, threaded timers are freely available components form and

    Let us know if anything helped.


    Author Comment

    Thanks for that, the threaded timer seems to make it work a lot better.  Thanks everyone for there help and suggestions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
    Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now