Setting up a C# method that tries to reconnect to a SQL Server on a different port after first failure

Question

I'm trying to set up a method that will attempt to connect to a SQL Server first on the default port (1433) and then on a different port like 7777 upon failure.

I want to avoid rebuilding the connection string and trying to connect again in case of failure because this method will be executed at a configured interval and I want to eliminate as much overhead as possible.

I have tried the following (courtesy of ConnectionStrings)

public void EstablishConnection()
{
    string ConnectionString = "Data Source=127.0.0.1; Failover Partner=127.0.0.1,7777; Initial Catalog=foo;Connection Timeout = 3; Persist Security Info =True;User ID=<id>;Password=<password>";

    try
    {
        SqlConnection Connection = new SqlConnection(ConnectionString);
        Connection.Open();
    }
    catch (SqlException)
    {
        // Connection failed 
    }
}

But based off of this article and my testing, it does not work the way I intended.

I could easily solve this by doing the following:

public void EstablishConnection()
{
    string ConnectionString = "Data Source=127.0.0.1;Initial Catalog=foo;Connection Timeout = 3; Persist Security Info =True;User ID=<id>;Password=<password>";

    try
    {
        SqlConnection Connection = new SqlConnection(ConnectionString);
        Connection.Open();
    }
    catch (SqlException)
    {
        try 
        {
          string ConnectionString = "Data Source=127.0.0.1,7777;Initial Catalog=foo;Connection Timeout = 3; Persist Security Info =True;User ID=<id>;Password=<password>";
          SqlConnection Connection = new SqlConnection(ConnectionString);
          Connection.Open();
        } 
        catch (SqlException) 
        {
           // Connection failed 
        }
    }
}

But this feels like spaghetti code and overall bad practice.

Also in the real application I am not fetching the full connection string from a configuration file, I am pulling the parameters from a configuration file and building the configuration string from there.

No the idea that I had was if it failed connecting to it on 1433, it would go to the Fail over Server, which is the same server on a different port. — Wamadahama, Jul 07 '16 at 21:13
Each instance of SQL Server has only one port. Need more than one instance of the sql server. — jdweng, Jul 07 '16 at 21:32

Adriano Repetti · Accepted Answer · 2017-08-01T08:48:05.480

First of all let's define an helper method to create and open the connection trying all given ports:

private SqlConnection TryEstabilishConnection(params int?[] portNumbers) {
    foreach (int? portNumber in portNumbers)
    {
        var connectionString = CreateConnectionStringBuilder();
        if (portNumber != null)
            connectionString.DataSource += $",{portNumber}";

        try {
            var connection = new SqlConnection(connectionString.ToString());
            connection.Open();

            return connection;
        }
        catch (SqlException) {
            // Attempt failed, log?
        }
    }

    // Connection failed with all given ports...
    return null;
}

CreateConnectionStringBuilder() method reads parameters from configuration and return a ready-to-use SqlConnectionStringBuilder object:

private SqlConnectionStringBuilder CreateConnectionStringBuilder() {
    // ...
}

Your code will then be:

public void EstabilishConnection() {
    // You can specify more than one alternative port
    var connection = TryEstabilishConnection(null, 7777, 58900);
    if (connection == null) {
        // Oops!
    }
}

Note that if you don't specify any port number it will try with default one (1433) but also try an UDP connection to 1434 to ask for an dynamically assigned TCP port (then alternative ports idea is useful only if this mechanism is disabled in SQL Server configuration).

Also note that sometimes connection doesn't work because of network related errors but SQL Server instance is listening on the default port, you may want to use a retry pattern, I just outline code here:

private SqlConnection TryEstabilishConnection(params int?[] portNumbers) {
    foreach (int? portNumber in portNumbers) {
        var connectionString = CreateConnectionStringBuilder();
        if (portNumber != null)
            connectionString.DataSource += $",{portNumber}";

        var connection = TryEstabilishConnection(connectionString.ToString());
        if (connection != null)
            return connection;
    }

    // Connection failed with all given ports...
    return null;
}

private SqlConnection TryEstabilishConnection(string connectionString) {
    for (int i=0; i < RetriesOnError; ++i) {
        try {
            var connection = new SqlConnection(connectionString);
            connection.Open();

            return connection;
        }
        catch (SqlException) when (i < RetriesOnError - 1) {
            Thread.Sleep(DelayBeforeRetry);
        }
    }

    return null;
}

These constants are just indicative, network errors may need longer delays (or higher number of attempts) but you may finally need to wait too long time before you're notified that connection is not available (and if this happens often then you should change your UX to inform user about what's going on):

private const int RetriesOnError = 5;
private const int DelayBeforeRetry = 1000;

Calling code is unchanged. You may use same pattern also to retry during normal operations, see also Know when to retry or fail when calling SQL Server from C#?. In this case I suggest to save the connection that worked to do not try again with default port if it's not available.

Very last note: Failover Partner (should) works only for mirrored databases, AFAIK it's not an alternative Data Source you can use without any other SQL Server configuration (but I'd say it may be a nice-to-have feature).

Setting up a C# method that tries to reconnect to a SQL Server on a different port after first failure

1 Answers1