tests_configure_ha_cluster.yml

# SPDX-License-Identifier: MIT
---
- name: Verify the role on the primary node
  hosts: all
  vars:
    mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true
    mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true
    mssql_accept_microsoft_sql_server_standard_eula: true
    mssql_password: "p@55w0rD"
    mssql_edition: Evaluation
    mssql_ha_db_name: ExampleDB
    __mssql_test_db_name: "{{ mssql_ha_db_name }}"
    mssql_debug: true
  tasks:
    - name: Verify that by default the role fails on EL < 8
      when:
        - ansible_distribution in ['CentOS', 'RedHat']
        - ansible_distribution_version is version('8', '<')
      block:
        - name: Run the role
          vars:
            mssql_ha_configure: true
          include_role:
            name: linux-system-roles.mssql

        - name: Unreachable task
          fail:
            msg: The above task must fail
      rescue:
        - name: Assert that the role failed with EL < 8 not supported
          assert:
            that: >-
              'mssql_ha_configure=true does not support running against EL 7
              hosts' in ansible_failed_result.msg

        - name: End EL < 8 host
          meta: end_host

    - name: Set the mssql_ha_replica_type fact to appear in hostvars
      set_fact:
        mssql_ha_replica_type: primary
      when:
        - ansible_play_hosts_all | length == 1
        - mssql_ha_replica_type is not defined

    - name: Set up test environment for the ha_cluster role
      include_role:
        name: fedora.linux_system_roles.ha_cluster
        tasks_from: ../../../tests/ha_cluster/tasks/setup_test.yml

    - name: Configure SQL Server and create an ExampleDB database on primary
      vars:
        mssql_input_sql_file: create_example_db.j2
      include_role:
        name: linux-system-roles.mssql
      when: mssql_ha_replica_type == 'primary'

    - name: Run on all hosts to configure HA cluster
      vars:
        mssql_ha_configure: true
        mssql_ha_firewall_configure: true
        mssql_ha_listener_port: 5022
        mssql_ha_cert_name: mssql_cert
        mssql_ha_master_key_password: "p@55w0rD1"
        mssql_ha_private_key_password: "p@55w0rD2"
        mssql_ha_reset_cert: false
        mssql_ha_endpoint_name: hadr_endpoint
        mssql_ha_ag_name: ag1
        mssql_ha_db_backup_path: /var/opt/mssql/data/{{ mssql_ha_db_name }}.bak
        mssql_ha_login: pacemakerLogin
        mssql_ha_login_password: "p@55w0rD3"
        mssql_ha_cluster_run_role: true
        mssql_ha_cluster_password: "p@55w0rD4"
        mssql_ha_cluster_virtual_ip: 192.168.122.10
        mssql_ha_stonith_resources:
          id: myapc
          agent: stonith:fence_apc_snmp
          instance_attrs:
            - attrs:
                - name: ipaddr
                  value: apc-switch.example.com
                - name: pcmk_host_map
                  value: rhel8-node1.example.com:1;rhel8-node2.example.com:2
                - name: login
                  value: apc
                - name: passwd
                  value: apc

      include_role:
        name: linux-system-roles.mssql

# This play tests if templates configure systems correctly
# Because CI runs on a single node, the following templates cannot be tested:
# remove_from_ag.j2,
# join_to_ag.j2,
# removing and adding replicas in configure_ag.j2
- name: Test templates
  hosts: all
  vars:
    mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true
    mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true
    mssql_accept_microsoft_sql_server_standard_eula: true
    mssql_password: "p@55w0rD"
    mssql_edition: Evaluation
    mssql_ha_db_name: ExampleDB
    __mssql_test_db_name: "{{ mssql_ha_db_name }}"
    mssql_debug: true
    mssql_ha_configure: true
    mssql_ha_firewall_configure: true
    mssql_ha_listener_port: 5022
    mssql_ha_cert_name: mssql_cert
    mssql_ha_master_key_password: "p@55w0rD1"
    mssql_ha_private_key_password: "p@55w0rD2"
    mssql_ha_reset_cert: false
    mssql_ha_endpoint_name: hadr_endpoint
    mssql_ha_ag_name: ag1
    mssql_ha_db_backup_path: /var/opt/mssql/data/{{ mssql_ha_db_name }}.bak
    mssql_ha_login: pacemakerLogin
    mssql_ha_login_password: "p@55w0rD3"
    mssql_ha_cluster_run_role: false
  tasks:
    - name: Ensure ansible_facts and variables used by role
      include_vars: ../vars/main.yml

    # enable_alwayson.j2 template test
    - name: Enable AlwaysOn event session when it's enabled
      vars:
        __mssql_input_sql_file: enable_alwayson.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Assert that enabling is skipped
      assert:
        that: >-
          "AlwaysOn Health events already enabled, skipping"
          in __mssql_sqlcmd_input_file.stdout

    - name: Disable AlwaysOn event session
      vars:
        __mssql_input_sql_file: disable_alwayson.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Enable AlwaysOn event session
      vars:
        __mssql_input_sql_file: enable_alwayson.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Assert that the template reported changed state
      assert:
        that: >-
          "AlwaysOn Health events enabled successfully"
          in __mssql_sqlcmd_input_file.stdout

    # configure_endpoint.j2 template test
    - name: Create endpoint when it exists
      vars:
        __mssql_input_sql_file: configure_endpoint.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Assert expected messages
      assert:
        that:
          - >-
            "Verifying the existing endpoint {{ mssql_ha_endpoint_name }}"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The LISTENER_PORT setting for the {{ mssql_ha_endpoint_name }}
            endpoint is already set to {{ mssql_ha_listener_port }}, skipping"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The ROLE setting for the {{ mssql_ha_endpoint_name }} endpoint is
            already set to {{ __mssql_ha_endpoint_role }}, skipping"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The certificate for the {{ mssql_ha_endpoint_name }}
            endpoint is already set to {{ mssql_ha_cert_name }}, skipping"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The ENCRYPTION setting for the {{ mssql_ha_endpoint_name }}
            endpoint is already set to AES, skipping"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "Endpoint {{ mssql_ha_endpoint_name }} is already started, skipping"
            in __mssql_sqlcmd_input_file.stdout

    - name: Alter endpoint to test how template handles incorrect endpoint
      vars:
        __mssql_input_sql_file: alter_endpoint.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Configure endpoint correctly
      vars:
        __mssql_input_sql_file: configure_endpoint.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Assert expected messages
      assert:
        that:
          - >-
            "Verifying the existing endpoint {{ mssql_ha_endpoint_name }}"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The LISTENER_PORT setting for the {{ mssql_ha_endpoint_name }}
            endpoint updated to {{ mssql_ha_listener_port }} successfully"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The ROLE setting for the {{ mssql_ha_endpoint_name }}
            endpoint updated to {{ __mssql_ha_endpoint_role }} successfully"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The certificate for the {{ mssql_ha_endpoint_name }}
            endpoint updated to {{ mssql_ha_cert_name }} successfully"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "The ENCRYPTION setting for the {{ mssql_ha_endpoint_name }}
            endpoint updated to AES successfully"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "Endpoint {{ mssql_ha_endpoint_name }} started successfully"
            in __mssql_sqlcmd_input_file.stdout

    - name: Drop the test_cert certificate
      vars:
        __mssql_input_sql_file: drop_cert.j2
        mssql_ha_cert_name: test_cert
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    # create_master_key_encryption.j2 template test
    - name: Create a master key when it exists
      vars:
        __mssql_input_sql_file: create_master_key_encryption.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Assert that creating is skipped
      assert:
        that: >-
          "The provided master key password is correct"
          in __mssql_sqlcmd_input_file.stdout

    - name: Verify creating master key with incorrect password
      block:
        - name: Try creating master key with incorrect password
          vars:
            __mssql_input_sql_file: create_master_key_encryption.j2
            mssql_ha_master_key_password: "p@55w0rD11"
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: This task shouldn't execute
          fail:
      rescue:
        - name: Assert the incorrect password error message
          assert:
            that: >-
              "You provided an incorrect master key password"
              in __mssql_sqlcmd_input_file.stdout

    - name: Drop endpoint
      vars:
        __mssql_input_sql_file: create_master_key_encryption.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Remove certificate from SQL Server to verify re-creating master key
      vars:
        __mssql_input_sql_file: drop_cert.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Remove certificate and private key
      file:
        path: "{{ item }}"
        state: absent
      loop:
        - "{{ __mssql_ha_cert_dest }}"
        - "{{ __mssql_ha_private_key_dest }}"

    - name: >-
        Verify creating master key with incorrect password and
        mssql_ha_reset_cert set to true
      vars:
        __mssql_input_sql_file: create_master_key_encryption.j2
        mssql_ha_master_key_password: "p@55w0rD11"
        mssql_ha_reset_cert: true
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Assert the expected error message
      assert:
        that:
          - >-
            "dropping master key to re-create it"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "Master key dropped successfully"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "Master key created successfully"
            in __mssql_sqlcmd_input_file.stdout

    # # restore_cert.j2 template test
    # - name: Create a certificate when it exists on secondary
    #   when: mssql_ha_replica_type in ['synchronous', 'witness']
    #   block:
    #     - name: Input restore_cert.j2
    #       vars:
    #         __mssql_input_sql_file: restore_cert.j2
    #       include_role:
    #         name: linux-system-roles.mssql
    #         tasks_from: input_sql_file.yml

    #     - name: Assert the already exists message
    #       assert:
    #         that: >-
    #           "Certificate {{ mssql_ha_cert_name }} already exists, skipping"
    #           in __mssql_sqlcmd_input_file.stdout

    # create_and_back_up_cert.j2 template test
    - name: Create and back up an existing and backed up certificate
      when: mssql_ha_replica_type == 'primary'
      block:
        - name: Input create_and_back_up_cert.j2 to create cert
          vars:
            __mssql_input_sql_file: create_and_back_up_cert.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Assert created successfully messages
          assert:
            that:
              - >-
                "Certificate {{ mssql_ha_cert_name }} created successfully"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "{{ __mssql_ha_cert_dest }} and
                {{ __mssql_ha_private_key_dest }} exported successfully"
                in __mssql_sqlcmd_input_file.stdout

        - name: Input create_and_back_up_cert.j2 when cert exists
          vars:
            __mssql_input_sql_file: create_and_back_up_cert.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Assert task skipping messages
          assert:
            that:
              - >-
                "Certificate {{ mssql_ha_cert_name }} already exists, skipping"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "{{ __mssql_ha_cert_dest }} and
                {{ __mssql_ha_private_key_dest }} already exist, skipping"
                in __mssql_sqlcmd_input_file.stdout

        - name: Back up certificate and private key
          copy:
            remote_src: true
            src: "{{ item }}"
            dest: /tmp/{{ item | basename }}
          loop:
            - "{{ __mssql_ha_cert_dest }}"
            - "{{ __mssql_ha_private_key_dest }}"

        - name: Remove private key
          file:
            path: "{{ __mssql_ha_private_key_dest }}"
            state: absent

        - name: Input create_and_back_up_cert.j2
          vars:
            __mssql_input_sql_file: create_and_back_up_cert.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Assert expected error messages
          assert:
            that:
              - >-
                "Certificate {{ mssql_ha_cert_name }} already exists, skipping"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "{{ __mssql_ha_private_key_dest }} does not exist while
                {{ __mssql_ha_cert_dest }} exists."
                in __mssql_sqlcmd_input_file.stdout

        - name: Remove certificate
          file:
            path: "{{ __mssql_ha_cert_dest }}"
            state: absent

        - name: Return private key
          copy:
            remote_src: true
            src: /tmp/{{ __mssql_ha_private_key_dest | basename }}
            dest: "{{ __mssql_ha_private_key_dest }}"

        - name: Input create_and_back_up_cert.j2
          vars:
            __mssql_input_sql_file: create_and_back_up_cert.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Assert expected error messages
          assert:
            that:
              - >-
                "Certificate {{ mssql_ha_cert_name }} already exists, skipping"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "{{ __mssql_ha_cert_dest }} does not exist while
                {{ __mssql_ha_private_key_dest }} exists."
                in __mssql_sqlcmd_input_file.stdout

        - name: Return certificate
          copy:
            remote_src: true
            src: /tmp/{{ __mssql_ha_cert_dest | basename }}
            dest: "{{ __mssql_ha_cert_dest }}"

        - name: Drop certificate from SQL Server
          vars:
            __mssql_input_sql_file: drop_cert.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Create cert when cert files exist and SQL cert does not
          block:
            - name: Input create_and_back_up_cert.j2
              vars:
                __mssql_input_sql_file: create_and_back_up_cert.j2
              include_role:
                name: linux-system-roles.mssql
                tasks_from: input_sql_file.yml

            - name: Unreachable task
              fail:
                msg: The above task must fail
          rescue:
            - name: Assert expected error messages
              assert:
                that:
                  - >-
                    "Certificate {{ mssql_ha_cert_name }} does not exist in SQL
                    Server, however, {{ __mssql_ha_cert_dest }} and/or
                    {{ __mssql_ha_private_key_dest }} files do exist."
                    in __mssql_sqlcmd_input_file.stdout

        - name: Remove certificate and private key
          file:
            path: "{{ item }}"
            state: absent
          loop:
            - "{{ __mssql_ha_cert_dest }}"
            - "{{ __mssql_ha_private_key_dest }}"

        - name: Ensure that certificates exist
          vars:
            __mssql_input_sql_file: create_and_back_up_cert.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Assert expected messages
          assert:
            that: >-
              "Certificate {{ mssql_ha_cert_name }} created successfully"
              in __mssql_sqlcmd_input_file.stdout

    # create_ha_login.j2 template test
    - name: Create a {{ mssql_ha_login }} login when it already exists
      vars:
        __mssql_input_sql_file: create_ha_login.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Assert task skipping messages
      assert:
        that:
          - >-
            "A {{ mssql_ha_login }} login already exists, skipping"
            in __mssql_sqlcmd_input_file.stdout
          - >-
            "{{ mssql_ha_login }} is a member of sysadmin role, skipping"
            in __mssql_sqlcmd_input_file.stdout

    # replicate_db.j2 template test
    - name: Replicate database what it is already replicated
      when: mssql_ha_replica_type == 'primary'
      block:
        - name: Input replicate_db.j2
          vars:
            __mssql_input_sql_file: replicate_db.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Assert task skipping messages
          assert:
            that:
              - >-
                "RECOVERY FULL on the {{ mssql_ha_db_name }} database is set,
                skipping" in __mssql_sqlcmd_input_file.stdout
              - >-
                "The {{ mssql_ha_db_name }} database is already backed up,
                skipping" in __mssql_sqlcmd_input_file.stdout
              - >-
                "database is already added to the {{ mssql_ha_ag_name }}
                availability group, skipping"
                in __mssql_sqlcmd_input_file.stdout

    # configure_ag.yml template test
    - name: Ensure endpoint exists
      vars:
        __mssql_input_sql_file: configure_endpoint.j2
      include_role:
        name: linux-system-roles.mssql
        tasks_from: input_sql_file.yml

    - name: Test configure_ag.yml on primary
      when: mssql_ha_replica_type == 'primary'
      block:
        - name: Input configure_ag.yml when AG exists
          vars:
            __mssql_input_sql_file: configure_ag.j2
          include_role:
            name: linux-system-roles.mssql
            tasks_from: input_sql_file.yml

        - name: Assert task skipping messages
          assert:
            that:
              - >-
                "DB_FAILOVER = ON is already set, skipping"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "The ENDPOINT_URL setting on this {{ mssql_ha_replica_type }}
                replica is already set correctly, skipping"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "The FAILOVER_MODE setting on this {{ mssql_ha_replica_type }}
                replica is already set correctly, skipping"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "The SEEDING_MODE setting on this {{ mssql_ha_replica_type }}
                replica is already set correctly, skipping"
                in __mssql_sqlcmd_input_file.stdout
              - >-
                "The SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) setting on this
                {{ mssql_ha_replica_type }} replica is already set correctly"
                in __mssql_sqlcmd_input_file.stdout

        # Using mssql_ha_ag_name: ag2 because running tasks against ag1 fails
        # with "Replica not Primary"
        - name: Verify configuration of a new ag2
          vars:
            __mssql_input_sql_file: configure_ag_cluster_type_none.j2
            mssql_ha_ag_name: ag2
          block:
            - name: Configure AG with CLUSTER_TYPE = NONE
              include_role:
                name: linux-system-roles.mssql
                tasks_from: input_sql_file.yml

            - name: Input configure_ag.yml with CLUSTER_TYPE = NONE
              vars:
                __mssql_input_sql_file: configure_ag.j2
              include_role:
                name: linux-system-roles.mssql
                tasks_from: input_sql_file.yml

            - name: Assert task skipping messages
              assert:
                that:
                  - >-
                    "The existing {{ mssql_ha_ag_name }} availability group has
                    incorrect cluster type set, dropping the group to re-create
                    it"
                    in __mssql_sqlcmd_input_file.stdout
                  - >-
                    "The {{ mssql_ha_ag_name }} availability group dropped
                    successfully"
                    in __mssql_sqlcmd_input_file.stdout
                  - >-
                    "The {{ mssql_ha_ag_name }} availability group created
                    successfully"
                    in __mssql_sqlcmd_input_file.stdout

            - name: Alter AG
              vars:
                __mssql_input_sql_file: alter_ag.j2
              include_role:
                name: linux-system-roles.mssql
                tasks_from: input_sql_file.yml

            - name: Input configure_ag.yml to configure ag properly
              vars:
                __mssql_input_sql_file: configure_ag.j2
              include_role:
                name: linux-system-roles.mssql
                tasks_from: input_sql_file.yml

            - name: Assert task skipping messages
              assert:
                that:
                  - >-
                    "Verifying the existing availability group
                    {{ mssql_ha_ag_name }}"
                    in __mssql_sqlcmd_input_file.stdout
                  - >-
                    "The ENDPOINT_URL setting on this
                    {{ mssql_ha_replica_type }} replica configured successfully"
                    in __mssql_sqlcmd_input_file.stdout
                  - >-
                    "The FAILOVER_MODE setting on this
                    {{ mssql_ha_replica_type }} replica is already set
                    correctly, skipping"
                    in __mssql_sqlcmd_input_file.stdout
                  - >-
                    "The SEEDING_MODE setting on this
                    {{ mssql_ha_replica_type }} replica configured successfully"
                    in __mssql_sqlcmd_input_file.stdout
                  - >-
                    "The SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) setting on
                    this {{ mssql_ha_replica_type }} replica configured
                    successfully"
                    in __mssql_sqlcmd_input_file.stdout