Mutiple Instance on SQL Cluster

Question

Mutiple Instance on SQL Cluster

Andrew Collins

Ten Centuries

Points: 1369
More actions
September 5, 2007 at 4:49 am

#118573

We currently have a 2 node Active/Passive SQL Cluster with 3-4 databases running(this will increase). But we now have to add approx 10 new databases which we would like to keep seperate for admin reasons.
What is the best way to do this, do I install a 2nd instance on the cluster, can this be done? or do i need to setup another virtual server. Do I need to have more nodes to do this or would the existing 2 be enough.
I am new to Clustering and virtual servers.

Thanks
Andy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike-405927 SSC Rookie Points: 35 More actions · Answer 1

I did set up a cluster with two instances, so it is possible. What to think about is to keep them totally separated, each instance will have its own set of disks. When running two instances on a cluster it is possible to run one instance on each node, to balance the load. This works fine if each node is well equipped so they can host both instances in case one of them goes down.

Regards

Micke

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 2

Thankyou

How would having 2 instances affect performance? The current cluster is used by 1000's of people and is critical to company.

Would a 2nd virtual machine with its own default instance be better for performace. Would I then need another node?

thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

What Micke is advocating is what is called an "active/active" cluster. During normal times, one node runs one of the instances, the other runs the second instance. Assuming you don't have a whole lot of cross-database chatter across instances, this can be a VERY fast config, because you have 2 beefy machines (who would each technically be sized to handle BOTH instances in the case of a disaster). It's also a popular config with finance dept., since you don't have a machine "just sitting there" (which makes since to us techie folks, but looks like money flying out the window to an accounting type).

Clustering is a rather big deal, so it's often restricted to mission-critical, uptime-required applications. Performance alone might not be enough to justify clustering, especially active/active, where you have to license SQL for 2 nodes (instead of invoking the "passive node" clause of the SQL licensing in a more traditional clustering active/passive model).

You won't get the same performance boosts out of a single-machine setup as if you have 2 separate machines, but this then turns into a cost vs performance analysis.

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

dbamark SSCommitted Points: 1694 More actions · Answer 4

Yes, it would make a difference on performance. I would go to an active\active set up. When we set up active\active cluster, we plan for add the total memory needed for each node and put that total memory on both nodes. In the event of a failure, memory will not be an issue. This is usually the bottle neck on failover. I'm not sure if any thing special is needed to go from an active\passive to an active\active cluster, other than possibly upgrading the hardware and licenses.

I do believe this would be your best bet.