Error during startup checking collation on database in availability group


We have recently deployed our production database servers (MS SQL Server 15) to AWS, configured in an availability group. This is working very well, and all our existing .NET and Delphi projects work with it without problem.

Continua, however, is having problems with this database configuration during startup when it tries to check the database collation. This occurred with Continua This morning I updated to Continua The issue remains.

Here is the top and bottom of the output during Continua startup, with the relevant bits highlighted.

The connection string being used is:

<property name="connection.connection_string">Server=MSSQLPROD;Database=ContinuaCI;Trusted_Connection=True;Min Pool Size=10;Max Pool Size=256;Pooling=True;MultiSubnetFailover=True</property>

The work around has been to change the connection string to a direct connection.

<property name="connection.connection_string">Server=CAA-PDB-SQL101;Database=ContinuaCI;Trusted_Connection=True;Min Pool Size=10;Max Pool Size=256;Pooling=True;MultiSubnetFailover=False</property>

Obviously the work around isn’t ideal as it’s now using a machine name directly instead of the name of the availability group.

Can you please investigate this issue and work out a fix?

Bruce Palmer

Hi Bruce,

The code which is giving the warning should only run if the database collation is not set to “SQL_Latin1_General_CP1_CI_AS”. We need to set the collation to a specific value to ensure that sorting and comparison is consistent throughout the application.

It runs the following query to check the current collation: SELECT collation_name FROM sys.databases WHERE name = 'ContinuaCI'. If the result is anything other than “SQL_Latin1_General_CP1_CI_AS” then it runs the following statements:


It appears that the first statement in this set fails to run when in high availability mode. We’re not currently aware of any alternative way to set the collation, but are investigating options.

The only option at the moment is to set the collation manually by running the above statements on all databases in the availability group.

Once the collation is set correctly on all database, this code should not longer run when Continua CI starts up and the warning should not be shown.

This warning is however currently handled and the start-up continues to run the database migration ignoring this failure. There is a second error occurring which prevents the server from starting up and it is not obvious from start-up messages what is causing this. Can you restart the server with debug logging enabled and send a copy of the debug log in a direct message by clicking on my avatar? This should allow us to understand which statement is failing.

Hi Dave,

Log file sent as requested.

I’ll now follow up the collation issue with our database admin.

Hi Dave,

I’ve received confirmation from our database admin that the collation of our ContinuaCI database is set to SQL_Latin1_General_CP1_CI_AS.

The A/G is for availability, and the database is replicated (for failover), so all instances of the ContinuaCI database have this collation.

Perhaps the collation detection isn’t working?


UPDATE: I’ve looked at the log file myself and it’s evident that the collation detection is failing, and the assumption is being made that it’s incorrect. So the collation error seems to be a symptom of a deeper issue.

Hi Bruce,

Thanks for sending the log file.

This shows that the error "The target database, 'ContinuaCI', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online." is being raised when connecting to the database, before any SQL query is run. This implies an issue with access to some of the databases in the availability group.

Can you check that the secondary database is readable - see Always-On Error? Also check that none of the databases has been suspended - see how to resume an availability database.

Hi Dave,

Thanks. I’ve passed that information on to our database admin. Will let you know when I get a reply.

I posted before seeing your last reply. It appears that the issue is not really related to the collation. The collation query just happens to be the first connection to the database.

1 Like

Hi Dave,

From our database admin:

No, the secondary Server is not a R/O replica, none of the DB’s is suspended.
There is no way that a client could connect to the A/G replicas as they are invisible to the client.
Could they supply the script or code they are using to detect the collation and we can potentially provide more detail.

Hi Bruce,

You have already tested the SQL that we use to detect the collation above, but this is not really relevant.

The error occurs when we make a standard ADO connection using the connection string provided in the configuration file. Can you try creating a new test.udl file on the server, double-click it, enter the connection details and then test the connection? See Creating a data link (UDL) file and testing the connectivity to a Microsoft SQL server.

If the connection works, check if you get an error running the following in PowerShell?

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 'Server=MSSQLPROD;Database=ContinuaCI;Trusted_Connection=True;Min Pool Size=10;Max Pool Size=256;Pooling=True;MultiSubnetFailover=True'
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT collation_name FROM sys.databases WHERE name = 'ContinuaCI'"
$SqlCmd.Connection = $SqlConnection
$result = $SqlCmd.ExecuteScalar()
Write-Output $result.ToString()

Hi Martin,

It’s using the standard MS SQL client as in the PowerShell script and it uses the connection string without modification. This is the C# code we used in this instance.

private static bool CheckSqlValue(string connectionString, string sql, string value)
	using var connection = new SqlConnection(connectionString);
	using var command = new SqlCommand(sql, connection);

	object result = command.ExecuteScalar();
	if (result == null || result == DBNull.Value)
		return false;

	return result.ToString().Equals(value, StringComparison.OrdinalIgnoreCase);

Is MSSQLPROD the name of the availability group listener? Surely it should only connect to the secondary database server if there is a failure?

Another thing to look at is the READ_WRITE_ROUTING_URL. Looks like this needs to be set to route read/write connections to the primary replica. See Redirect read/write connections to primary replica - SQL Server Always On | Microsoft Docs

Hi Dave,

The cause of the issue was that a HOST file entry had been setup which was pointing to the secondary node. This was done in the past as a workaround for scripts using ODBC connections which were not A/G friendly but which have since been replaced.

The problem has been fixed and Continua is now working fine using the proper connection string.

Thank you for the time you put into this. In the process of helping to diagnose the issue you have imparted a number of useful articles which I will keep bookmarked for future reference.

Bruce Palmer

1 Like