We help IT Professionals succeed at work.

How to handle an SQL server being down

monitorwa
monitorwa asked
on
583 Views
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
Comment
Watch Question

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.
Mike LittlewoodEngineer

Commented:
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.

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.