SQL Azure Geo Replication Configuration and Testing

By:   |   Comments (3)   |   Related: > Azure


Business continuity is always a main focus for any database technology. Microsoft SQL Azure database offers business continuity options through multiple solutions. In this tip we will review the options and dig into some of the details for Geo Replication.


Microsoft SQL Azure provides the following business continuity options:

  1. Geo Restore
  2. Geo Replication (Standard and Active Geo Replication)

Geo Restore

This solution is similar to restoring a database backup with point in time recovery with traditional SQL Server databases. The database backup for Microsoft SQL Azure is stored in geo redundant Azure storage called GRS. Geo restore provides the ability to restore the database into any Azure region from the Geo redundant backup.

For this option, recovery time depends on the database size, performance level of database, and the number of restore requests being severed by the target region. So this is not a recommended solution for mission critical databases where downtime should be very minimal in case of any disaster.

Geo Replication

With Geo Replication we can create replica databases in a secondary region. The database in the secondary region is always a consistent copy of the primary database.

There are two types of Geo Replication:

  1. Standard Geo Replication: With Standard Geo Replication there is a non-readable replica of the primary database. This is useful if a disaster happens on the primary database. However, this database cannot be used for load balancing.
  2. Active Geo Replication: Active Geo Replication provides a readable copy of the primary database so the secondary database can be used for reporting purposes or to distribute the application workload where the only requirement is to read the data. We can have multiple online secondary databases.

Standard databases can have one non-readable secondary and must use the recommended region. Premium databases can have up to four readable secondarys in any of the available regions. Standard databases do not have the option of Active Geo replication, premium databases allow both standard and active replication.

Standard Geo Replication initially creates a copy of the primary database, called seeding. Once the seeding is completed, all committed transactions are sent to the secondary databases although the database will be out of sync for a period of time. There will be a delay in syncing the databases as only committed transactions are replicated, which means there can be some data loss in case failover occurs prior to the databases being in sync.

As per Azure documentation, below is the comparison between these solutions:

Scenario Geo Restore Standard Geo Replication Active Geo Replication
Regional disaster Yes Yes Yes
DR drill Yes Yes Yes
Online application upgrade No No Yes
Online application relocation No No Yes
Read load balancing No No Yes

Configure SQL Azure for Geo Replication

Login to the Azure portal and go to the SQL Databases interface.

Microsoft SQL Azure SQL Databases

We can also see the replication role for each database in this interface. Since we have not configured replication the value is "None".

Suppose we want to configure Geo Replication for the Adcentureworks2012 database. To do so, click on the Adventureworks2012 database which will open the database details interface.

SQL Azure Database Properties

Now click on the "Not configured" link below the Geo Replication role highlighted in green above.

By clicking on this link, the next interface will show the Geo Replication in a graphical mode along with the recommended region which can be used for creating a secondary replica.

SQL Azure Geo Replication

Select a region for Geo Replication

Since I am using the standard tier database, I cannot create a secondary replica except from the recommended region. If you click on another region, you see the below error message.

To enable this region, click here to upgrade the database to Premium edition.

So click on recommended region, which will open the configuration window for "Create secondary" as shown below.

Confirm the parameters for the Create secondary database

Now click on the Target server to configure the required settings which will show the existing server, if any, else it gives you the option to create a new database server in that region. Here we are creating a new server.

Configure the secondary server name, user name and password

Complete the required details including the name of the server, server admin login id and password then click on the "Select" button. This will show the server name in the create secondary window as shown below.

Confirm the secondary server configuration

Now click on the "OK" button and the deployment of Geo Replication will begin.

Geo replication is starting

Once deployment is complete, we can see the role against the source database as primary. On the map we can also see the regions connected with a line that shows the Geo replication is configured between these two regions.

Validate the geo replication configuration

We can also see the new Replication Roles configured on the SQL databases interface.

Review the Replication Roles

Connecting to SQL Azure via SQL Server Management Studio

Now let's try to connect to the new server from SQL Server Management Studio. Unfortunately, we receive the following error message:

SQL Server Management Studio error message due to firewall issues

This is because the firewall rule for the new server doesn't allow connections from our local machine, so in the Azure Portal click on the database name and configure the firewall rule option. For this demo I have allowed a whole range of IP's, but in your environment you should select the correct IP Address range.

Configure SQL Azure Firewall Settings

Now we can connect to the database from SQL Server Management Studio, but since we deployed standard Geo Replication we cannot access the data in the new database.

Successfully connect to SQL Azure from SSMS

Failover from primary to secondary database with SQL Azure

Before starting the manual failover let's UPDATE a record in the primary database so that we can verify replication is working properly.

Update data to test data status

To start the failover in the Azure Portal, go to Geo Replication and we can see both the primary and secondary replica.

Review the Geo Replication status

Click on the secondary and you have the option to either stop replication or initiate a failover. See the details below that the old primary will automatically become the new secondary if it is online.

Initiate a failover between the primary and secondary servers

Once we click on the Yes button for failover, the failover process will start and once completed we can see the roles of both databases swapped.

Request submitted to failover replication
See the database roles swapped in the Geo Replication interface

Now let's connect to the new primary and verify the data includes the UPDATE statement that was issued.

Review the data after the swap to validate replication has completed

We can see the database is updated with the record that shows that the data was transmitted from the primary to the secondary.

Also the role of the database can be seen from the SQL Database tab in the Azure Portal.

Review the Replication Roles after the failover process

During the failover process there might be slight application disconnect until the secondary database takes the role of the primary database.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, April 27, 2021 - 9:21:25 AM - rajendra gupta Back To Top (88614)
Hi Rajashekar

I hope you have checked the Microsoft documentations carefully. If not ,go through its Github version


Tuesday, April 27, 2021 - 6:39:00 AM - Rajasekhar Reddy Bolla Back To Top (88611)
Hi Rajendra,

We have two concepts only 1) Auto failover groups 2) Geo replication. This is as per Microsoft

There is no Standard geo-replication and active geo-replication.

Thursday, August 25, 2016 - 7:42:54 PM - Jason Back To Top (43188)

Rajendra, Nice aritcle.

Does Active Geo Replication performs automatically failover too, when the database in the primary is not available for some reason?

How can we have monitoring on the SQL DB to understand when the database is down in primary region?

As a user , do we need to enable any Monitoring?

As a user, do we need to enable any Load balancing between primary and secondary database? 


get free sql tips
agree to terms