Configuring SQL Server for High Availability in the Cloud

Every big Cloud service provider offers you guaranteed SLAs availability about 99.95 %, and this percentage of availability can make you think that cloud is perfect for a SQL Server deployment which requires High Availability. Azure and AWS are the two giants of cloud services, and their geographical distribution of data centers can leverage you to think of cloud for a SQL server development configured with disaster recovery.

Let’s reconsider the complete scenario, cloud services only guarantee you with cloud infrastructure, not with the availability of the application running on that infrastructure. We require some extra steps if we want our SQL server to run in the cloud with 99.95 or greater availability.

Infrastructure Availability

When we deal with the High Availability and Disaster Recovery Infrastructure, we need to consider two important options Availability Zones and Availability Sets.

Availability Zone

Let’s consider AWS and Azure for this option, this option for HR and DR infrastructure is available in both the cloud service providers. It put Virtual Machines in different data centers, rather than putting them in separate racks. This makes sure that one of the Virtual Machines remains available which enables providers supporting Availability Zones that guarantee 99.99% times of availability.

Availability Sets

This option is not available in AWS, only Azure cloud provides this. In this option, we have two or more than two Virtual Machines in different racks but at the same data center. In this option, Microsoft says that at least the Virtual Machine would be available 99.95 % of the time.

The main question arises here is what happen if any Virtual Machine goes down would it take the SQL server down too?

The answer is another Virtual Machine will remain available at another rack or data center, but the problem is the Infrastructure does not guarantee that the data will be transfer to another Virtual Machine, there could be a complete loss of data there. The end-users will face a complete loss of service is there is no automated way to redirect the connections to the remaining node or the secondary virtual machine does not have a copy of the data recently used by the primary SQL server.

Availability of SQL Server in the Cloud:

Generally, we use SQL Server Failover Cluster Instances (FCIs) or SQL Server Always On Availability Groups for the SQL Server deployment configured for High Availability or DR, though both the options are feasible but come with some warnings.

SQL Server Failover Cluster Instances (FCIs) uses

SQL Server Failover Cluster Instances (FCIs) uses shared storage, and the cloud does not support this, so for this problem, we have the third-party tool, “Storage Space Direct (SSD)”. This feature first introduced in window server 2016 Enterprise Edition, in this feature the SSD pool locally attached storage across two or more servers in the cluster and creates a Virtual Storage Area Network (SAN). Any virtual machine can access this virtual storage area network.

The Storage Space Direct can only work with the Availability Set, the server which is configured with Availability Zone does not support Storage Space Direct.

There are two limitations to using FCIs and Storage Space Direct.

It is limited with the Availability Sets and only Azure supports this option.

It requires SQL server 2016 or newer, the earlier versions are not supported.

Always On Availability

There is another approach, Always On Availability Groups, and this option is supported by both the cloud service providers. This raises the infrastructure availability SLA up to 99.99% and it copies the data from the primary instance of the virtual machine to the secondary instance. So it does not matter if the one Virtual machine goes down another Virtual Machine will handle the data because both the VMs are at the same data centers.

Limitation of Always On Availability

• It only copies the user-defined database to the secondary Virtual machine, and the Master database does not replicate.
• If there is a high server failure none of the data gets replicated at secondary VMs.
• It works only on the SQL server 2012 Enterprise edition or newer, which is very expensive.

Conclusion

There are limited options if we want to run the SQL server in the cloud because it is a new and complex technology and there are not many competitors across the globe. Azure and AWS cover most of the cloud services and provide specific features and impose various constraints.

For High Availability and DR, we have two options Always On Availability and FCI. Always On Availability provide more features with replicating data but it is expensive. Another Approve is FCI which imposes many constrains and only supported the Azure cloud service and also does not support database replication.