Availability groups offer us a variety of features. They offer high-availability (HA), they can give disaster recovery (DR), and they have connectivity and off-loading features but we have to decide how to use them. This is the first of a four-part blog series which focuses on my recommended availability group architectures. I will cover; what they are, when you should use them, and the pros / cons.
Why you need disaster recovery
On April 6th, 2012, an F-18 Navy fighter jet crashed into an apartment building in Virginia Beach, VA. That apartment building is six miles from the two primary data centers for an international franchise organization where I was employed.
It is not often that a company can point at such a near-miss and say, “that is why we have a remote disaster recovery site.” Hurricanes, extended power outages, or even someone driving a pick-up truck into the building were more likely to occur than a jet taking out our data center but life reminded us that anything can happen.
Availability group – HA only
The architecture above, single site architecture, is the simplest architecture that you can set up with an availability group. Availability groups require a Windows Server Fail-over Cluster (WSFC). The two server nodes in the cluster can be physical or virtual, it makes no difference to the availability group. Replica A and B are both stand-alone installations of SQL Server 2012 or higher and the disks are local drives. Whether the drives are physically attached or not is of little consequence. What is important is that, in this architecture, the data is duplicated and that the copies of the data are not located within the same device, such as a single SAN. Finally, the connecting line between the replicas represents the data synchronization in synchronous commit mode.
- High-availability is achieved! The combination of synchronous commit mode and the physical separation of the disks allows for automatic fail-over in the event of catastrophic failure of either node.
- Simplicity. This architecture’s simplicity means that it is the easiest to support and easiest to troubleshoot during an incident.
- Data duplication. Availability groups duplicate your data, that is in their nature. In two of the posts, later in this series, I will discuss means of limiting disk capacity requirements.
- Server objects. You must synchronize your server objects manually or write your own automation scripts. Availability groups handle database level synchronization and does not offer any help with server objects. This too is in the nature of the availability group, however, two of the architectures that I will discuss can mitigate some of this issue.
- No disaster recovery site. Without a second site, you are still vulnerable to many types of catastrophic problems, such as F-18s with dual engine failures.
When to choose this
To be blunt, I would never choose this architecture. The lack of a disaster recovery site is not acceptable to me, as a database administrator. Unfortunately, my desires are unimportant when making architecture decisions, only the business needs are relevant. If the cost of a remote DR site is not something that your company can afford or needs then you will be forced to take this path.
If you choose this architecture remember to keep your data on physically separate devices and use synchronous commit. If your applications cannot handle the latency that comes from using synchronous commit, fix the internal networking issues, do not choose asynchronous commit. Choosing not to buy a $500 safe to secure a $10 watch is a good business decision.
The next post in this series will expand upon this architecture by adding in a remote disaster recovery site. It will also contain two more servers, a second sub-net, and a combination of data synchronization modes.