SQL Server Failover between AWS and Azure using SQL 2014 Standard Edition
Posted by Rob Risetto on September 11, 2016
In Part 1 of this series I described how to create a three node, multi-site SQL Server 2014 cluster across two AWS servers
and an Azure VM using SIOS DataKeeper Cluster Edition to mirror the clustered data disk. On the positive side the article presented a neat and robust HA/DR solution for AWS cloud implementations, the downside though is the cost of the SQL Server 2014 Enterprise Edition used in the solution. So what do you do if you can only afford SQL Server 2014 Standard Edition but still want a HA/DR solution that spans AWS and Azure or maybe your On-premise network?
Simple, you create a two node SQL Server 2014 Standard Edition cluster in AWS, with servers in separate AWS Availability Zones and therefore different subnets, again using SIOS DataKeeper Cluster Edition to mirror the clustered data disk to both of the AWS servers and also to a Standalone (non clustered) SQL Server in Azure.
But wait you ask, Standard Edition doesn’t support multi-subnet SQL clusters, well interestingly in SQL Server 2014 Standard Edition multi-subnets clusters are supported. Well there was no blocking of two subnets in the SQL Server Clustering installation.
As in Part 1, you follow steps 1 to 8 to create a SQL Server 2014 two node cluster in AWS (using Standard Edition in this case), make sure you bind a secondary private IP address for the Windows Cluster network name and another for the SQL Server Cluster network name on each AWS server using private IPs in the same subnet range of the server. As mentioned in Part 1, this will facilitate the correct IP address to be assigned for the Windows cluster and SQL Server Cluster network names at failover in AWS. Basically you will have automatic failover between the AWS SQL Servers.
The Azure VM is a standalone SQL Server (Standard Edition also) and therefore is not part of the cluster, however, the Disk F that contains the system and users databases of the AWS SQL cluster is still asynchronously mirrored to the Azure VM. Of course the failover to Azure will require manual steps like
- Using the DataKeeper GUI failover the mirror to Azure VM.
- Attached the user databases located on Disk F to the standalone Azure SQL Server.
In addition, you will need to manually maintain logins and SQL Agent Job definitions on the Azure SQL VM. This is not as neat as the 3 node SQL Enterprise Edition cluster but the manual effort is worth it based on dollars saved in licensing.
Fail back is a no brainer, using the DataKeeper GUI, resync the mirrored disk back to the AWS servers and when in sync perform a mirror failover to the primary AWS server and then bring the SQL Server cluster resources online.
In summary, the solution provides the benefit of a high availability SQL Server cluster using the cost effective SQL Server 2014 Standard Edition, with the added benefit of the eliminating single point of failure of the data disk. It also provides a relatively straight forward (but manual) failover to a disaster recovery site, in this case Azure and thereby also eliminating the “All eggs in one basket” scenario.