How to connect to SQL Server redundantly using C#?

I have two SQL Server machines, server1 and server2 that are redundant and have the same data.

My application开发者_开发百科 wants to select data from a table in the msdb database every 1 second. But my application can connect only using one conection string. How to edit my application can work redudantly with both servers?

• SQL Server 2000 SP4
• Window Server 2003
• C#

My connection string is

server=10.15.13.70;database=msdb;user id=sa;pwd=""

Please advise me.

Pick a server at random and insert the name or IP for that in the connection string before opening the connection.

using (SqlConnection _con = new SqlConnection("server=" + giveMeAServer() + ";database=msdb;user id=sa;pwd="))

and:

private String giveMeAServer()
{
    return "10.15.13.70";
}

The proper fix, especially if you are reconnecting as often as once per second, is most likely to set up some sort of cluster on the SQL Server side, and connect to that, rather than having your application worry about load balancing the database backend.

Better is you try to open the connection for some interval say 5ms, and if connection cant be opened, switch to other server through connection string

What you are looking for is Load balancing. This is a technique that can only be applied to readonly databases. It basically spreads the load across multiple servers.

Here is a good read on load balancing with SQL Server 2000 by Microsoft.

http://technet.microsoft.com/en-us/library/cc917707.aspx

Imho, what you try to achieve should not be handled programmatically but on a hardware level.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

(from http://www.mssqltips.com/tip.asp?tip=1289)