[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

How to handle an SQL server being down

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
0
monitorwa
Asked:
monitorwa
1 Solution
 
Ashish PatelCommented:
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.
0
 
mikelittlewoodCommented:
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.
0
 
JohnjcesCommented:
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;
  ClientSocket1.Open;
    repeat
      Application.ProcessMessages
          until
             Done;  // Boolean value

Now in the ClientSocketOnconnect event I have

ClientSocket1.Close;
// and other startup stuff

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

Lastly in the ClientSocket.OnError I have
 ErrorCode := 0;
 ClientSocket1.Close;
 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.

John
0
 
JohnjcesCommented:
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 Torry.net and DelphiPages.com.

Let us know if anything helped.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now