September 24, 2007 at 8:07 am
Hi Guys,
I fully understand SQL Server active/passive clusters. I fully understand Oracle RAC. I don’t really understand the use of SQL Server active/active clusters. Is it just two separate active/passive clusters on the same physical hardware? If a failover occurs from node1 to node2 I have two instances running on node2. Does this mean my application is divided up between two instances? Or is it really meant for separate applications?
Thanks in advance
September 24, 2007 at 8:15 am
That's roughly my understanding (certainly how we used it). Active/Active is essentially two Active/Passive instances "shared" between two nodes. As to whether it should deal with single applications or multiple - because the two instances would have to talk to each other using something like linked servers, I would tend to split things between the two instances according to how much "chatter" there is between databases. In other words - I'd keep the ones that need to talk to each other a LOT in the same instance.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 24, 2007 at 9:01 am
The way we do it is that we have a Dev instance on one server in the cluster and then our Test / QC instances on the other server in the cluster. This makes the cluster active/active. If one server fails, then the instance(s) will get sent over to the other server.
I don't know how to do a true load balancing active/active cluster with SQL Server, though. That would probably involve some sort of merge replication with two "production" instances on two seperate machines, but I'm just guessing.
September 24, 2007 at 9:09 am
You can't load balance with Active Active. You're just running two two Active/Passive, which is a great way of putting it. If there's a disaster of some sort, then you are sharing resources on that one server.
If you need something to balance performance, I'd look at the Polyserve product or even the ParAccel products. They're unique solutions that address some of the resource issues.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2007 at 9:38 am
active active probably provides the best ROI for the tin - you just have to make sure each node is powerful enough to run both nodes sql server(s)
You can load balance with SQL Server, but it's tricky but doesn't need any third party tools - I'd love to set it up for a test but i don't have the middle tier knowhow to do it. The how to is how technet site works - ask microsoft.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 25, 2007 at 7:36 am
..but Microsoft really should have some equivalent of Oracle's RAC, ie. true active/active database cluster. They can't match Oracle in that area at the moment.
😀
September 25, 2007 at 8:32 am
...well - they DO talk about the concept of setting up "Federated Databases", so as to partition load across multiple SQL servers. Not automated, and does take a fair amount of effort to set up, but once set up - it is a form of load balancing. Of course, one might argue that if you're going to build something that big and that performance driven - you probably would want to do something other than turnkey.
Anyway - Federated Databases start here:
http://msdn2.microsoft.com/en-us/library/ms190381.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2007 at 8:50 am
Clustering Technology on MS SQL Server is IMHO the most hyped least bang-for-the-buck thing people tend to use. The expense you incur with all that specialized hardware is really not worth it and now with database mirroring eliminating such expense and providing automated client redirection plus providing very fast failover times will make clustered solutions almost unjustifiable, to say the least! There are "counted" cases in which clustering make sense but even then there is no real "sharing" or "load balancing" involved.
To look at the problem as a two set of Active Passive gives you the correct unhyped figure of what it really means 🙂
Just my $0.02
* Noel
September 25, 2007 at 9:25 am
Well - we're now talking about two distinct concepts: High availability and "load balancing". In many ways - the two have opposing priorities, so talking about them as if they were equivalent is dangerous at best. Actually - confusing the two is often why clustering is seen as a "waste of money".
A standard MSCS setup of SQL server requires no specialized hardware (an extra NIC, and a "quorum" drive, but those are hardly specialized), and requires nothing odd to "redirect the user" (that happens automatically). Any time that's lost during failover is the time it would take for the DB server to figure out (and possibly fix) anything damaged during the failure (undo/redo,etc...).
Data mirroring only allows redundancy of the storage layer - doesn't help much when dealing with something having zapped your server's power supply (or both if you have them). Clustering just adds another dimension to redundancy (if built correctly of course).
As to whether it gives you the "bang for you buck" - well that's a business decision. If the app can afford to be down for a while - then no.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2007 at 10:25 am
agreed we're talking too different topics - no idea what Oracle RAC is. Clusters's protect the tin and hopefully give a minimum of down time - don't always think of just a two node cluster, it's possible to use one node to provide failover for multiple nodes - that's good availability whilst not having too much tin sitting doing nothing.
Load balancing is something else and I agree sql server doesn't have this as yet, but I truthfully wonder how many people really need to stretch to multiple servers to satisfy a request. It's easy for read only but much more tricky for a full blown oltp solution.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 25, 2007 at 11:31 am
Contradicting an earlier post, PolyServe has nothing to do with load balancing. It's just a different kind of clustering (shared everything vs. shared nothing).
Eddie Wuerch
MCM: SQL
September 25, 2007 at 11:51 am
Sorry Matt, but I could not disagree with you more in some of your replies 😀
A standard MSCS setup of SQL server requires no specialized hardware (an extra NIC, and a "quorum" drive, but those are hardly specialized), and requires nothing odd to "redirect the user" (that happens automatically). Any time that's lost during failover is the time it would take for the DB server to figure out (and possibly fix) anything damaged during the failure (undo/redo,etc...).
Well to setup a SQL Server cluster you have to purchase a CLUSTER CERTIFIED SYSTEM. Not just parts or bits and pieces that you put toguether. That, is very expensive!
Redirecting the User is NOT handled by the clustering software, you must code retry logic in the app. The failover time in a cluster solution takes longer than in a Mirroring solution.
Clustering does requires a quorum drive which if down the whole thing goes down and they tryed to ease that single point of failure by introducing " majority voting" but it is even more expensive to do that.
Data mirroring only allows redundancy of the storage layer - doesn't help much when dealing with something having zapped your server's power supply (or both if you have them). Clustering just adds another dimension to redundancy (if built correctly of course).
Another Mistake ... Database mirroring can be configured with Witness servers which will see the failure on the primary (ANY KIND network, disks, power, etc) and will bring the secondary up "automatically" and what's more, the TDS driver now has built in support in the connection string for client redirection without any code changes.
As to whether it gives you the "bang for you buck" - well that's a business decision. If the app can afford to be down for a while - then no.
The app CAN be down for a while with Clustering too while the failover is taking place and to top it all any problem in the quorum implies that the whole thing is hung. Mirroring takes away "all" those issues using cheaper harware and a lot less effort. Clustering should in my opinion used when "multiple" database must failover at once ( which is something Mirroring does not guaranties)
I am not talking about load balancing; which RAC does do. I was simply comparing Mirroring vs Clustering for High Availability
Cheers
* Noel
September 25, 2007 at 2:25 pm
Well - it doesn't help that I didn't notice the word DATABASE in database mirroring. That being said:
Well to setup a SQL Server cluster you have to purchase a CLUSTER CERTIFIED SYSTEM. Not just parts or bits and pieces that you put toguether. That, is very expensive!
All due respect - but I don't run even my test box on just about any bits and pieces I put together. Do you really run critical stuff on scavenged hardware? After all - the goal is high availability after all (for a Microsoft product?) :hehe:
The most expensive part of a cluster is the disk subsystem, but we'd be tying into a SAN for performance anyway.
Keep in mind as well - the "auto failover" option of mirroring with witness involves THREE servers, each licensed for SQL Server.
Try running on High Safety mode with less than optimal hardware and a heavy load. I'm also not incredibly fond of a scenario where the mirror could die quietly and "noone cares" (the witness' job is ONLY to care about the primary) Otherwise - in High Perf mode - you're likely to "lose data" in the failover.
Clustering should in my opinion used when "multiple" database must failover at once ( which is something Mirroring does not guaranties)
Well - touche there. I just haven't been in a situation where my environment on the Cluster is one single DB. One single instance of our environment was keeping our SAN @87% utilization, so running a mirror for 100+ DB on yet another SAN wasn't something the CFO was willing to part with.
As to failover times - I've found them to be related to what transactions needed playing. Yes - it takes a little longer.
Look - sounds to me that we're discussing fairly different scenarios, and actually - appropriately configured in each case. The two are not interchangeable in my mind.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2007 at 2:53 pm
Matt,
I never meant to say that you are running clusters on cheap hardware :D. I just mentioned that the entire system not just the SAN must be certified to work as a whole in orther to get any kind of support from M$.
There are many Apps that use a single database and there are many others that use "multiple" databases.
You can handle with database Mirroring multiple APPs that use single database backend.
In other set of things the witness server could be FREE because any edition can be used as the witness. For those people that can Implement mirroring with their currrent apps I would recomend it hands down over clustering because Clustering is not doing anything but being more expensive for them.
Cheers 😀
* Noel
September 25, 2007 at 10:39 pm
One thing that has always driven me a little nuts is that many folks hear of "active/active" and immediately assume that an "active/active" cluster = horizontal scalability (NOT) = Oracle RAC (NOT).
Yes, horizontal scalability can be done (e.g. mirrored federated database servers) but it's not for the faint of heart.
Joe
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply