Solved

2012 sql cluster(alwayson) needs more sql instances ?

Posted on 2014-07-22
2
203 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
2 Comments
 
LVL 26

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

20 Experts available now in Live!

Get 1:1 Help Now