SQL Server 2005 Connection Question

Question

Hi there,

In SQL Server 2005, is there a way to specify more than one connection string from within a .NET Application, with one being a primary preferred connection, but if not available it defaults to trying the other connection (which may be going to a diff DB / server etc)?

If nothing along those exact lines, is there anything we can use, without resorting to writing some kind of round-robin code to check connections?

Thanks.

Answer 1

Unfortunately there are no FCL methods that do this - you will need to implement this yourself.

Answer 2

We would typically use composition on our SqlConnection objects to check for this. All data access is done via backend classes, and we specify multiple servers within the web/app.config. (Forgive any errors, I am actually writing this out by hand)

It would look something like this:

class MyComponent
    {
    private SqlConnection connection;

    ....
    public void CheckServers()
    {
        // Cycle through servers in configuration files, finding one that is usable
        // When one is found assign the connection string to the SqlConnection
        // a simple but resource intensive way of checking for connectivity, is by attempting to run
        // a small query and checking the return value
    }
    public void Open()
    {

        connection.Open();

    }

    public ConnectionState State
    {
        get {return connection.State;}
        set {connection.State = value;}
    }
    // Use this method to return the selected connection string
    public string SelectedConnectionString
    {
        get { return connection.ConnectionString; }
    }
    //and so on
}

This example includes no error checking or error logging, make sure you add that, so the object can optionally report which connections failed and why.

Answer 3

Assuming that you'd want to access the same set of data, then you'd use clustering or mirroring to provide high availability.

• SQLNCLI provider supports SQL Server database mirroring

  Provider=SQLNCLI;Data Source=myServer;Failover Partner=myMirrorServer

• Clustering just uses the virtual SQL instance name.

Otherwise, I can't quite grasp why you'd want to do this...