Solved

SQL 2012 Cluster + Always On

Posted on 2013-11-14
14
1,201 Views
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?

Thanks!
0
Comment
Question by:DELAGIO
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 76

Accepted Solution

by:
arnold earned 167 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
0
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 167 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.

http://msdn.microsoft.com/en-us/library/ff929171.aspx

Regards Marten
0
 
LVL 76

Expert Comment

by:arnold
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.
0
 
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.
0
 

Author Comment

by:DELAGIO
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?

Thanks!
0
 
LVL 76

Expert Comment

by:arnold
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.
0
 
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:

http://technet.microsoft.com/en-us/library/bb522786.aspx

Alternatively, you could look into some kind of ETL process (or log shipping) to a secondary location and then report from that location instead.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:DELAGIO
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.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 166 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.
0
 

Author Comment

by:DELAGIO
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.
lab-aaon.PNG
0
 
LVL 76

Expert Comment

by:arnold
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.
0
 

Author Comment

by:DELAGIO
ID: 39661549
Well,

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.
lab-aaon2.PNG
0
 
LVL 76

Expert Comment

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

Author Comment

by:DELAGIO
ID: 39663263
Thanks to everybody.

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

Regards.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
OfficeMate Freezes on login or does not load after login credentials are input.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now