Solved

2012 sql cluster(alwayson) needs more sql instances ?

Posted on 2014-07-22
2
200 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

18 Experts available now in Live!

Get 1:1 Help Now