Having some redundancy in your database infrastructure is essential to making sure your applications are accessible as much as possible. Even with a solid platform as Azure SQL database, you might still encounter a problem in one Azure region where your data is no longer accessible for a period of time. Not your best morning when that happens.

To mitigate that risk, you can use Failover Groups in Azure SQL. It should be as magical as just selecting another server, but there are some pitfalls to avoid. Optionally I describe how to use it in Navision setup, as I described before how to use Azure SQL as backend for Navision.

Setup Failover

You need an existing Azure SQL database which you want to enable failover for. Failover is enabled at server level, and there you can chose which databases should be included in that failover process.

On the Azure SQL server level, you go to Data management and create a Failover Group. For the Failover Group, you select a second database server to which the failover will happen. This secondary one will function as a readonly replica as well.

I tried to create the secondary server during the setup of the Failover Group, but that server never gets created and hence you cannot pick it from the secondary list. So I’d advice to set it up on beforehand.

This screen makes you believe you’re going to setup a server, but when clicking Create, and returning to the Failover Group setup, you can’t pick that new server from the list, nor does it exist in your subscription.

When create up a new Azure SQL server, you best chose an Azure Region which is different from the one your original database server is in, as this is the point of the failover.

Back to the process of creating the Failover Group, click + Add group.

You can now fill in the required fields, select the database(s) you want to replicate and press create.

The grace period is the time allowed before automatically failing over, as there might be other reasons why a database is temporarily not available. Failing over all databases because of a single network delay induces much more network traffic, making the issue probably worse. At least, after an hour the process is started, and you still have the ability to manually fail over.

After you created the group, you can see its configuration

Replication from West Europe to North Europe

The endpoints for connecting to the primary and the read-only are also listed. These we will need for configuration access from our applications:
Primary : axis0-fo.database.windows.net
Read-only (Secondary) : axis0-fo.secondary.database.windows.net

Aligning logins

The failover group is now in place. You could think this is the end of the story, but when you test out the connection, you might notice the following problem.

You want to create a login on each server that will be used by your app. Since the failover can point to either server, you need to be able to access it. Moreover, you’d need the same password on both servers, as the application cannot know to which server it is connecting. For tips on connecting to Azure SQL, I summarised them in this write-up.

-- on the primary server only
CREATE LOGIN myapp WITH PASSWORD = '1!securepwd' 

-- switch to the primary database
CREATE USER myapp 
exec sp_addrolemember 'db_owner', 'myapp' -- in case you want to give it all access to this database

The User will get replicated on the secondary database (and you cannot create users there as it is read-only), but it will not be linked to a login, as you didn’t create the login yet.

More importantly, the login has to be created with the same SID as the the one from the primary server, or the user will not link up to the login, and you will not get access.

-- retrieve the SID
SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE name = 'myapp'

Use the value of this SID in the statement below that will create the LOGIN on the secondary server:

CREATE LOGIN myapp
WITH PASSWORD ='1!securepwd',
SID = 0x01060000000000640000000000000000FF7B5B4A5AF2814EB4E65AF4071A8C4F

You now created both logins, and have a user on both databases. You’re finally ready to use them.

You can test connecting to your failover group primary endpoint (axis0-fo.database.windows.net from above) with the myapp login.

Trigger a manual failover and try connecting again to the server. You’ll get connected, but your data will come from the North Europe region now. This can be verified by issueing

SELECT @@SERVERNAME

which will return axis0 before failover and axis1 after failover, confirming both logins work and the failover happened. Fail back to bring this back to the original state.

Connecting to Navision

In Navision, the configuration is quite agnostic of the backend. In the Administration screen, you simply provide the Failover Group primary endpoint as servername.

Obviously, you remove the keyfile as usual and you supply the credentials, restart Navision.

When you test the failover, you’ll notice Navision doesn’t even disconnect and you can continue working.