AlwaysOn SQL Server 2012 - any option for true active/active?

Question

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)?

Solution

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.

Comments

[Admin]

Please tag more carefully. this is not a cluster-analysis question. Replaced this with load-balancing