Building a hybrid SQL Server infrastructure

The geographic distribution of cloud data centers makes it easy to configure SQL Server instances for high availability (HA) and/or disaster recovery (DR), but what good is that to you if you’re already invested in a well-tuned SQL Server system that’s firing away on all cylinders on-premises? The idea of ensuring higher levels of availability or easily adding a disaster recovery site may sound very attractive, but the thought of moving everything into the cloud to achieve those ends may give rise to cold sweats and nightmares.

But here’s the thing: The cloud is not an all-or-nothing proposition. You can mix on-prem and cloud infrastructures to create a hybrid infrastructure. Some parts of your solution will be on-prem, while other parts will be in the cloud. Ensuring that your hybrid infrastructure can deliver the availability support that you want, though, requires an awareness of both the differences between your on-prem and cloud configuration options as well as an understanding of how to bridge those differences.

Feet on the ground, head in the clouds

Let’s assume you want to continue to use your on-prem SQL Server configuration as your primary SQL system. It ain’t broke, as they say, so why move it? But if you want to protect your organization from operational downtime or infrastructure loss that might occur in the event of a catastrophe—a hurricane, for example, or a major earthquake that compromises your on-prem infrastructure—you can put another instance of your SQL Server configuration in the cloud, where it is unlikely to be affected by the local disaster. If a catastrophe takes your on-prem infrastructure offline, the infrastructure in the cloud can take over.

One way to look at the cloud, in this scenario, is as if it were a remote data center in which you have a standby server, ready to take over in a moment’s notice. There are two fundamental ways to do this. You could configure a multi-node Always On Availability Group (AG), a feature of SQL Server itself, or you could use Windows Server Failover Clustering (WSFC) to configure a multi-node SQL Server failover cluster instance (FCI).

While the former approach is specific to SQL Server and the latter is generic to Windows Server, both approaches enable you to deploy SQL Server on cluster nodes running in geographically separate locations and both orchestrate failover from a primary node (on premises, in this scenario) to a secondary node (in the cloud) in the event of a catastrophe.

Hybrid cluster configuration challenges

The challenges you’ll encounter if you view your hybrid architecture this way are twofold. First, you need to ensure that the cloud-based instance of SQL Server can access the data that your on-prem instance has been using, and how you do that depends on the approach you take.

Copyright © 2020 IDG Communications, Inc.

Source link