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 1.9.2.729. This morning I updated to Continua 1.9.2.771. The issue remains.
Here is the top and bottom of the output during Continua startup, with the relevant bits highlighted.
<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?
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:
ALTER DATABASE "ContinuaCI" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE "ContinuaCI" COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE "ContinuaCI" SET MULTI_USER WITH ROLLBACK IMMEDIATE
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.
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.
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.
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.
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.
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);
command.Connection.Open();
object result = command.ExecuteScalar();
command.Connection.Close();
if (result == null || result == DBNull.Value)
return false;
return result.ToString().Equals(value, StringComparison.OrdinalIgnoreCase);
}
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.
