configure_ag.j2

IF NOT EXISTS (
  SELECT * FROM sys.availability_groups
  WHERE name = '{{ mssql_ha_ag_name }}' AND
        cluster_type_desc = 'external'
)
BEGIN
  PRINT 'Creating the {{ mssql_ha_ag_name }} availability group';
  CREATE AVAILABILITY GROUP {{ mssql_ha_ag_name }}
{% if ansible_os_family == 'RedHat' and
  ansible_distribution_version | float < 8.3 %}
    WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
{% else %}
    WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL, WRITE_LEASE_VALIDITY=20)
{% endif %}
    FOR REPLICA ON
      N'{{ ansible_hostname }}' WITH (
        ENDPOINT_URL = N'tcp://{{ ansible_fqdn }}:{{ mssql_ha_listener_port }}',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC
{% for item in ansible_play_hosts %}
{%   if hostvars[item]['mssql_ha_replica_type'] == 'synchronous' %}
      ),
      N'{{ hostvars[item]['ansible_hostname'] }}' WITH (
        ENDPOINT_URL = N'tcp://{{
          hostvars[item]['ansible_fqdn'] }}:{{ mssql_ha_listener_port }}',
        AVAILABILITY_MODE = {{ hostvars[item]['__mssql_ha_availability_mode'] }},
        FAILOVER_MODE = {{ hostvars[item]['__mssql_ha_failover_mode'] }},
        SEEDING_MODE = {{ hostvars[item]['__mssql_ha_seeding_mode'] }}
{%   elif hostvars[item]['mssql_ha_replica_type'] == 'witness' %}
      ),
      N'{{ hostvars[item]['ansible_hostname'] }}' WITH (
        ENDPOINT_URL = N'tcp://{{
          hostvars[item]['ansible_fqdn'] }}:{{ mssql_ha_listener_port }}',
        AVAILABILITY_MODE = {{ hostvars[item]['__mssql_ha_availability_mode'] }}
{%   endif %}
{% endfor %}
      );
  PRINT 'The {{ mssql_ha_ag_name }} availability group created successfully';
END
ELSE
BEGIN
  PRINT 'Verifying the existing availability group {{ mssql_ha_ag_name }}'
  IF EXISTS (
    SELECT * FROM sys.availability_groups
    WHERE name = '{{ mssql_ha_ag_name }}' AND
          cluster_type_desc != 'external'
  )
  BEGIN
    PRINT 'The existing {{ mssql_ha_ag_name }} availability group has \
incorrect cluster type set, dropping the groupt to re-create it';
    DROP AVAILABILITY GROUP ag1;
    PRINT 'The {{ mssql_ha_ag_name }} availability group dropped successfully';
  END
  IF NOT EXISTS (
    SELECT * FROM sys.availability_groups
    WHERE name = '{{ mssql_ha_ag_name }}' AND
          db_failover = 1
  )
  BEGIN
    ALTER AVAILABILITY GROUP ag1 SET (DB_FAILOVER = ON)
    PRINT 'Set DB_FAILOVER to ON succesfully'
  END
  ELSE
  BEGIN
    PRINT 'DB_FAILOVER = ON is already set, skipping'
  END
  PRINT 'Verifying replicas'
{% for item in ansible_play_hosts %}
{%   if hostvars[item]['mssql_ha_replica_type'] != 'absent' %}
  IF EXISTS (
    SELECT * FROM sys.availability_replicas
    WHERE replica_server_name = '{{ hostvars[item]['ansible_hostname'] }}' AND
          availability_mode_desc !=
            '{{ hostvars[item]['__mssql_ha_availability_mode'] }}'
  )
  BEGIN
    PRINT '{{ hostvars[item]['ansible_hostname'] }}: The availability mode \
of this availability replica does not match the required availability mode, \
removing this replica re-create it';
    ALTER AVAILABILITY GROUP {{ mssql_ha_ag_name }} REMOVE REPLICA ON
      N'{{ hostvars[item]['ansible_hostname'] }}'
    PRINT '{{ hostvars[item]['ansible_hostname'] }}: Removed successfully'
  END
  IF NOT EXISTS (
    SELECT * FROM sys.availability_replicas
    WHERE replica_server_name = '{{ hostvars[item]['ansible_hostname'] }}'
  )
  BEGIN
    PRINT 'Adding the {{ hostvars[item]['ansible_hostname'] }} \
{{ hostvars[item]['mssql_ha_replica_type'] }} replica';
{%       if hostvars[item]['mssql_ha_replica_type'] == 'synchronous' %}
    ALTER AVAILABILITY GROUP {{ mssql_ha_ag_name }} ADD REPLICA ON
      N'{{ hostvars[item]['ansible_hostname'] }}' WITH (
        ENDPOINT_URL = N'tcp://{{
          hostvars[item]['ansible_fqdn'] }}:{{ mssql_ha_listener_port }}',
        AVAILABILITY_MODE = {{ hostvars[item]['__mssql_ha_availability_mode'] }},
        FAILOVER_MODE = {{ hostvars[item]['__mssql_ha_failover_mode'] }},
        SEEDING_MODE = {{ hostvars[item]['__mssql_ha_seeding_mode'] }}
      );
{%       elif hostvars[item]['mssql_ha_replica_type'] == 'witness' %}
    ALTER AVAILABILITY GROUP {{ mssql_ha_ag_name }} ADD REPLICA ON
      N'{{ hostvars[item]['ansible_hostname'] }}' WITH (
        ENDPOINT_URL = N'tcp://{{ hostvars[item]['ansible_fqdn'] }}:{{ mssql_ha_listener_port }}',
        AVAILABILITY_MODE = {{ hostvars[item]['__mssql_ha_availability_mode'] }}
      );
{%       endif %}
    PRINT 'The {{ hostvars[item]['ansible_hostname'] }} \
{{ hostvars[item]['mssql_ha_replica_type'] }} replica added successfully';
  END
{%     if hostvars[item]['mssql_ha_replica_type'] != 'primary' %}
  ELSE
  BEGIN
    PRINT 'Verifying the existing replica {{ item }}';
{%     if (hostvars[item]['mssql_ha_replica_type'] == 'primary') or
          (hostvars[item]['mssql_ha_replica_type'] == 'synchronous') %}
{%       set ag_replica_settings = ({
"endpoint_url":{
  "sql_setting_name":"ENDPOINT_URL",
  "sys_setting_name":"endpoint_url",
  "setting_value":"N'tcp://" + hostvars[item]['ansible_fqdn'] + ":" +
    mssql_ha_listener_port | string + "'"},
"failover_mode":{
  "sql_setting_name":"FAILOVER_MODE",
  "sys_setting_name":"failover_mode_desc",
  "setting_value":hostvars[item]['__mssql_ha_failover_mode']},
"seeding_mode":{
  "sql_setting_name":"SEEDING_MODE",
  "sys_setting_name":"seeding_mode_desc",
  "setting_value":hostvars[item]['__mssql_ha_seeding_mode']}
}) %}
{%     elif hostvars[item]['mssql_ha_replica_type'] == 'witness' %}
{%       set ag_replica_settings = ({
"endpoint_url":{
  "sql_setting_name":"ENDPOINT_URL",
  "sys_setting_name":"endpoint_url",
  "setting_value":"N'tcp://" + hostvars[item]['ansible_fqdn'] + ":" +
    mssql_ha_listener_port | string + "'"}
         })
%}
{%     endif %}
{%     for key, value in ag_replica_settings.items() %}
    IF NOT EXISTS (
      SELECT * FROM sys.availability_replicas
      WHERE replica_server_name = N'{{ hostvars[item]['ansible_hostname'] }}'
      AND
{%       if key == 'endpoint_url' %}
            {{ value.sys_setting_name }} = {{ value.setting_value }}
{%       else %}
            {{ value.sys_setting_name }} = '{{ value.setting_value }}'
{%       endif %}
    )
    BEGIN
      ALTER AVAILABILITY GROUP {{ mssql_ha_ag_name }} MODIFY REPLICA ON
        N'{{ hostvars[item]['ansible_hostname'] }}' WITH (
          {{ value.sql_setting_name }} = {{ value.setting_value }}
        );
      PRINT '{{ hostvars[item]['ansible_hostname'] }}: \
The {{ value.sql_setting_name }} setting on this \
{{ hostvars[item]['mssql_ha_replica_type'] }} replica configured successfully';
    END
    ELSE
    BEGIN
      PRINT '{{ hostvars[item]['ansible_hostname'] }}: \
The {{ value.sql_setting_name }} setting on this \
{{ hostvars[item]['mssql_ha_replica_type'] }} replica is already set \
correctly, skipping';
    END
{%     endfor %}
  END
{%     endif %}
{%   elif hostvars[item]['mssql_ha_replica_type'] == 'absent' %}
  IF NOT EXISTS (
    SELECT * FROM sys.availability_replicas
    WHERE replica_server_name = '{{ hostvars[item]['ansible_hostname'] }}'
  )
  BEGIN
    PRINT '{{ item }}: this replica is already removed, skipping';
  END
  ELSE
  BEGIN
    PRINT '{{ item }}: Removing this replica';
    ALTER AVAILABILITY GROUP {{ mssql_ha_ag_name }}
      REMOVE REPLICA ON N'{{ hostvars[item]['ansible_hostname'] }}';
    PRINT '{{ item }}: This replica is removed successfully';
  END
{%   endif %}
{% endfor %}
END

-- It is not possible to grant permissions fully idempotently
ALTER AVAILABILITY GROUP {{ mssql_ha_ag_name }} GRANT CREATE ANY DATABASE;