This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.
Starting from the scenarios:
- Your application is high on writes/reads and you wish to offload your reads to a different server.
- Your database resides in a specific location let’s say East US 2 but different applications or users are using it for creating reports from all over the world.
- You want to have some DR setup for your Azure SQL Database.
The one stop solution for all the above scenarios is using Geo-Replication for Azure SQL Database.
Azure provides two different for providing business continuity and which are Geo-Replication and Auto-Failover groups. (We’ll discuss about the Auto-Failover groups in a later blog)
The main difference between these two options are as below.
For offloading reads (scenario 1) and creating multiple Replicas in different regions (scenario 2) Geo-Replication works like a charm.
There is one thing to consider when using Geo-Replication for providing business continuity and that is you need to change the application string to point to the replicated database at time of failur of primary, and this is a manual task and requires time.
Configuring Geo-Replication for Azure SQL Database
Steps for configuring Geo-Replication are pretty easy. Just select your database (in the portal) choose the Geo-Replication option and select the location which suits your requirement.
Under the covers Geo-Replication actually uses the SQL Server Always-On Availability Group technology with the replicas in Asynchronous mode.
Once you select any region it will ask you to provide the SQL Server, which already exist or you can also create a new one from there itself.
Once you make the selection and create, it will take some time to provision the database and configure the Replica as it seeds the data from the primary to the other region.
To know how to configure the application to offload your reads to secondary refer this link – Design cloud solutions for disaster recovery.
Azure has different options available to help with business continuity, Geo-Replication being one of that.
When using that do consider that it uses Asynchronous mode that means your readable secondary could be lagging behind the primary depending upon the type of load on primary, the size of the secondary and also the location of the secondary Azure SQL Database.
But if it suits your requirement it is easy to configure and manage.