On-Premises/Cloud Hybrid for SQL Server Data Availability

By on

Click to learn more about author Dave Bermingham.

Today, organizations are buying cloud infrastructure services rather than on-premises hardware and software. For those companies running mission-critical SQL server environments, cloud vendors promise geographically distributed cloud data centers that make it easy to configure SQL server instances for high availability (HA) and/or disaster recovery (DR). This might be a good value proposition if your organization is setting up a new SQL server environment, or your hardware is nearing end-of-life (EOL). But what if you are heavily invested in a well-tuned, on-prem SQL server system that is servicing data to and from users? Moving everything to the cloud at once without the cloud experience may not seem prudent or even necessary to continue delivering ongoing user access to critical application data.

However, moving to the cloud is not an all-or-nothing proposition. You can easily mix on-premises and cloud infrastructures to create a hybrid. For example, you can retain some operations on-premises while moving others to the cloud, creating a strategic opportunity to limit data risk and improve financial and operational performance. To ensure your solution can deliver effective HA/DR support, take a look at your options.

Balance Risk and Reward

Look at the cloud as if it were a remote data center containing standby server(s), ready to take over at any time. The on-prem “node(s)” failover to their counterpart nodes in the cloud. There are two common ways to do this.

One solution is to use SQL Server’s multi-node Always On availability group (AG) feature. Another is to apply Windows Server Failover Clustering (WSFC), which sets up multiple nodes in a failover cluster. Both approaches offer data protection because your SQL server is in two different locations. However, there are some challenges.

If you use the basic AG feature of SQL Server Standard Edition, you can replicate only one node to a second node. If your on-prem AG was already configured with two nodes, your cloud-based SQL server would be a third node, which is not supported. You would have to upgrade both your on-prem and cloud-based instances of SQL Server to the Enterprise Edition (2012 or later). That would be an expensive proposition.

WSFC requires you to think differently about data storage. Windows Server failover clusters share data — typically on a storage area network (SAN), which all the individual cluster nodes can access. However, there is no SAN option in the cloud. How would your primary on-prem SQL server with its SAN work with the secondary SAN-less cloud?

The workaround is a hybrid configuration with on-prem and cloud-based infrastructures. In this configuration, you can failover from one node to another on-premises and replicate storage to a third node in the cloud for DR protection. 

A simple, cost-efficient solution is to create a SAN-less failover cluster using SIOS DataKeeper to replicate all the data on your on-prem storage to the local storage attached to your cloud-based VM. If a disaster strikes, your on-prem site will immediately failover to your secondary SQL server node in the cloud for as long as necessary. This solution works with both SQL Server Standard and Enterprise editions.

By planning and understanding the risks associated with any HA/DR solution, including the hybrid model, you will position your organization to better secure and protect data and its availability.

Leave a Reply