Azure SQL Failover Setup

TL;DR;

Azure SQL Failover Groups is a managed version of Geo Replication which enables both automatic and manual failover.

1. Create a SQL Server in another region
2. Create a Failover Group for a database currently on the primary server
3. Check your authentication works on both servers
4. Change your connection string to point to [failover-group-name].database.windows.net

For azure cli, see immediately below.
For bicep, see the bottom of the post.

Set up test server

If want to just try this out, use this bash script to create a server and database to experiment with. Note; Even if you don't have the Azure CLI on your machine, you can run all of this from the Azure Cloud Shell very easily.

1 group=fl-20221201-sqlfailover
2 location=northeurope
3 sqlServerName=failovertest-uat-eun-ss
4 sqlDbName=failovertest-uat-db
5 sqlUsername=[sql user name of your choice]
6 sqlPassword=[strong password]
7 sqlCapacity=10 #20 = S1, 10 = S0
8 
9 az group create --name $group --location $location
10 
11 az sql server create --name $sqlServerName --location $location --admin-user $sqlUsername --admin-password $sqlPassword -g $group
12 
13 az sql db create --name $sqlDbName --server $sqlServerName --edition Standard --capacity $sqlCapacity -g $group
14 

Auth heads-up

Before you go any further it is important to stop for a minute and discuss authentication to avoid problems later.
The failover is essentially just a bit of network routing magic: When you connect to the Failover connection string, it just routes you to the currently primary server. This means you need to take care that your applications credentials works correctly on both servers. Otherwise, everything will break when you fail over.

Using server administrator and dbo

If your application just uses the "SQL Server Administrator" login and defaults to everything in SQL (i.e. using "dbo" at the database level) then it's relatively straight-forward: Just make sure you use the exact same SQL Admininistrator username and password on both servers.

* You are not really supposed to do this! Your application should have a different user, with different permissions etc. In practice, though, many applications use Azure SQL exactly like this.

Using Managed Identity

If you use Managed Identity (or other AD accounts) to access the database, it should "just work". When you do CREATE USER [user] FROM EXTERNAL PROVIDER then it is created as a "Contained User" in the database, meaning it doesn't create a "login" on the SQL Server itself.

That said - do test it, preferably in a test environment.

Managed Identity with Azure SQL

Using your own SQL users

If you have created your own SQL User for your application (and, indeed, others) and you didn't create them as "Contained Users" then you need to do some more manual work. See this Microsoft documentation for more details.

Set up fail over

1 group=fl-20221201-sqlfailover
2 failoverlocation=westeurope
3 existingSqlServerName=failovertest-uat-eun-ss
4 failoverSqlServerName=failovertest-uat-euw-ss
5 failoverGroupName=failovertest-uat-fog
6 sqlDbName=failovertest-uat-db # name of the *existing* database
7 sqlUsername=[sql user name of your choice] # See auth section above. May need to be the same as the primary
8 sqlPassword=[strong password] # See auth section above. May need to be the same as the primary
9 
10 az sql server create --name $failoverSqlServerName --location $failoverlocation --admin-user $sqlUsername --admin-password $sqlPassword -g $group
11 
12 az sql failover-group create --name $failoverGroupName --partner-server $failoverSqlServerName --server $existingSqlServerName --add-db $sqlDbName --failover-policy Automatic -g $group
13 

Remember to look at vNets and firewall rules and make sure they match!

Change connection string

Final step is to change the connection string in your application to be [failover-group-name].database.windows.net. This will connect your application to whichever database is currently primary and will automatically redirect if the servers fail over.

WARNING: If your connection string specifies the server name in the user ID like this: User ID=user@server then this won't work when you fail over. You need to change it to just be User ID=user.

Failover

Automatic fail over

By default the automatic fail over will wait one hour before failing over.
Azure will not automatically "fail back": Once the primary region is working again, you need to manually "fail over" again, if you wish to do so. If your application is running hot-hot in both data centres it doesn't matter, but otherwise you will incur a performance hit and ingress/egress costs by having your application and your database in different regions.

Manual failover

You can manually fail over in the Azure portal by going to either of the servers (not database) and select "Failover groups" from the menu.
Alternatively you can use the Azure CLI like this:

1 group=fl-20221201-sqlfailover
2 existingSqlServerName=failovertest-uat-eun-ss
3 failoverSqlServerName=failovertest-uat-euw-ss
4 failoverGroupName=failovertest-uat-fog
5 
6 # See which server is currently primary
7 az sql failover-group list --server $existingSqlServerName -g $group
8 
9 # Fail over
10 az sql failover-group set-primary --name $failoverGroupName --server $failoverSqlServerName -g $group
11 
12 # Fail back
13 az sql failover-group set-primary --name $failoverGroupName --server $existingSqlServerName -g $group
14 

Bicep version

1 @description('The name of the primary SQL logical server.')
2 param primaryServerName string = 'failovertest-uat-eun-ss'
3 
4 @description('Primary Location')
5 param primaryLocation string = 'northeurope'
6 
7 @description('The name of the secondary SQL logical server.')
8 param secondaryServerName string = 'failovertest-uat-euw-ss'
9 
10 @description('Primary Location')
11 param secondaryLocation string = 'westeurope'
12 
13 @description('Failover group name')
14 param failoverGroupName string = 'failovertest-uat-fog'
15 
16 @description('The name of the SQL Database.')
17 param sqlDBName string = 'failovertest-uat-db'
18 
19 @description('The administrator username of the SQL logical server.')
20 param administratorLogin string
21 
22 @description('The administrator password of the SQL logical server.')
23 @secure()
24 param administratorLoginPassword string
25 
26 resource primarySqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
27   name: primaryServerName
28   location: primaryLocation
29   properties: {
30     administratorLogin: administratorLogin
31     administratorLoginPassword: administratorLoginPassword
32   }
33 }
34 
35 resource sqlDB 'Microsoft.Sql/servers/databases@2022-05-01-preview' = {
36   parent: primarySqlServer
37   name: sqlDBName
38   location: primaryLocation
39   sku: {
40     name: 'Standard'
41     tier: 'Standard'
42   }
43 }
44 
45 resource secondarySqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
46   name: secondaryServerName
47   location: secondaryLocation
48   properties: {
49     administratorLogin: administratorLogin
50     administratorLoginPassword: administratorLoginPassword
51   }
52 }
53 
54 resource failoverGroup 'Microsoft.Sql/servers/failoverGroups@2022-05-01-preview' = {
55   name: failoverGroupName
56   parent: primarySqlServer
57   properties: {
58     partnerServers: [
59       {
60         id: secondarySqlServer.id
61       }
62     ]
63     databases: [
64       sqlDB.id
65     ]
66     readWriteEndpoint:{
67       failoverPolicy: 'Automatic'
68       failoverWithDataLossGracePeriodMinutes: 60
69     }
70   }
71 }
72