Best practice: How to detect if a SQL Server is available and failover if not

Question

Hi there, I am looking for a way to detect if a SQL Server fails to respond (timeout/exceptions etc) so that if our C#/.net program trying to access the server asks a server that is down for maintenance, it will jump and try another automatically.

I would like a solution where we do the SQL connection and THEN get the timeout. We could build a WCF service and ask that one, but that is a bit of an overkill in my opinion.

The sync between multiple server locations is not the issue here.

Our development platform at the moment is SQL2008express as its plenty at the moment, but later on we might switch to real SQL2008 servers (or whatever is latest when the time comes).

Clients will connect to "first known" in a "last known dynamic list" asking a "rootserver" or hardcoded configs for first lookup.

When clients loses connections, they will automatically have to try to reconnect to other nodes in the clusters and use whatever returns a reply first. The nodes will individualle connect and share data through other services which we also distribute in the cloud.

We know that mirroring and clustering might be available through large licenses, but our setup demands a more dynamically "linking" and we believe this approach suits our needs better.

So... to be specific:

we need to detect when a SQL-server goes offline, when its not available anymore. Eg. in the middle of a transaction or when we try to connect.

Is the best approach to do a "try-catch" exception handling or is there better tricks when looking over WAN's and using C#/.net =

EDIT

I've received a lot of good ideas to use failover servers, but I would like a more programatical approach, so whats the best way to query the server if its available?

Situation: 4 different SQL servers running on seperate WAN/IP's, each will maintain a list of "where the others are" (peer-to-peer). They will automatically be moving data from each other (much like a RAID-setup where data is spread out on multiple drives)

A client retries the list from an entry-point-server and asks the first available. If the client asks a server that is "down for maintance" or the data has moved to one of the other servers, it must automatically ask the next in the list.

What we are looking for..

is the best way from within C#/.net to detect that the server is currently unavailble.

1. We could have a service we connect to and when we loose this, the server is off
2. We could make a "dbConnectionSqlServer3.open()" and wait for the time out.
3. We could invest in "real cluster servers", and pay a lot + bind ourselfs to 1 SQL-server type (The SQL platform might change in future, so this is not a real option)

So whats your vote here: 1 or 2? or can you come up with a number 4 that beats the h**k out of ideas? :o)

Answer 1

In the ConnectionString you can specify a failover partner like

Data Source=<MySQLServer>;Failover Partner=<MyAlternateSQLServer>;Initial Catalog=<MyDB>;Integrated Security=True

On http://www.connectionstrings.com there is a section talking of DataBase mirroring.

Answer 2

I would probably take a different approach.

I'd designate a server (with a failover partner) to be the holder of the server list. It should monitor all of the other servers for availability.

When the client initially wants to connect, it should contact the list server and ask it what to use. From that point forward the client should stick with it, unless a failure is detected. At which point it should contact the list server to get a new one.

The list server would be able to implement any type of load balancing you wanted just by telling the clients which one to connect to. Further, deploying new servers would be easy as the only "list" would be maintained by this primary server.

This completely ignores any sort of server synchronization issues you might have.

One major benefit is that by having a central server doing the monitoring your clients won't have to fall through 3, 5, or 10 servers just to find one that's up. which would increase responsiveness.

UPDATE
@BerggreenDK: The best, and probably only assured way, to detect if a server has failed is to connect to it and run a simple query. All other mechanisms such as pinging, a simple check if the port is open, etc. might give a false positive. Even having a process running on that server itself may give a false reading if the server itself is up but SQL Server is down (e.g: the database is offline).

Ultimately it sounds like your client will have to connect to one of the gateway servers and get a short list of sql servers to attempt to connect to. On failure, it will have to rotate through that short list. If all are down it will have to go back to the gateway and ask for a new list to start the process over.