[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


SQL 2012 Cluster + Always On

Posted on 2013-11-14
Medium Priority
Last Modified: 2013-11-20
Hi experts!

I have a demand for a project like this:

I need to set up a SQL server 2012 cluster together with the SQL 2012 Always On.

This is a 2 Server scenario combining this 2 technologies (cluster + always on).

Anyone knows if this is possible? Has anyone done it? Does Microsoft support this scenario?

Question by:DELAGIO
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
LVL 80

Accepted Solution

arnold earned 668 total points
ID: 39650169
Is your intention to setup a geographically dispersed setup using two servers only?

Or do you have a dispersed setup with existing domain spanning two locations with shared data replicating?
Where each server has two instances of sql running.
A cluster has sql running on one node only.

Always on feature requires two running sql instances.

Have you looked at http://technet.microsoft.com/en-us/library/ff878487.aspx
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 668 total points
ID: 39650351
Both technologies use the Windows Server Failover Cluster (WSFC) feature. So it's not possible to set them both up. You'll have to choose.


Regards Marten
LVL 80

Expert Comment

ID: 39650415
The two technologies are not mutually exclusive.  The warning is not to use the failover cluster manager to manipulate/change items from the availability groups.

There is an article  "step-by-step: sql 2012 creating a SQL server always on.

The allwayson grouping has the option for a single server or a cluster. But that would mean you need at least four servers. (two per cluster at each location) with the allwayson overlay.

Are you planing solely for a disaster recovery or do you need both locations to write into their local cluster while the data/changes are replicated between them?

If you could detail what it is you have and what it is you want to achieve, that might make things clearer.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 28

Expert Comment

by:Ryan McCauley
ID: 39651356
The two technologies aren't mutually exclusive at all, and it causes some confusion that they both use Windows Server Failover Clustering as the underlying technology, but consider it this way:

 - AlwaysOn controls how clients access the database and which instance they get their data from
 - Traditional clustering controls where the instance(s) run (on which server)

They have different purposes, and you configure different options in each, but generally:

 - AlwaysOn can be used to load balance by sending read-only queries to secondary servers. It's actually more like historic SQL Database Mirroring, but allows databases to move from primary to mirror as groups (and if you're familiar with Oracle at all, it delivers functionality similar to Active Dataguard).
 - Clustering is used for hardware failure protection, and the instance only runs in one place at a time. However, it allows you to take a node offline to perform maintenance with minimal interruption, or if there's an unexpected hardware failure, it brings the services up on another node within 15-20 seconds or so, allowing users to reconnect.

They can be used in combination to maximize the benefits of each, but as Arnold mentions, you'll need at least four servers if you're going to use AlwaysOn with clustered instances. Traditional SQL Clustering requires low latency, high-speed shared storage (like a SAN), and AlwaysOn can provide some of the same benefits of clustering in a case where you don't have a high-performance SAN available. However, they really deliver separate feature sets that address different (though similar) challenges.

Author Comment

ID: 39657470
Actually the 2 server are in the same data center.

My problem: The sql server hardware is not handling well the load, so I wonder if it would be possible to keep the cluster and add a always on, so the secondary server (passive node) would be the "reports" server", all reports would be demanded from there.

Is it clear now?

LVL 80

Expert Comment

ID: 39658023
The always on will be just an overlay resource to a single "companent" the active node in the cluster.

You could install another instance to setup a active/active cluster.
The difficulty you will run into given your current setup barely has resources to handle the current load, when the other node fails all tasks will revert to the single active node doubling up on the resource/spacing+reporting.

An option you may want to consider is to offload the reporting either via a logshipping with a snapshot creation from a "recovering" database.

or if your upgrade cycle is near, look at replacing/upgrading the existing cluster servers, then keep them for your reporting functionality with the always-on.

You are in a more difficult situation if the servers were recently purchased and are underspeced.

if you have off-peak and you can use the SSRS to generate reports that will be emailed.
i.e. at 3am run reports, at 4am run reports. etc.
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39659697
Using AlwaysOn to push your read-only "reporting" queries to a secondary server is definitely an option, especially as a failure would just push them back to your main production server (a cluster, it sounds like), so you're not worse off for it.

However, I've got some concerns that you feel your new servers are underpowered, and implementing additional complexity (AlwaysOn) to compensate for that isn't ideal - while that might be the right move for other reasons, I'd encourage you to look at adding more memory, additional disks, or something else to improve performance. Memory is cheap, especially when compared to the expense of your time spent troubleshooting it, so I'd encourage you to buy all you can afford.

If you're concerned about the load that your SSRS reports are generating specifically, you may want to look into caching your reports overnight, which could address your concerns as well without a large change in infrastructure. Once you set up caching, you can schedule the reports to run overnight to the null destination so they finish processing, and then they're available throughout the day:


Alternatively, you could look into some kind of ETL process (or log shipping) to a secondary location and then report from that location instead.

Author Comment

ID: 39659932
Well, actually the reports aren't from the report services, they are from the application itself.

The servers (2 nodes) are 2x 8 core processors with 512gb ram, I can't upgrade them right now.

Guys, I appreciate all the help but right now I need 2 short answers please:

1- Can I add a always on feature to a 2 node only sql cluster?

2- Is this scenario supported by Microsoft?

Thanks a lot.
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 664 total points
ID: 39660667
That's quite a bit of power (at least from the CPU/RAM perspective) - I didn't intend to derail the troubleshooting, but just wanted to mention it as a potential concern. In this case, it doesn't appear those are the cause of your performance issues, but I'd also check to ensure that your disks aren't saturated.

You can't add features to a failover cluster instance - once it's installed, the installer won't let you add or remove any features. That said, AlwaysOn isn't an installer feature in this sense, but is more akin to replication or mirroring, and can be added to any database server that supports it (and is fully supported by Microsoft). To do this, you'll need two independent SQL instances - the first can be your clustered instance, but you'll need a second instance as well (as the secondary portion of your AlwaysOn implementation).

Have you attempted to walk through the AlwaysOn configuration and are running into issues, or are you just asking about the feasibility? I don't see any issues with adding this configuration and getting the advantages AlwaysOn has to offer.

Author Comment

ID: 39660696
Ryan thanks for your attention so far.

I am doing a lab to see if this is possible.

I came across a problem: the drive letter "s:" is a clustered disk and off course it is used by both nodes, who is the active one... The always on feature demands that the replica server needs to have the same drive\letter so the replication can occur and its not possible, because if I create the same path for the replica server (who also is the passive node) when a failover occur it will fail.

Did you get the picture?

Take a look at the attachment.

PS: This is not a lab test only, I am trying it in a lab environment so I can replicate it to a production one.
LVL 80

Expert Comment

ID: 39660981
Your always on needs another system/sql configuration to which the clustered instance will replicate.
Is the app custom internal or vendor product.

Tunning the database/query.

While the report is from the application, if it is needed, ssrs could be setup to pull/create a similar report.

Author Comment

ID: 39661549

I finally have an answer for this thread:

It is not possible to have both SQL CLUSTER AND ALWAYS ON with only 2 nodes.

First it complained about the path for the replica, that should be the same path as the primary node. I "bypassed" that by adding (for testing) a local drive, assigning the same letter and creating the same path as the database is in the primary node, but in the end it did not allow me to keep going, take a look at the attachment guys.
LVL 80

Expert Comment

ID: 39661571
I believe my comment http:#a39650169 pointed that out.

Author Comment

ID: 39663263
Thanks to everybody.

I will split the point among the relevant participations of this thread.


Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question