Enable Auto-Failover Groups

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features

Knowledge Base Microsoft Azure Sql Enable Auto-Failover Groups

Risk level: Medium (should be achieved)

Rule ID: Sql-014

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.

This rule resolution is part of the Cloud Conformity Security & Compliance tool for Azure

Reliability

When auto-failover groups are used for your Azure SQL databases, any outage that impacts one or more SQL databases within the group results in an automatic failover. In addition, auto-failover groups provide read-write and read-only listener endpoints that remain unchanged during failovers. Whether you use manual or automatic failover activation, the failover process switches all secondary databases in the group to primary databases. After the failover is completed, the DNS record is automatically updated to redirect the endpoints to the new Azure region.

Audit

To determine if your Azure SQL database servers are configured to use auto-failover groups, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

04 Click on the name of the SQL database server that you want to examine.

05 On the navigation panel, under Settings, select Failover groups to access the list with the auto-failover groups available for the selected database server.

06 On the Failover groups page, check for any auto-failover that includes the selected server. If there are no failover groups listed on the page, instead the following status is displayed: "You have no group created", the selected Microsoft Azure SQL database server is not configured to use auto-failover groups.

07 Repeat steps no. 4 – 6 for each SQL database available within the current Azure subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI

01 Run sql server list command (Windows/macOS/Linux) using custom query filters to list the identifier for each SQL server provisioned within the current Azure subscription:

az sql server list --query '[*].id'

02 The command output should return the requested SQL server identifiers:

[
"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-production-server",
"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-private-server"
]

03 Run sql failover-group list command (Windows/macOS/Linux) using the ID of the Azure SQL server that you want to examine as identifier parameter to list the auto-failover group(s) created for the selected SQL server:

az sql failover-group list
  --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-production-server"

04 The command output should return the configuration metadata for the requested failover group(s):

[]

If the sql failover-group list command output returns an empty array, as shown in the example above, the selected Microsoft Azure SQL database server is not included within an auto-failover group, therefore the Azure resource does not have database replication and automatic failover enabled.

05 Repeat step no. 3 and 4 for each Azure SQL server provisioned in the selected subscription.

06 Repeat steps no. 1 – 5 for each subscription available within your Microsoft Azure cloud account.

Remediation / Resolution

To configure your Microsoft Azure SQL database servers to use auto-failover groups, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

04 Click on the name of the SQL database server that you want to reconfigure (see Audit section part I to identify the right resource).

05 On the navigation panel, under Settings, select Failover groups, then click Add group to initiate the auto-failover group setup process.

06 On the Failover group panel, perform the following:

In the Failover group name box, provide a unique name for the new failover group.
Click on Secondary server then select Create a new server option. On the New server panel, provide the configuration details for the secondary SQL server, select the appropriate Azure region (must be different than the primary region), then click Select to add the secondary database server to the failover group.
From Read/Write failover policy dropdown list, select Automatic for automatic failover activation.
From Read/Write grace period (hours) dropdown list, select the grace period that you want to configure for the new failover group. The grace period represents the interval in hours before automatic failover is initiated if an outage occurs on the primary SQL server.
Click on Database within the group, choose the SQL database that you want to add to your failover group, then click Select to confirm your action. Adding this database to the failover group will automatically start the geo-replication process.
Click Create to create the new Azure SQL server auto-failover group.

07 Repeat step no. 4 – 6 for each SQL server that you want to implement database replication and automatic failover, available in the selected subscription.

08 Repeat steps no. 4 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure Console

01 Run sql server show command (Windows/macOS/Linux) using the ID of the primary SQL database server as identifier parameter (see Audit section part II to identify the right resource) to describe the configuration details for the selected SQL server:

az sql server show
  --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-production-server"

02 The command output should return the configuration metadata for the selected server:

{
  "fullyQualifiedDomainName": "cc-ms-production-server.database.windows.net",
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-production-server",
  "administratorLogin": "ccadminusername",
  "administratorLoginPassword": null,
  "identity": null,
  "kind": "v12.0",
  "location": "westeurope",
  "name": "cc-ms-production-server",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "state": "Ready",
  "tags": {},
  "type": "Microsoft.Sql/servers",
  "version": "12.0"
}

03 Run sql server create command (Windows/macOS/Linux) using configuration information returned at the previous step, to create a secondary SQL database server in the failover region, in this case, the North Europe region (the command does not produce an output):

az sql server create
  --name cc-ms-secondary-server
  --resource-group cloud-shell-storage-westeurope
  --location northeurope
  --admin-user ccadminusername
  --admin-password ccadminpassword
  --no-wait

04 Run sql failover-group create command (Windows/macOS/Linux) to create an auto-failover group between the primary SQL server and secondary server (i.e. partner server) created at the previous step:

az sql failover-group create
  --name cc-auto-failover-group
  --server cc-ms-production-server
  --partner-server cc-ms-secondary-server
  --resource-group cloud-shell-storage-westeurope
  --add-db cc-ms-production-database
  --failover-policy Automatic
  --grace-period 1

05 The command output should return the metadata for the newly created auto-failover group:

{
  "databases": [
    "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-production-server/databases/cc-ms-production-database"
  ],
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-production-server/failoverGroups/cc-auto-failover-group",
  "location": "West Europe",
  "name": "cc-auto-failover-group",
  "partnerServers": [
    {
      "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-secondary-server",
      "location": "North Europe",
      "replicationRole": "Secondary",
      "resourceGroup": "cloud-shell-storage-westeurope"
    }
  ],
  "readOnlyEndpoint": {
    "failoverPolicy": "Disabled"
  },
  "readWriteEndpoint": {
    "failoverPolicy": "Automatic",
    "failoverWithDataLossGracePeriodMinutes": 60
  },
  "replicationRole": "Primary",
  "replicationState": "CATCH_UP",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "tags": null,
  "type": "Microsoft.Sql/servers/failoverGroups"
}

06 Repeat steps no. 1 – 5 for each SQL server that you want to enable database replication and automatic failover, available in the selected subscription.

07 Repeat steps no. 1 – 6 for each subscription created in your Microsoft Azure cloud account.

References

Azure Official Documentation
Use auto-failover groups to enable transparent and coordinated failover of multiple databases
Configure a failover group for Azure SQL Database

Publication date Oct 26, 2019

Enable Auto-Failover Groups

Audit

Using Azure Console

Using Azure CLI

Remediation / Resolution

Using Azure Console

Using Azure Console

References

Related Sql rules