SQL Azure Geo Replication Configuration and Testing

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

Problem

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.

Solution

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:

Configure SQL Azure for Geo Replication

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

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.

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.

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.

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

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.

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.

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

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.

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

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:

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.

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.

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.

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

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.

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.

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

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.

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

Next Steps

• As you consider moving to SQL Azure, be sure to understand the options for SQL Server High Availability and Disaster Recovery.
• Check out more SQL Server Azure Tips
• Read more about Azure SQL database documentation.

About the author

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