Configuring Azure SQL DB Active-Geo replication

One of my customers is looking into migrating his SQL environment to Azure SQL DB. When migrating to a new platform, business continuity is key. When looking at the different availability solutions for Azure SQL DB we decided that for some business critical databases the Azure SQL DB standard tier with geo-replication and 1 standby secondary database was just not enough.

The premium tier of Azure SQL DB offers you ACTIVE geo replication with up-to 4 readable secondary databases! You can find an overview of the different Azure SQL Database Single-database service tiers here:

 

 

 

 

 

In this blogpost I will describe the different business continuity solutions Azure SQL Database offers and how you can configure Active geo-replication to protect your databases. But let’s have a look first at the differences between standard and active geo-replication.

Standard GEO-replication

Built on the same continuous copy mechanism as Active Geo-Replication, Standard Geo-Replication asynchronously replicates committed transactions from the primary database to one secondary database. The secondary, unlike Active Geo-Replication, is offline and does not accept client connections.

The offline secondary can be switched to active if there is a datacenter issue resulting in unavailability of the primary database. In such an event, the failover property of the database is enabled. This will activate the offline databases and making it an independent database ready to accept client connections.

The target region for the offline secondary is predetermined based on the location of your primary database.

 

 

 

 

 

When planning to use Standard Geo-Replication, consider the following:

• The secondary database has the same service tier and performance level as the primary database. Ensure you have enough capacity on the server for your offline secondary. In addition, the collation setting is replicated from the primary to the offline secondary.
• The offline secondary counts towards the maximum number of databases on a server.
• The offline secondary counts toward the maximum allowed replicas in Active Geo-Replication. This means if you decide to use an offline secondary, you will be limited to three active secondary replicas. The source and target servers must belong to the same subscription.

Active GEO-replication

The Active Geo-Replication feature implements a mechanism to provide database redundancy within the same Microsoft Azure region or in different regions (geo-redundancy). Active Geo-Replication asynchronously replicates committed transactions from a database to up to four copies of the database on different servers, using read committed snapshot isolation (RCSI) for isolation. When Active Geo-Replication is configured a secondary database is created on the specified server. The original database becomes the primary database. The primary database asynchronously replicates committed transactions to each of the secondary databases.

 

 

 

 

 

One of the primary benefits of Active Geo-Replication is that it provides a database-level disaster recovery solution with very low recovery time. When you place the secondary database on a server in a different region you add maximum resilience to your application. The cross-region redundancy enables applications to recover from a permanent loss of an entire datacenter or parts of a datacenter caused by natural disasters, catastrophic human errors, or malicious acts.

Another key benefit is that the secondary databases are readable and can be used to offload read-only workloads such as reporting jobs.

Configuring Active GEO-Replication

How do you need to configure Active GEO-replication? If you have a running Azure Premium SQL DB  you can very easily add a secondary database.

The following steps create a new secondary database in a Geo-Replication partnership. The secondary database will have the same name as the primary database and will, by default, have the same service level. You can however change the service level of the secondary databases if required. The secondary database is readable and can be a single database or an elastic database. For more information, see Service Tiers. After the secondary is created and seeded, data will begin replicating from the primary database to the new secondary database.

• In the Azure portal browse to the database that you want to setup for Geo-Replication.
• On the SQL Database blade, select All settings > Geo-Replication.
• Select the region to create the secondary database.

 

 

 

 

 

 

 

• Configure the Secondary type (Readable, or Non-readable).
• Select or configure the server for the secondary database.

 

 

 

 

• Click Create to add the secondary.
• The secondary database is created and the seeding process begins.

 

 

 

 

 

 

 

That’s it! In a few mouse clicks you have created a secondary failover database. You can use the same procedure to add another 3 databases, in different regions to increase your availability.

In the next blog post we are going to cover the different failover mechanisme of our newly protected Azure SQL Database.

Thanks,

Alex