SQL 2014 AlwaysOn Failover Clusters

We are looking at implementing a SQL cluster and we want to limit data loss if there is a failure.  I realize that using AlwaysOn Availability Groups is a better option, but it is a much more expensive option.  I know that Failover Cluster (FC) uses shared storage and so I will have a single point of failure on the storage.

My big question is if one of my servers fails will I have any data loss?  Another thing that I would like to know is in a FC can I have Server 1 the master of DB1 and Server 2 master of DB2?
George PerolliSenior Systems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The AlwaysOn feature doesn't need a SQL Server cluster but a Windows Cluster so the only shared resource will be the Quorum disk.
0
Ryan McCauleyData and Analytics ManagerCommented:
Just to clear up some terminology, Microsoft is now usin the term "AlwaysOn" to describe all of their high-availability clustering options. "Availability Groups" are the feature you're referring to (which is the next generation of Mirroring), and "Failover Cluster Instances" are the new name for what was previously commonly called a sql cluster. Both fall under the "AlwaysOn" umbrella, though.

Availability Groups can be a pretty expensive option - you need Enterprise Edition to use them, where you can do a two-node failover cluster with just standard edition (and add more nodes with enterprise). They do allow some great features, like balancing reads across multiple nodes and a quicker failover than traditional FCI (can be seconds instead of 30 seconds or so for an FCI), but they cost about 4x as much. Also, it's further complicated by the requirement for licensing of both sides of the cluster if you're going to have clients reading from each.

The data loss risk from a failure is the same with both solutions - if the active SQL instance in your failover cluster configuration (or the writable instance in your Availability Group) goes offline, all the transactions currently in process are rolled back and the clients have to reconnect. There's minimal risk of data corruption, as SQL Server is really good to keeping the files safe and recoverable, but you still lose the things that aren't yet committed. If you're concerned about the SAN as a single point of failure in your FCI, there are ways around that - log shipping, transactional replication, and other options that get your data to second physical location. Also, if you're setting up AG and then putting storage for both servers on the same SAN, you're going to have many of the same risks of a SAN failure. There is an upside to AG here, though - with FCI, you have only a single copy of your data and physical corruption can cause problems, where with AG each server has its own copy of the data and physical corruption of one copy of the data still leaves the other copy intact.

Not sure if this clarifies or muddies the waters, but I'm hoping it's helpful. If you've got a specific question given this additional detail, please share it and I'll contribute what I can.
0
George PerolliSenior Systems AdministratorAuthor Commented:
@Ryan

I have one big question after reading you answer.  If I use FCI do I only have to license one server?  Meaning if I have 2 x 8 core servers I only have to buy licenses for 8 cores?  If I use log shipping or transactional replication I am going to need licensing for the additional server, but I could license for fewer core right?  Then if one of the primary servers dies I can move the licensing.

I am trying to get as much protection as I can for the least amount of money.  Don't get me wrong, if we have to go Enterprise to get the protection and performance that the owner deems necessary then we will get it.  However if I do not have to spend the money, I won't.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan McCauleyData and Analytics ManagerCommented:
Agreed - getting what you need for the lower number of dollars is always preferable :)

I assume you've seen the feature list by edition, but here it is just in case:

https://msdn.microsoft.com/en-us/library/cc645993.aspx

Also, here's the SQL 2014 licensing guide:

http://download.microsoft.com/download/B/4/E/B4E604D9-9D38-4BBA-A927-56E4C872E41C/SQL_Server_2014_Licensing_Guide.pdf

I'm not a licensing expert, but here's my interpretation of the scenarios you're asking about - if you want to read them yourself, you're looking for page 14 and page 20.

Failover cluster instances - Nodes are passive only if you aren't querying them and they're not being used to support anything. You must license both nodes, even if the second node is passive. However, if you have active software assurance (about a 50% cost premium) on the licenses covering your active node, you can cover the standby node for free with no additional licensing

Replication - You can replicate your data to a second location for failover purposes and you're not required to license the standby location. However, you're only allowed to move a core license once every 90 days, so you can't fail over to your standby dataset more than once every 3 months. Again, this is relaxed if you have software assurance on these licenses, and they can be moved between servers at will, with no restrictions.

In either case, you can fully license the standby nodes and it removes all restrictions around what you're allowed to do (failing back and forth, reporting from the secondary, secondary backups, etc).

Again, that's my understanding - please read the guide if you want to get Microsoft's official language.
0
George PerolliSenior Systems AdministratorAuthor Commented:
I understood that in a FCI configuration you could not access the second node.  Is it possible to configure FCI so that 1 DB can be accessed from one node and another DB can be accessed from the second node?
0
Ryan McCauleyData and Analytics ManagerCommented:
You can install multiple instances of SQL Server in a failover cluster and place them each on different nodes - that would satisfy what you're looking to do. However, you can't place databases on other nodes by themselves, but instead the databases move between nodes according to the instance to which they're attached.

It might be worth reading through this post, which attempts to clear up what "Active/Active" means and might answer some of your questions:

http://www.airbornegeek.com/2010/10/t-sql-tuesday-11-the-misconception-of-activeactive-clustering/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.