Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Azure Geo Replication Configuration and Testing


By:   |   Last Updated: 2016-03-25   |   Comments (1)   |   Related Tips: More > 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:

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


Last Updated: 2016-03-25


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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? 

 


Learn more about SQL Server tools