SQL Server AlwaysOn Availability Group Cookbook

A “short” guide for ops engineers who are not database administrators but are working on migration MS SQL servers to AWS and Azure. TL;DR

Foreword: Do not reinvent the wheel!

I’m serious! Do not reinvent something that already works and works very well. I’m relatively new to the world of MS SQL Server DBA but even I was able to google Brent Ozar. Of course, he is referencing to points that you should do wherever you need to achieve your goals and ignore even “best practices” but, believe me, in 90% of projects it’s not your case. So, please, use his guides till the moment when you understand that you and your product is special (interpretation for TPM, it means “never”)

Problem

Some engineers (DevOops or who already moved from mom basement) use to reference to “best practices” that blindly so when it is the time to move further they aren’t able to. Transition from MS SQL Server 2008 Cluster clustering to Use Availability Groups in SQL Server 2012 is a very good example of this problem. In short, those solutions can be used in the same cluster without any temptations.

Even though you’re enabling the Failover Cluster feature, you are NOT required to have shared storage to use Availability Groups. You have the option to use a Failover Cluster in an Availability Group, but you can also run your Availability Groups with entirely independent storage subsystems if you desire. The feature is required because no matter what, Availability Groups will use parts of the Failover Clustering feature to manage a virtual network name and IP Address.

Graphical representation for a TPM is:

As a result, theoretically you can use shared disk approach for new SQL Servers 2012/2014/2016 with Standard Edition subscription and not pay extra for Enterprise Edition that is required for AlwaysOn Availability Group.

Win-Win! Actually, it is not so. From DBA perspective Standard Editions lack some nice features that are helpful for them (MS SQL 2016 version can be exception). And IMHO, even Microsoft supports this version because “The First Hit’s Free, Baby”.

From DevOops perspective everything become very crappy because AWS and Azure don’t support shared disks. Bummer! So you are faced with options to use Availability Group or something like Data Kepeer. People are using second option because there are “fear, doubts and uncertainty” and other nice habits that stop human kind to evolve.

Spoiler! We are not discussing AWS RDS here because of some limitations related to HIPAA and other compliances.

We had seen that problem so often so we even created a list of common issues related to SQL cluster migration to AWS. Still, don’t listen to me, use “expert opinion”.

Technical introduction

I will skip long introduction into Availability Group because there are much better documentations and introduction. Second reason, as a DevOops engineer I’m to bored to justify “DevOps Culture” and pretend that I’m “full stack developer”.

Still, I should provide the basis that can be skipped as “a boring part from official documentation” till next comics picture, the so called diagram.