Steps For SQL DB Geo Replication

SQL DB Geo Replication

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 foot-plus 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.

Azure Portal

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 blade under Settings. 

Geo-Replication blade 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. 

SQL DB Geo Replication

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. 

Secondary blade

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 its 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.

new region

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

SQL DB Geo Replication

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

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. If you wanted to have automatic synchronous failover, you would need to set up 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 failover 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.


Similar Articles
Denny Cherry & Associates Consulting
Expert Consultants From HA to DR to up-time to SQL virtualization to scalability.