Kevin Remde's IT Pro Weblog
IT Pro Resources
IT Pro Evangelist Blogs
“Hey Kevin.. How do I get high availability out of my SQL Server?”
Cluster. Shared Storage. Windows Failover Clustering supports that.
“But… I want my SQL Servers running in Windows Azure. And I don’t have shared storage in “the cloud”. But I still need the benefits of clustered high availability.”
Wow.. you sure want a lot. Thankfully, I have a great solution. SQL Server 2012 has a new high availability feature called SQL Server 2012 AlwaysOn. With AlwaysOn configured, you can have two or more (up to 5) complete copies of a database that are maintained by each of the participating SQL Servers. Each SQL Server could be hosting the database on it’s local storage, or at some other location (SMB 3.0 file share? You bet!), but from the perspective of SQL Server 2012 AlwaysOn, it’s a copy of the database served up by each of the SQL Servers. The SQL Servers in turn are nodes in a Windows Server Failover Clustering cluster.
“And, because it’s not a cluster using shared storage, I can run these SQL Servers up in Windows Azure Infrastructure Services as Virtual Machines?”
Yes! Windows Azure is a great place to run these! As long as you have an Active Directory domain controller available, your servers can be cluster nodes in a Windows Failover Cluster, and SQL Server 2012 AlwaysOn can be configured.
“Sounds terrific, Kevin! How do I get started?”
You’re in luck! There’s are two very detailed step-by-step walkthroughs on how to build an example up in Windows Azure. One describes how to build the machines manually, and the other actually uses PowerShell and other command line scripting to configure everything. In the end you have 4 servers:
The examples also walk you through how to create the required Azure networks, the Availability Group for your machines, the configuration of the cluster, and the creating and configuration of a database that is highly available.
Here are the links:
For my own learning, I used the scripted option. Unfortunately, some of the commands listed needed to be tweaked a little bit to work with the most recent version of the Windows Azure Powershell module, and there were several typos in the text. But once I worked out the kinks, I was able to consistently create the machines and configure the cluster and the SQL Servers.
Here are my servers in Windows Azure…
And here’s looking at my Availability Group “AG1” on one of the SQL Servers…
And looking in the Failover Cluster Manager I can see the clustered “AG1” service, currently being served by “CONTOSOSQL1”.
And I can manually move that clustered service…
…so that it’s failed-over and now being served by “contososql2”.
TRY IT: I’ve created and uploaded a .txt file containing my modified snippets of PowerShell used to configure my Windows Azure subscription and to create the storage, the networking and the four Virtual Machines. YOU CAN FIND IT HERE. I strongly suggest that you change the file extension to .PS1, and open it with the PowerShell Integrated Scripting Environment. Read the notes at the top, and only run the specific segments at the time they’re asked for in the tutorial.
For the official details about AlwaysOn Availability Groups in SQL Server, check out the main documentation page on TechNet.
John Joyner did a very good write-up on SQL Server 2012 AlwaysOn for TechRepublic. Read his article: SQL Server 2012 AlwaysOn: High Availability database for cloud data centers
Don't forget to sign up for your free 90-day trial of Windows Azure, to start working with SQL Server 2012 AlwaysOn!
And you can find all of the articles in the complete "20 Key Scenarios with Windows Azure Infrastructure Services" series HERE.
What is Microsoft smoking?
We take an easy to configure and low complexity beauty like database mirroring and deprecate it.
Then we add the need for domain controllers, clusters, complexity, licensing, give it a fancey name, and call it better?
I'm one of many who don't agree.
Great feedback, Kwen. I can't speak to the "what we're smoking" part of your question, but the "call it better" part has to do with the always-on aspect; not having to fail-over with any kind of downtime if the other copy needs to be used.
I sincerely appreciate you sharing your opinion!