Enable Auto-Failover Groups

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.

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):

[]

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.

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:

1. In the Failover group name box, provide a unique name for the new failover group.
2. 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.
3. From Read/Write failover policy dropdown list, select Automatic for automatic failover activation.
4. 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.
5. 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.
6. 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.

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.