Geo-Replication for Azure SQL Database

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:

  1. Your application is high on writes/reads and you wish to offload your reads to a different server.
  2. 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.
  3. 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.

Taken from MS videos

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.

Enable Geo-Replication

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.

Create Secondary server/database

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.

Conclusion

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s