It is always a good idea to test your failover processes when you have setup failover groups in Azure. I have the following setup:

One SQL Server in West Europe being my primary called milkchoc and my secondary server in west US 2 called Cadbury (Yes, I like chocolates). I access the one database (called testdb) via the read/write endpoint called choc.database.windows.net.

I want to test a failover (which you should be doing if you have setup failover groups) to see what happens. To do this I go to the failover group and click “Failover”.

Once the failover completes you should see the switch of the primary and the secondary, as shown below.

So what happens now if I connect to the read/write endpoint? (I test this via SSMS)

The dreaded IP address / create a new firewall rule message. Why? Well this setup utilized a “server” level firewall rule and the server in the US did NOT have the IP address mapped in, you can see from the below screen shot that there are no firewall rules configured.

Fixing this is easy, you could just add the IP address on the secondary server as another server level rule but you should seriously consider using a database level firewall rule, the setup will get replicated to the secondary server making failover experience smoother.

Let’s test this. I failover back to the original setup. Within my user database (testdb) when on the primary server I execute the following to create a database level firewall rule:

 EXECUTE sp_set_database_firewall_rule N'ArunIP','104.xxx.xxx.xx','104.xxx.xxx.xx'

SELECT * FROM sys.database_firewall_rules

Waiting a couple of seconds I can connect to the secondary (Cadbury server) and query the database to confirm the IP address has been replicated.

Let’s failover and reconnect via the read/write endpoint now. Don’t forget the endpoint is called choc.

I can connect now regardless of where my primary server actually is.

It is important to understand this behaviour because even though I was testing the “experience” via SSMS it could relate to any resource with an IP address needing a connection to the server where the database is located.