If you want to have highly available Azure SQL Database it is highly recommended that you configure Active Geo-Replication (this feature is available for all databases in all service tiers).

Active Geo-Replication consists of two (or more) databases in different regions, where one database is primary (read + write operations) and all other databases are secondaries (allowed only read operations). Secondary databases are automatically synced with changes in your primary database.

Failover event can be triggered manually or if disastrous event happens in one of Azure datacenters. In this case your primary database goes into secondary mode and one of your secondaries becomes new primary database. If you are accessing your databases directly by using connection string, you will have to update it to reflect primary / secondary database switch.

Of course, we want to update our connection string automatically and we will show you two C# code snippets how to detect which database is currently primary (in our examples we operate with one primary and one secondary database).

Example 1⌗

Run special query on each database to determine if it is in primary or secondary role:

public static async Task GetPrimaryConnectionString() { bool? isPrimary = await IsPrimaryAsync(YOUR_CONNECTION_STRING_DB1); if (isPrimary == true) { return YOUR_CONNECTION_STRING_DB1; }

isPrimary = await IsPrimaryAsync(YOUR_CONNECTION_STRING_DB2);
if (isPrimary == true)
{
    return YOUR_CONNECTION_STRING_DB2;
}

//could not determine which database is primary
return null;

}

private static async Task<bool?> IsPrimaryAsync(string connectionString) { try { using (var conn = new SqlConnection(connectionString)) { if (conn.State != System.Data.ConnectionState.Open) { await conn.OpenAsync(); }

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "SELECT role FROM sys.dm_geo_replication_link_status";
            var res = await cmd.ExecuteScalarAsync();
            return Convert.ToInt32(res) == 0;
        }
    }
}
catch (Exception ex)
{
    //handle exception
    return null;
}

}

Example 2⌗

If you try to update database which is currently in secondary role you will get following SqlException: Failed to update database “YOUR_DB_NAME” because the database is read-only.

You can use this information to update your connection string:

try { //your code } catch (SqlException sqlException) { if (IsReadOnlyDatabase(sqlException) == true) { //current connection string probably points to secondary database (sqlException.Server) //update your connection string to your other database } }

private static bool? IsReadOnlyDatabase(SqlException se) { if (se?.Errors == null) return null;

foreach (SqlError error in se.Errors)
{
    if (error.Number == 3906)
    {
        return true;
    }
}

return false;

}