Strategies for protecting SQL Server in the cloud
- Published: Friday, 27 February 2015 10:06
By Tony Tomarchio
SQL Server is a business critical application that requires high availability protection, regardless of where it is deployed. Even in the cloud, you still need to protect SQL Server from downtime if the cloud instance or the cloud provider fails. However, traditional solutions, such as shared-storage clusters may not be practical or even possible in the cloud. SANless software can provide enterprises with high availability and disaster recovery protection for SQL Server in the cloud without the limitations of shared storage. This article will look at strategies for how this can be achieved.
Most cloud providers allow you to deploy your applications across multiple separate and redundant data centers. However, they do not offer shared storage (i.e., a SAN), which is required to support traditional Microsoft Windows Server Failover Clustering (WSFC) across these computing resources. The solution is to use SANless software instead of a shared storage to create a high availability cluster using WSFC with local storage. You can deploy a highly available SQL Server Failover Cluster Instance (FCI) across multiple windows instances in the cloud. When a failure occurs, WSFC will coordinate the SQL Server failover and restart SQL on another node in the cluster. SANless software as an ingredient in your WSFC environment in a cloud deployment eliminates the need for shared storage. Instead of a SAN, the software keeps storage in all nodes synchronized (identical) using real time, block level replication. Replication can be performed in either synchronous or asynchronous modes. Cluster nodes can also be located in separate cloud regions for added protection. The local, replicated storage is presented to WSFC as if it was shared storage. Using SANless clusters is a fast, easy way to deploy SQL Server in a highly available configuration in the cloud while continuing to use Windows Server Failover Clustering.
To provide high availability and disaster protection for SQL Server in the cloud, you may also want to configure a failover cluster with nodes in different geographically separate areas (e.g., AWS or Microsoft Azure Regions). One way to achieve this is using the AlwaysOn Availability Groups feature that is built into SQL server itself. However, Availability Groups requires SQL Server Enterprise Edition, which can be very cost prohibitive for simple two node deployments in this configuration. Unfortunately, Availability Groups also has a number of limitations you should be aware of: it isn’t compatible with distributed transactions, so if your application relies on the Microsoft Distributed Transaction Coordinator (MSDTC) you cannot use AlwaysOn Availability Groups because the instance ID of the server changes upon failover and the distributed transaction coordinator is not aware of the new instance ID. Additionally, Availability Groups only replicates user defined databases and NOT system databases (like Master and MSDB). SQL Agent jobs and SQL logons are not automatically synchronized and will not fail over as part of the Availability Group. Finally, Availability Groups introduces additional administrative overhead you might not want to deal with. Availability Groups are configured and managed at the database level, not at the SQL Server instance level. Therefore, administrators must reconfigure protection every time a database is added or dropped. Essentially, you are managing two separate instances of SQL and have to take great care that you keep their configurations in sync.
In these cases, SANless software can be used to deploy a SQL Failover Cluster Instance (FCI) to provide high availability failover for the entire SQL instance, even across different subnets, using cost-efficient SQL Server Standard Edition. You can build a cluster using SANless software to enhance WSFC enabling failover of SQL Server Standard Edition across cloud availability zones or regions. This can be the best choice for companies that want full high availability and disaster recovery protection of the entire SQL instance; gaining the affordability of SQL Server Standard Edition, maximum application compatibility, and reduced administrative overhead.
For many years, WSFC has been used to provide high availability and disaster recovery protection for SQL applications in traditional on-site environments. Until recently, managing site failures has been very complex and expensive, requiring large investments in specialized hardware and software as well as the availability of a second data center site. The cloud offers an attractive and cost-effective second site in which to locate a cluster member and to handle a failover when the local site fails. However, because traditional clusters require shared storage between all cluster nodes across local and cloud resources, this has not been a practical architecture for traditional clusters. In these cases, SANless software can be used to create clusters that provide high availability across local and cloud resources and extend an on-premises SAN-based or SANless cluster for SQL Server to the cloud for disaster recovery protection without the cost of a remote recovery site or SQL Server Enterprise Edition licenses. SANless software can be an easy, cost-efficient high availability and disaster recovery solution for companies that are continuing to manage their SQL environments on physical, private cloud, or on-premises VM environment.
Tony Tomarchio is director of Field Engineering at SIOS Technology Corp. Tony is responsible for defining and delivering technical pre-sales services, support and best practices to SIOS customers, prospects and partners. Tony has more than a decade of experience providing systems management and high availability solutions to enterprise customers.