Uploaded image for project: 'RHEL'
  1. RHEL
  2. RHEL-99334

mssql: Fix re-creating AGs

Linking RHIVOS CVEs to...Migration: Automation ...Sync from "Extern...XMLWordPrintable

    • None
    • None
    • rhel-system-roles
    • None
    • False
    • False
    • Hide

      None

      Show
      None
    • 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.

              spetros@redhat.com Sergei Petrosian
              spetros@redhat.com Sergei Petrosian
              Sergei Petrosian Sergei Petrosian
              Daniel Yeisley Daniel Yeisley
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: