Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Implementing a SQL Server Cluster in Azure


By:   |   Last Updated: 2016-06-02   |   Comments (10)   |   Related Tips: More > Azure

Problem

Microsoft Azure is becoming a widely used platform for SQL Server implementations and Microsoft continues to add new offerings for these cloud based options.  It has taken time for companies to adopt cloud based solutions for their SQL Server applications, but the tide is changing and implementations are increasing.  One of the requirements for many SQL Server environments is the need for high availability with minimal downtime. Although Azure has many built-in fault tolerant features, having a high availability solution for SQL Server is still a necessity. Windows Server Failover Clustering (WSFC) has been and remains to be the primary means of implementing a high availability solution and Microsoft continues to add more features to make clustering more robust and easier to implement.  One of the downsides to WSFC is that it requires shared storage which is not an option with Azure based virtual machines.  So how can you get the benefits of Azure along with the benefits of Windows Server Failover Clusters when implementing SQL Server on Azure?

Solution

As mentioned, Microsoft Azure offers several options for SQL Server such as SQL Database, SQL Data Warehouse, SQL Server Stretch Database as well as the ability to implement SQL Server using Azure virtual machines.  Each of these solutions provides different benefits, but using Azure virtual machines gives you the most flexibility as well as the ability to manage your SQL Server instances similarly to how you manage your on-premises servers.

Since we need a solution that provides the most options and flexibility to implement a high availability SQL Server solution, we will be discussing how to build a Windows Server Failover Cluster for SQL Server using Azure virtual machines.  As stated, one of the requirements for WSFC is the ability to access shared storage which is not an option in Azure, but by integrating a solution like SIOS DataKeeper Cluster Edition into the mix we can build a SQL Server cluster using Azure virtual machines.  SIOS DataKeeper Cluster Edition is a Microsoft Azure Certified product which means this is a fully supported Microsoft solution.

Implementing a SQL Server Cluster in Azure

The great thing about using a Windows Server Failover Cluster for SQL Server on Azure is that if you already know how to implement and manage a Microsoft cluster there is very little to learn.  You build and manage the cluster like you would if the cluster was on-premises.  There are a few steps that are different to prepare your storage, but once the storage has been configured using SIOS DataKeeper Cluster Edition the cluster build and management is similar to what you are doing today.

Here are the high level steps:

  • Provision the virtual machines
  • Add the Failover Clustering feature to the virtual machines
  • Create a file share witness
  • Install DataKeeper and setup the storage

SIOS DataKeeper allows us to get around the need for shared storage, by creating disk volumes that are replicated between each node in the cluster. The data in these volumes is synchronized using real time, block level replication. The data therefore exists on all nodes in the cluster, which also eliminates the possibility of losing the data with a single shared storage source.

failolver cluster in the cloud

Once the volume or volumes have been created on the cluster nodes using SIOS DataKeeper, this storage becomes available within the Failover Cluster Manager as available storage.

After the storage has been configured, the following steps remain:

  • Install SQL Server on the first cluster node using the "New SQL Server failover cluster installation option". When you go through the setup of SQL Server you will see the available storage that has been configured using SIOS DataKeeper.
  • Install SQL Server on the second cluster node using the "Add node to a SQL Server failover cluster" option.
  • Configure the Azure Internal Load Balancer, so clients can connect to active node.

Conclusion

These are the high level steps to implementing a Windows Server Failover Cluster for SQL Server on Azure.  As you can see, implementing a SQL Server cluster on Azure can be done quite easily using SIOS DataKeeper.  Take some time by learning more about SIOS DataKeeper by referring to the links below. 

Next Steps

Review these resources:

MSSQLTips.com Product Editorial sponsored by SIOS Technology Corp., makers of SIOS DataKeeper Cluster Edition.



Last Updated: 2016-06-02


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 30, 2016 - 11:10:43 AM - Dave Bermingham Back To Top

SIOS DataKeeper works either from within an Azure region from Fault DOmain to Fault Domain or across regions or even hybrid from on-prem to Azure. SIOS DataKeeper is strickly a bring your own license software model so you will need to purchase a license first.


Friday, September 30, 2016 - 10:10:53 AM - Darryl Matthews Back To Top

 

 Nice article as I'm looking to set up a cluster in Azure for DR but would like to do HA as well.  The SIOS works seemlessly across different regions within Azure as well?  Do I have to go to SIOS and purchase somethig through them or is it all just billed through Azure?  


Tuesday, September 27, 2016 - 2:03:46 PM - Dave Bermingham Back To Top

Steve,

You are correct that reindexing and anything that causes blocks on the disk to change are going to have to be replicated, thereby introducing some overhead when doing synchronous block level volume replication. The key to minimizing any additional write overhead is to ensure the network has minimal latency and the intent log (bitmap file) is stored on low latency high speed disks.

The blocks on the source and target need to be identical, so unfortunately a simple handshake wouldn't suffice in this situation. There are some tradeoffs between block, file and SQL based replication solutions, so it is good to understand the differences when picking the right solution for your HA DR requirements.


Tuesday, September 27, 2016 - 11:36:45 AM - Steve Back To Top

Hi Greg,

Reindexing large tables and batches are sure to lag.  It's a shame just the commands aren't being replayed rather than copying blocks of data. Then all that would be needed is a metadata handshake to acknlowdge success/failure and the converge nodes.

Steve


Tuesday, September 27, 2016 - 11:09:12 AM - Greg Robidoux Back To Top

Hi Steve,

The data is replicated between all nodes in the cluster using block level replication.  The actual database files are being replicated, so all objects in the database for all databases are replicated.  As far as how in-synch each node is depends on the network bandwidth for the actual replication.

-Greg


Tuesday, September 27, 2016 - 10:09:15 AM - Steve Back To Top

Thanks Greg. Confirmed that BYOL is our scenario.  Now I wonder how in-sync the replica(s) are.  Is T-SQL mirrored at each replica or does data actually move?


Tuesday, September 27, 2016 - 9:37:00 AM - Dave Bermingham Back To Top

Steve,

Licesning concerns should always be cleared with your Microsoft licensing specialist, but my understanding is that in a "bring your own license" (BYOL) situation in any environment from cloud, virtual or physical, as long as you have software assurance the passive node of a SQL Server FCI does NOT need to be licesnsed. However, if you are renting SQL from your cloud provider instead of BYOL you wind up paying for SQL Server on both nodes, regardless of whether you use it in an active passive cluster or not. I'm hopeful that this will change some day, but for now it generally makes financial sense to BYOL if you are building a 2 node SQL Server FCI and plan to run it for at least a year or so.

 


Monday, September 26, 2016 - 2:01:34 PM - Steve Back To Top

Thanks Greg.  Good to know.  Does straddling Azure zones with nodes increase licensing costs or does one server license suffice?


Monday, September 26, 2016 - 1:26:59 PM - Greg Robidoux Back To Top

Hi Steve, yes this approach will work using different geo zones.

-Greg


Monday, September 26, 2016 - 10:20:56 AM - Steve Back To Top

Thanks for this clear explanation of Aure clustering.  Does it support nodes in separate geo zones for better business continuity?


Learn more about SQL Server tools