-
Bug
-
Resolution: Unresolved
-
Undefined
-
None
-
None
-
None
-
None
-
rhel-system-roles
-
None
-
False
-
False
-
-
None
-
None
-
None
-
None
-
Unspecified
-
Unspecified
-
Unspecified
-
None
Some AG properties are not configurable, in order to configure them the role needs to re-create an AG.
Currently in the role, such properties include cluster_type, is_contained, and db_failover.
See 'DROP AVAILABIILTY GROUP' in https://github.com/linux-system-roles/mssql/blob/main/templates/configure_ag.j2
Currently, the role does all AG configuration within a single configure_ag.sql script that it builds from a template.
This script would do a check like this:
PRINT 'Verify existing availability group {{ mssql_ha_ag_name }}'
IF EXISTS (
SELECT name, cluster_type_desc
FROM sys.availability_groups
WHERE name = '{{ mssql_ha_ag_name }}'
AND cluster_type_desc != '{{ mssql_ha_ag_cluster_type }}'
)
BEGIN
PRINT 'The existing {{ mssql_ha_ag_name }} availability group has \
incorrect cluster type set, dropping the group to re-create it';
DROP AVAILABILITY GROUP {{ mssql_ha_ag_name }};
PRINT 'The {{ mssql_ha_ag_name }} availability group dropped successfully';
END
But during my tests of is_contained, where I re-created AG to reset the value of is_contained option, I realized that re-creating AG is not that simple.
Just dropping an AG on the primary does not remove AGs on secondaries, and rather breaks secondaries because they cannot connect to the re-created AG because of databases being de-synced.
The role must re-create an AG in a completely safe manner.
I would think of a SQL script that checks if AG must be re-created and returns 1 if yes.
Then based on the returned value it can apply the steps to re-create the AG on all nodes.