AppBeat.io Blog

Uptime and performance monitoring made easy

How to detect if Azure SQL database is in primary or secondary role?

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<string> 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;
}

 

Tip: Azure SQL Database and transient errors

If you are using Azure SQL Database you may occasionally experience following exception (this is example throw by native SqlClient library):

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  This failure occurred while attempting to connect to the routing destination.

What is this? 

Azure system may occasionally quickly shift hardware resources to better load-balance various workloads. During this reconfiguration time span, you may have connectivity issues to Azure SQL Database. If this happens, your client can receive transient error (transient fault).

How do you handle this?

 If you are using .NET Framework 4.6.1 or later you can simply update your connection string, without touching your code.

Simply add following to your connection string:

ConnectRetryCount=VALUE1_IN_SECONDS;ConnectRetryInterval=VALUE2_IN_SECONDS;Connection Timeout=VALUE3_IN_SECONDS;

where VALUE1_IN_SECONDS is number between 0 and 255, VALUE2_IN_SECONDS is number between 1 and 60, VALUE3_IN_SECONDS is number between 0 and 2147483647.

It is recommended that you set Connection Timeout to: ConnectRetryCount * ConnectionRetryInterval (in our case VALUE3_IN_SECONDS = VALUE1_IN_SECONDS * VALUE2_IN_SECONDS). It is not recommended to set this value to 0 because this indicates no limit (connect waits indefinitely).

Links:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-connectivity-issues/