I'm about to upgrade and consolidate a group of SQL Server 2008R2 to a single SQL Server 2012. I want have high availability and looking for the different options. The number of databases is fairly high (150+) so DBMirroring is out of the question.
Now I'm looking at "AlwaysOn Availability Group" and "AlwaysOn failover cluster" and I can't really figure out what way to go..... maybe even more options are available.
clustering might be a good way to do things, but it's really annoying to have a big power server doing nothing at all but waiting for the primary server to fail.
Is there any way to do real active/active clustering in SQL Server (real load balancing)?
Microsoft SQL Server does not support a 'real' load balancing scheme out of the box. AFAIK, this is still true with SQL Server 2012. (Someone will enlighten me if I'm wrong.) It doesn't matter if we are talking about database mirroring or AlwaysOn or clusters.
(In order to hammer that point home, MS seems to call SQL Server clusters "SQL Server failover clusters" lately. Pedantics.)
If you want to load balance your databases, you have to do the hard work yourself with some sort of sharding, federation or replication. (Note that federation (by views) has been in the product since SQL Server 2000, it just wasn't very popular.) And, of course, that would mean modifying either your databases or the apps themselves, which is almost always either too much work or violates your vendor agreements. With 150 databases, it's just that much more insurmountable.
You can have an active-active cluster, but the thing is that you would have to carefully distribute your databases on your nodes to divvy up the load. With 150 databases, this might be more granular than if you just had five databases, but if you have one database that is a ton of load and 149 that are light-weight or rarely used, you might still find one machine bogged down and the other isn't. And, some databases are busy sometimes and hardly busy at other times. Which means that everything might come down to when a user decides to run some heavy process.
Of course, you have to be able to support all of that load on a single node when you fail over, for whatever reason, even if it is something mundane like patching Windows. If you only patch during known slow traffic periods, that's great. If you don't have slow periods, or if the failover occurs because the hardware actually has a fault, the other node might not take the load and your users will be out of luck. If you think about it like that, having the second machine "doing nothing" isn't quite so irritating. At least you know that it will take all of the traffic that the primary usually does.
AlwaysOn Availability groups also support read-only replicas - this would allow for load balancing for reads and as far as I can tell this is the first time such a thing has been made available with SQL Server 2012.
2012 also supports the classic cluster active/active that is often referred to (what happens if you add more than 2 nodes, active/active/active yuck!), but bear in mind that has limitations in that it's providing 2 instances and as soon as one goes down the other remaining server gets potentially slammed with double the load or more.
In addition to the load-balanced read-only replicas, Availability Groups provide much more flexibility particularly with a third asynchronous witness server that can be in a remote DR site.
One other thing you could do is set up multiple sql server instances on each node, and cluster each instance. Split up your databases across each instance. Each instance itself is active/passive, but if you run each instance on a seperate node, you effectively distribute the load. You still need to ensure that if you do fail over, a single node can support the resourc