Solved

2012 sql cluster(alwayson) needs more sql instances ?

Posted on 2014-07-22
2
228 Views
Last Modified: 2014-07-31
In regular (2008 SQL Cluster) MS SQL Cluster, as an example we had 3 nodes, but two instances on two nodes separately, and third node was empty (no instances) and ready to take a failed instance, as needed. (the nodes did not need a distinct SQL instance)

but in the new Always on featured 2012 version, it seems like you need an instance on all 3 nodes, other than the cluster instance. (so 4 sql instances instead of one, previously)

Does that not imply 3 times the resource?
0
Comment
Question by:25112
[X]
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
2 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 40213654
You are talking about 2 different things:

1. SQL "classic" cluster where you can have active-passive or active-active configuration. In active-active you can use the 2 servers at the same time and each of them is ready to take the functionality of the other one if it fails. In the active-passive configuration only 1 instance is live and the other becomes live only if the primary fails. In both of these situations you have one thing in common and that is the sharing of the storage device. Basically only the SQL server host computers are distinct but they share the same storage so that the database files are only once, so there is no data synchronization involved. In this scenario the storage unit still represents a single point of failure, which means if that fails the whole SQL cluster is compromised.

2. In the SQL cluster in Always On context both the machines(computers) and data storage are supposed to be separate. There is no sharing involved, which means you will have 2 distinct servers from top to bottom. One of them will be the primary and th eother one the secondary and the database file will be separate but kept in sync by the AO availability group.

However you can combine the 2 configurations with Always On. You can have this:

1. Distinct SQL instances residing on distinct machines each with its own storage. You need at least 2 in this scenario to create a cluster. One will be the primary and the other one the secondary. The database files between primary and secondary will be kept in sync all the time and if the primary fails the role will be switched to the secondary. You can have multiple secondaries, up to four, and all of them will have separate data storage and they all will be kept in sync.

2. You can have a "classic" cluster with 2 SQL servers that share the data storage combined with a stand alone instance on a separate machine with separate storage. All 3 nodes will be still part of the same cluster but in Always on the 2 nodes in the classic cluster form single AO node, which cam be set to be primary, and the third node will be the secondary. The classic node will still failover and remain primary if one of its instances fail and switch the the other.  If the shared storage fails then the whole configuration will be switched to the secondary to become the primary.

So if you want to use a classic cluster with 2 nodes with the AO then you keep that and you will have to add only the third separate node to be the secondary.

If you want to keep it simple only with 2 nodes in a cluster with no shared storage then you need 2 distinct instances. I found this way simpler and still enough to ensure high availability.

You can find a lot of references on the net by just searching.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 250 total points
ID: 40214432
Zberteoc's answer provides all the detail you need, but I wanted to provide another reference to clarifying the terminology - I wrote an article a while back that tries to clear up some cluster terminology, and a follow-up on my blog that explains some misconceptions about Active/Active and what AlwaysOn means for that:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/A_11134-Shedding-some-light-on-Windows-clustering-terminology.html

http://www.trycatchfinally.net/2012/09/cluster-terminology-what-activeactive-actually-means/

I hope it clears things up a bit - again, not exactly an answer to your question, but the confusion around the terms might make it worth reading through.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

627 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