Steps for SQL DB Geo Replication

Published On: 2018-09-14By:

Unless you are living under a rock, you have most likely heard of the hurricane that is bearing down on the east coast of the United States.  Some areas have mandatory evacuation and I have seen reports of 80’ plus foot waves so this hurricane is not a joke.

Another item that should not be a joke is a disaster recovery plan.  This is a vital and critical plan that every organization should have in place.  If you do not have a plan in place, you are asking for events, like a hurricane, to potentially force your business to close its doors for days or even weeks on end.

However, if you are utilizing SQL DB in Azure, you can easily configure a quick and dirty DR plan to get you through the rough times.  While it might not be an automatic failover (I’ll blog about that later), it is better than nothing and it just might save your business.

Let’s take a look.

Azure Portal

Looking in the portal, I’ve got an Azure database, Test1, that is residing in the East US region.  This is just a Basic database, nothing fancy nor pricey.  However, with the hurricane coming, I would want to move my data away from the East coast.

 

 

The database currently is not replicated anywhere and other than the default backups that Microsoft takes, it is not configured for any type of failover or disaster.  However, that can be easily remedied with a couple of mouse clicks.

If you did not notice, there is a Geo-Replication option under Settings.

 

The resulting blade will show you a map of the world along with all of the options for where a replica (or two) could reside.  The blue check mark indicates where the database currently resides.

You will also notice that geo-replication is currently not configured.

There are a couple of ways that you could enable geo-replication.  You can click on any of the green circles on the map or you can select an appropriate region under the “Target Regions” shown below the map.

The West US 2 region seems like it’d be a good fit for my needs.  By choosing this region, the database would be replicated from the East coast to the West coast, which should be well away from the hurricane.  If the hurricane has an effect on the West coast, we all have bigger problems to worry about.

Once I select West US 2, a Create Secondary blade will appear.

In this case, I already had a server configured in West US 2 that I can use.  If it was not already present, it would only take a few mouse clicks to configure a new target server.   You will also notice that the pricing tier is just Basic and nothing fancy.

Once you have the server configured and/or selected, just simply click “OK”.  You’ll be returned back to the database geo-replication blade.  You will see that there is now a secondary listed and it’s status is “Initalizing”.  The blue dotted line (it is animated in the portal) dictates data movement from one region to another.  Once data has replicated to the new region this will turn into a solid line.

 

Once it has finished, the status will also change to Readable as shown below.

If you wanted to do a manual fail-over at this point to West US 2, you could do so by selecting the elipse option next to the secondary and select “Forced Failover”

It is worth noting, however, that this geo-replicated configuration is asynchronous which means there is a potential for data loss in the event of a manual failover.  It is worth repeating.  A manual failover in this configuration you run the risk of potential data loss.

If you wanted to have automatic synchronous failover, you would need to setup a failover group.  I’ll blog about that later.

Summary

If you are using SQL DB in Azure and don’t have your critical database geo-replicated, you are opening yourself up to potential issues in the event of a disaster.   By configuring geo-replication you at least give yourself the ability to make a choice.  You could manually fail-over and keep the lights on, or do nothing and potentially close the doors for days or weeks.

Keep in mind that after the disaster, you could stop the replication and delete the secondaries.  Yes, you will have an increase in cost but it might just be worth it to save your business.

 

© 2018, John Morehouse. All rights reserved.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.