SQL Database: How to Configure Active Geo-Replication

Active Geo-location is powerful magic for ensuring the high availability of a Azure SQL database, and for disaster-recovery. In choosing the best options, you need to accurately understand the value that the business places on the service you're running, long it will take for a secondary replica to be in synch with the primary replica, the importance of spreading the location of replicas widely, and the maximum tolerable unscheduled downtime. Just clicking all the options could prove to be expensive.

Whatever the nature of your database server, your data has to be as readily available as the business expects it to be. The Business Continuity (BC) plan is one of the most important ways of tying down and agreeing the importance of a system to the business, and determining whether you are running a mission-critical database.

On this article we will take a look on how to configure and deal with the BC options that are available on Azure SQL Database. We will show how to select those option within the Active Geo-Replication that align closest to the requirements specified in your BC plan:

We will approach the configuration by using the Azure Portal, but there are other ways to do it. We could, for instance, use PowerShell.

This article is divided into different sections that show you everything from how to set up the Active-Geo Replication from zero to how to maintain it.

What is Active Geo-Replication?

As already mentioned, Active Geo-Replication is a Business Continuity feature for SQL Database that allows the addition of up to four secondary replicas of your database spread in regions of your choice. If you are familiar with the High Availability and Disaster Recovery options for an on-premises installation of SQL Server, the Active Geo-Replication is very similar to the AlwaysOn Availability Groups.

The primary database and its four replicas are kept in sync through an asynchronous mechanism that may lead to some data loss in certain circumstances.

The Active Geo-Replication can be used as a Disaster Recovery and High Availability solution, thanks to its capability to maintain four readable replicas. By taking advantage of it, users that are connecting to the database from another geographic location might have a readable replica in a location that is closer to them, which would give them the best possible performance from the application. Active Geolocation also allows you to change roles to keep the database highly available: you can avoid planned downtime by promoting a secondary to primary when a planned maintenance is imminent.

On the other hand, problems may happen. If you have a failure on the primary location, your data will be safe on another location, and you can easily keep the service running with the newly promoted Primary replica. Usually, in a planned maintenance, great care is taken to synchronize the actions to avoid losing data, but in an unplanned failover, some data may inevitably be lost.

The Active Geo-Replication is now available on all the three tiers (Basic, Standard and Premium), but this wasn’t always the case. This feature was once available only on the Premium service tier, and the alternative was to use Standard Geo-Replication. On April 25th, Microsoft announced that the Active Geo-Replication would be extended to all the tiers and the Standard Geo-Replication would be retired within 12 months. From April 2017, the non-readable secondaries will be retired, being all the replicas as a readable secondary.

Do I really need the Active Geo-Replication?

If you are too lavish in your choice of Active Geo-Replication options, you will incur unnecessary costs for your database environment. For this reason, you need to evaluate if you really need to use this feature and, if you do need it, the number of replicas that you require. A good plan is understand the business requirements for:

  • Estimated Recovery Time (ERT): The required time that a secondary replica needs to be in synch with the primary replica.
  • Recovery Time Objective (RTO): The maximum time that an application should recovery after an outage.
  • Recovery Point Objective (RPO): The acceptable amount of data loss when a disruptive event occurs.

Apart from avoiding the consequences of a disaster, the Active Geo-replication could be used to avoid down time during a maintenance and to distribute the data load, as I’ve already mentioned.

Remember that Azure SQL Database has other ‘tools’ for the Business Continuity role. Depending on the agreed continuity requirements, you may be well-served with a ‘point-in-time’ restore or a geo-restore, for example.

Configuring the Active Geo-Replication

In order to configure the Active Geo-Replication, your user should be the subscription owner or co-owner. At this stage you should already have the primary database ‘up-and-running’ on Azure. The created secondaries will have, by default, the same name and service tier as the primary. This means that if you already have a database with the same name in the Active Geo-Replication partner (secondary logical SQL Server instance), the configuration will fail.

The service tier used on the secondaries can be modified after the initial configuration, but it is best that the secondary should never run in a service tier lower than the primary.

While mentioning the service tier, I should point out that each secondary replica is priced as a ‘normal’ SQL Database. If your primary is at the S0 level and the secondary at S2, you will pay the regular S2 price for the secondary.

  1. Log into the Azure Portal (https://portal.azure.com/)
  2. Click on ‘SQL Databases’ in the menu. If the item is not there, go to ‘Browse>SQL Databases’
  3. Select the database that you want to replicate. As example, I will select the ‘AdventureWorksLT’ sample database.
  4. Once the database is selected, a new pane will be opened. On the left-hand settings menu, select the ‘Georeplication’ option.
  5. The Geo-Replication information will be shown, including the already existing replicas and possible target regions to configure the new secondary. As you can see in the image below, the Azure Portal will recommend the best region, based on your current settings, which may not be the best for your strategy.
  6. Select the desired region to create the replica and a new pane will be shown.
  7. On this new pane, select the pricing tier, the secondary type (readable or non-readable replica), the target server/logical SQL Server instance (if it isn’t created already, you can create during the process) and the elastic database pool, if required (the pool should already exists on the target server). Click on ‘ok’.

  8. The creation process of the new secondary will start and the progress can be monitored from the ‘Geo-Replication’ pane.

  9. Once the status is ‘Readable’ your secondary database will be ready.

  10. You can also verify the database role from the SQL Database menu.

    This process needs to be repeated in order to add new secondaries (up to four).

Failover to a secondary in another region

Now that the Active Geo-Replication is configured, you should have, at least, one replica. This replica can change roles and become the primary – a failover can occur. This is a simple process, on the database point-of-view, and can be done as follows:

  1. Log into the Azure Portal (https://portal.azure.com/)
  2. Click on ‘SQL Databases’ in the menu. If the item is not there, go to Browse>SQL Databases
  3. Select the SQL Database that is on Primary role.
  4. Once the database is selected, a new pane will be opened. On the left-hand settings menu, select the ‘Geo-replication’ option.
  5. In the Geo-Replication panel, you will find a list of all the secondary databases, per region. Select the secondary that you want to change roles with the Primary.
  6. Some information about the new replica will be shown, with two options ‘Failover’ and ‘Stop Replication’.

    In order to switch roles, click on the ‘Failover’ button and confirm the action, when prompted.
  7. Once the failover action is started, the new primary will be available typically in less than 30 seconds and all the other replicas will automatically connect to this new primary in order to keep synchronizing.

How to remove a secondary database?

If it is possible to add replicas, it is also possible to remove replicas. And the process is very simple and very similar to what was done in order to failover, as shown on the steps bellow:

  1. Log into the Azure Portal (https://portal.azure.com/)
  2. Click on ‘SQL Databases’ in the menu. If the item is not there, go to Browse>SQL Databases
  3. Select the SQL Database that is on Primary role.
  4. Once the database is selected, a new pane will be opened. On the left-hand settings menu, select the ‘Geo-replication’ option.
  5. In the Geo-Replication panel, you will find a list of all the secondary databases, per region. Select the secondary that you want to be removed.
  6. A pane with information about the selected replica will be shown. Click on the ‘Stop Replication’ button and confirm your intention, when asked to.

  7. The removal process will begin, and the replica will be excluded from your SQL Databases.

My application is not working after the failover, what should I do?

If you your database is configured to be replicated by using the Active Geo-Replication, you need to be prepared for a planned, or even an unplanned, failover.

In terms of database configuration, once the Active Geo-Replication is configured you will need to take care of other two aspects:

  • Firewall rules: The database replication does not bring the firewall configurations to the secondary replica. This way, you will need to make sure that the same rules affecting the respective primary database will be also true on all the replicas.
  • Logins and users: As example of the firewall rules, all the logins used to connect to the primary database should exist on the secondary as well.

By taking care of those two aspects, you will be sure that your database will be reachable after a failover, the only remaining action is the connection string configuration, which will be done at application level.

Do not forget to constantly test a failover situation, in order to simulate a real incident. This is a very important act that will help to ensure that all the teams and configurations are aligned and ready for the circumstance when a real failover occurs. Remember that apart of the technology, human aspects are decisive for the success of a good Business Continuity Solution.

On this article, we approached one of the methods to configure the Active Geo-Replication for an Azure SQL Database, as well as how to remove replicas and how to perform planned failover. If you are starting with Azure SQL Database, I recommend that you read my previous articles ‘Azure SQL Database – How to Choose the Right Service Tier‘ and ‘Questions About Azure SQL Database You Were Too Shy to Ask‘.