> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Enable Auto-Failover Groups By Server

### More Info:

Ensure that Microsoft Azure SQL database servers are using auto-failover groups in order to enable database replication and automatic failover. A Microsoft Azure SQL failover group is designed to automatically manage replication, connectivity, high availability and failover for a set of SQL databases.

### Risk Level

Medium

### Address

Security

### Compliance Standards

* Cloudanix Best Practice

### Triage and Remediation

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate the misconfiguration "Enable Auto-Failover Groups By Server" for Azure using Azure console, follow the below steps:

        1. Login to Azure portal ([https://portal.azure.com/](https://portal.azure.com/)).
        2. Navigate to the SQL Server for which you want to enable auto-failover group.
        3. Click on the "Failover groups" option in the left-hand side menu.
        4. Click on the "Add" button to create a new failover group.
        5. In the "Basic" tab, provide the below details:
           * Name: Enter a name for the failover group.
           * Subscription: Select the subscription in which the failover group should be created.
           * Resource group: Select the resource group in which the failover group should be created.
           * Region: Select the primary region for the failover group.
           * Primary server: Select the primary SQL server for the failover group.
           * Secondary region: Select the secondary region for the failover group.
           * Secondary server: Select the secondary SQL server for the failover group.
        6. In the "Advanced" tab, select the checkbox "Enable auto-failover groups by server".
        7. Click on the "Review + create" button.
        8. Review the details and click on the "Create" button to create the failover group.

        Once the failover group is created with auto-failover groups enabled by server, the SQL server will be configured to automatically failover to the secondary server in case of any outage or failure in the primary server.

        #
      </Accordion>

      <Accordion title="Using CLI">
        To remediate the misconfiguration "Enable Auto-Failover Groups By Server" in Azure using Azure CLI, follow the below steps:

        Step 1: Open Azure CLI and login to your Azure account using the command below:

        ```
        az login
        ```

        Step 2: Once you are logged in, set the subscription where the server is located using the command below:

        ```
        az account set --subscription <subscription_id>
        ```

        Step 3: Check the current configuration of the server using the command below:

        ```
        az sql server show --name <server_name> --resource-group <resource_group_name>
        ```

        Step 4: If the "auto-failover" property is set to "Disabled", you can enable it using the command below:

        ```
        az sql server update --name <server_name> --resource-group <resource_group_name> --auto-failover-group Enabled
        ```

        Step 5: Once the command is executed successfully, verify the changes using the command below:

        ```
        az sql server show --name <server_name> --resource-group <resource_group_name>
        ```

        Step 6: Verify that the "auto-failover" property is set to "Enabled".

        By following the above steps, you can remediate the misconfiguration "Enable Auto-Failover Groups By Server" in Azure using Azure CLI.
      </Accordion>

      <Accordion title="Using Python">
        To remediate the misconfiguration "Enable Auto-Failover Groups By Server" in Azure using Python, follow the below steps:

        1. Import the required libraries:

        ```
        from azure.common.credentials import ServicePrincipalCredentials
        from azure.mgmt.sql import SqlManagementClient
        from azure.mgmt.resource import ResourceManagementClient
        ```

        2. Authenticate with Azure using Service Principal credentials:

        ```
        TENANT_ID = 'your_tenant_id'
        CLIENT_ID = 'your_client_id'
        CLIENT_SECRET = 'your_client_secret'
        SUBSCRIPTION_ID = 'your_subscription_id'

        credentials = ServicePrincipalCredentials(
            client_id=CLIENT_ID,
            secret=CLIENT_SECRET,
            tenant=TENANT_ID
        )
        ```

        3. Create a resource management client:

        ```
        resource_client = ResourceManagementClient(credentials, SUBSCRIPTION_ID)
        ```

        4. Get the list of SQL servers:

        ```
        sql_client = SqlManagementClient(credentials, SUBSCRIPTION_ID)
        servers = sql_client.servers.list_by_resource_group(resource_group_name)
        ```

        5. For each server, check if Auto-Failover is enabled, and if not, enable it:

        ```
        for server in servers:
            failover_group_policies = sql_client.failover_groups.list_by_server(resource_group_name, server.name)
            if not failover_group_policies:
                failover_group_policy = sql_client.failover_groups.create_or_update(
                    resource_group_name,
                    server.name,
                    {
                        'read_write_endpoint': {
                            'failover_policy': 'Automatic',
                            'failover_with_data_loss_grace_period_minutes': 60
                        },
                        'partner_servers': []
                    }
                )
        ```

        6. Save the script and run it to enable Auto-Failover Groups By Server for all SQL servers in the specified resource group.

        Note: Replace the placeholders "your\_tenant\_id", "your\_client\_id", "your\_client\_secret", "your\_subscription\_id" and "resource\_group\_name" with the appropriate values for your Azure environment.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        # Primary SQL Server (already exists in your env)
        resource "azurerm_mssql_server" "PRIMARY_SQL_SERVER" {
          name                         = "PRIMARY-SQL-SERVER-NAME"          # replace
          resource_group_name          = "PRIMARY-RESOURCE-GROUP-NAME"      # replace
          location                     = "PRIMARY-LOCATION"                 # replace
          version                      = "12.0"
          administrator_login          = "SQL_ADMIN_USERNAME"               # replace
          administrator_login_password = "SQL_ADMIN_PASSWORD"               # replace
        }

        # Secondary SQL Server in a different region
        resource "azurerm_mssql_server" "SECONDARY_SQL_SERVER" {
          name                         = "SECONDARY-SQL-SERVER-NAME"        # replace
          resource_group_name          = "SECONDARY-RESOURCE-GROUP-NAME"    # replace
          location                     = "SECONDARY-LOCATION"               # replace
          version                      = "12.0"
          administrator_login          = "SQL_ADMIN_USERNAME"               # same or different as needed
          administrator_login_password = "SQL_ADMIN_PASSWORD"
        }

        # Auto-failover group for all/some databases on the primary server
        resource "azurerm_sql_failover_group" "PRIMARY_SQL_FAILOVER_GROUP" {
          name                = "FAILOVER-GROUP-NAME"                       # replace
          resource_group_name = azurerm_mssql_server.PRIMARY_SQL_SERVER.resource_group_name
          server_name         = azurerm_mssql_server.PRIMARY_SQL_SERVER.name

          partner_servers {
            id = azurerm_mssql_server.SECONDARY_SQL_SERVER.id
          }

          # Replicate specific databases; add all that must be protected
          databases = [
            "/subscriptions/SUBSCRIPTION_ID/resourceGroups/PRIMARY-RESOURCE-GROUP-NAME/providers/Microsoft.Sql/servers/PRIMARY-SQL-SERVER-NAME/databases/PRIMARY-DATABASE-NAME", # replace
          ]

          read_write_endpoint_failover_policy {
            mode          = "Automatic"
            grace_minutes = 60   # replace with your threshold for automatic failover
          }

          read_only_endpoint_failover_policy {
            mode = "Enabled"
          }
        }
        ```

        This change does not force replacement of the existing SQL servers or databases; it adds a new failover group resource that configures replication and automatic failover between them.

        Verification: `terraform plan` should show `+ create` for `azurerm_sql_failover_group.PRIMARY_SQL_FAILOVER_GROUP` (and for `azurerm_mssql_server.SECONDARY_SQL_SERVER` if it is new), with `read_write_endpoint_failover_policy.mode = "Automatic"` and the expected `databases` and `partner_servers` values.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>