Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com May 3 '12 at 19:23

This question came from our site for professional and enthusiast programmers.

    
Please tag more carefully. this is not a cluster-analysis question. Replaced this with load-balancing –  Anony-Mousse May 3 '12 at 18:28
add comment

4 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

If you want to load balance, since SQL Server doesn't have a true active/active cluster support at the sql service level, can you achieve roughly the same effect if you were able to virtualize the SQL Server on something like VMWare esx, where you can thne spread the virtual server over a number of physical hosts? To sql server it looks like a single machine, but in reality it's spread across a larger infrastructure. If high availability is also a concern, you might still need to cluter the virtual machine, because the virtual machine is still a point of failure, but a second virtual machine is less costly then a physical one, and if it's passive, it won't consume a lot of resources from the host.

Not sure if this is an effective solution or not...

share|improve this answer
    
Don't know what version of ESX you have in your company but in vSphere 5.1 a single vm is still bound to running on one host at a time. vm's spanning multiple hosts still do not exist in modern virt software. Think you have read to much cloud marketing stuff ;) –  user157958 Feb 7 '13 at 9:42
    
Possibly. I guess that's why my "answer" was more formatted like a question. –  Jeremy Feb 7 '13 at 22:43
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.