We already talked a lot about Azure and hybrid deployments on this. In order to better understand this article I suggest the reading of the article “Azure Blob Storage – Placing database files in the cloud”, as the presented solution will be based on the same approach.
What’s the objective of a low-cost disaster recovery solution?
Disaster… a sudden event, such as an accident or a natural catastrophe that causes great damage or loss of life. This is the word’s definition, and fits very well here… If you are in your job, and tsunami alert sounds, are you going to keep calm, get all the servers and run to a safer place OR are you going to run to your family and look to protect them (and yourself)?
People are more important than processes, data, and hardware. And the natural behavior of anyone is to try to save lives, not computers. And that’s why a good disaster recovery plan is needed.
But what is the definition of a “good disaster recovery plan”? There’s no exact answer, as it depends from case to case, but in general, a good disaster recovery plan must focus on:
- Minimum (even zero) data loss (RPO);
- Very small recovery time (RTO);
Looking for those two factors, we can easily understand that a good plan cannot rely on human intervention. As said in the first part of this article, in a disaster, people will be trying to save their skins… and more, there’s no way to have someone ready to react so quick to avoid an extended downtime, so the zero-data-loss will be never net.
Talking about SQL Server, we have some options to configure a DR solution. Like AlwaysOn Availability groups, Database Mirroring, log-shipping and even a complex combination of AlwaysOn Failover Cluster Instance and replicated storage in a multi-subnet environment. But here is the first problem…which one will we choose? The answer is the default for everything related to SQL Server: it depends. Not only the RPO and RTO are factors here: the database size, the database severity in the business point of view, the available infrastructure, the network performance – which is more critical when dealing with server on different locations, etc…
Those factors are the responsible for changing the price of your disaster recover strategy. The cheaper, the higher arr the RPO and the RTO. The more expensive, the lesser are the RPO and the RTO.
For big companies, it’s relatively easy to deploy, i.e., an AlwaysOn Availability Group, running on a powerful multi-subnet cluster.
This solution is not cheap, even without the requirement of a shared storage, you still need to have two datacenters (or rent a space to collocate your server), you need to maintain a powerful network with low latency (between distinct geo-locations) and pay the SQL Server Enterprise Edition license, which is not cheap, by the way.
For any company, a micro or a macro, the value of the data is immeasurable.
I’ve heard stories of companies that broke because they didn’t do the basic disaster recovery practice: backups!
So, let’s go to the point of this article: low-cost disaster recovery.
Why I would need a low-cost disaster recovery solution? Well, let’s pretend you jst created a startup company and you don’t have enough money to invest in everything that you would like. Years ago, you would be in trouble! Nowadays there is something that helps a lot: The Cloud!
You as an entrepreneur who cares about your company and customers, decides to create a disaster recovery solution for your current environment, but after building your dream diagram, you had to start cutting some points, because you had no budget… After all the cuts, talking about the database part, you end up with a log-shipping solution working in a VM, hosted by the same platform where your production is running! This is not a disaster recovery solution…
The solutions with Azure
We already talked about backups to Azure in another article. That solution works very well and brings the capability of save your backup files in another location, far away from your datacenter. If you are still keeping your backups in the same datacenter as you have your server, you are doing this wrong. Check that article, and see how easy and cheap is to store your backups in the cloud.
Now, literally talking about Disaster Recovery solutions, let’s see the options….
Option #1: I have money to invest, but not that much.
We have a solution for you! Where you won’t need to have another datacenter, you won’t need to hire extra staff to keep the new infrastructure and is easy to configure: AlwaysOn Availability Groups replica in Azure.
Yes, this is possible, precisely, since SQL Server 2012 SP1 CU2+.
- Already have an AG replica working on-premises.
- An active Azure account (of course).
- Have a site-to-site VPN connecting your Azure virtual subnet to on-premises datacenter. (I’m explaining how to do this by using RRAS here).
How to configure?
You have two option: manually or using the wizard.
If you opt to do this manually, the overall steps are:
- Deploy the Azure VM
- Install SQL Server, if you choose a clean template.
- Join the VM into the domain
- Add the VM as cluster node
- Enable HADR feature.
- Join the instance as AG replica
- Start syncing the databases
If you choose the wizard, the task is way simpler… Just follow the steps and everything will be fine!
Option #2: I’m counting coins, but I really need a disaster recovery solution…
Well, you don’t have all the budget that you would like to, and you are ok in have a high RTO and an a not zero data loss (RPO). This solution is for you!
Basically we will be taking advantage of two Azure features: Azure VM and Azure Blob Storage.
The idea is very simple:
- You have your physical server on premises
- You have your database with the data and log files placed in Azure
- Note #1: Se more about this in this article
- Note #2: If you don’t want to have your database in Azure Blob Storage, you can work around this by having a replica of your database attached in the same instance (with another name), with the files in Azure
- An Azure Virtual Machine needs to be deployed with a SQL Server instance ready to attach that databases
- This VM should have scripts ready to attach the database file that are in Azure Blob Storage
- You can also script out the instance objects, on daily basis, and store the scripts in Azure Blob Storage. After that you just need to create a job to be triggered once SQL Server start-up and execute the scripts
- After deploying this VM, and prepare everything, you just shutdown the VM in order to reduce costs
- In case of a disaster, you will lose your server on-premises, but your files will be safe in Azure, and you just need to start-up the VM nd redirect the connections to that Virtual Machine
Of course, this is not the best solution, but works well and is really low-cost. That’s Azure flexibility!
I hope you liked it… Keep tuned for more articles. “See” you in another post.
With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community,
View all posts by Murilo Miranda
Latest posts by Murilo Miranda (see all)
- Understanding backups on AlwaysOn Availability Groups – Part 2 - December 3, 2015
- Understanding backups on AlwaysOn Availability Groups – Part 1 - November 30, 2015
- AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4 - October 13, 2015