SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Azure SQL Database Active Geo Replication Changes

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.

MainREPL

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.

QuickADD

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.

readonly SSMS

Try and update a table and it will fail.

FAILEDREAD


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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...