Six months ago how you would go about setting up Active geo replication for your SQL Databases would be different to today, yes things (naturally) do change but for this specific area it has changed for the better – again something that you would expect right?

So what exactly am I talking about? In the past there were two things that I didn’t like:

• If failover occurred you would need to update your connection strings, yes a manual laborious task.
• If your server had (for example) 11 databases that you wanted to setup for geo replication, then you would have to individually set them up.

Microsoft Listened

So Microsoft came up with the idea of failover groups. Azure SQL Database auto-failover groups is a SQL Database feature designed to automatically manage geo-replication relationship, connectivity, and failover at scale.

Under the covers you may be familiar with it, it uses the Always On technology of SQL Server to asynchronously replicate committed transactions on the primary database to a secondary database using read committed snapshot isolation (RCSI).

Once setup it looks like this.

The key point here, you have these endpoints (read only and read/write) which you should be utilising instead of manually updating  connection strings. (Top arrow I just wanted to highlight it because I like the map diagram).

Going back to my other issue about scalability if you want to add another (or multiple) database now you just click Add database and select what you want.

What else?

Not necessarily new features but it is worth mentioning the capabilities of this technology.

• Multiple readable secondary databases, don’t forget the READ ONLY endpoint I mentioned earlier on.
• Failover Groups DOES support elastic pools (I might do another blog post on this depending on Azure credits).
• You can configure your secondary to have a lower performance tier if you think it will suit your workloads.
• You can control failover (also forced failover) and failback with a click of a button.

Just to prove one can connect to the read only endpoint I will use SSMS.

Try and update a table and it will fail.

Filed under: Azure, Azure SQL DB Tagged: Azure, Azure Portal, Azure SQL DB, Replication, SSMS