MS SQL Server 2008 R2 Multi Instance Cluster


I'm planning to setup Multi-instance (Active/Active) Cluster using MS SQL Server 2008 R2 on Windows 2008 R2. The reason for this setup is to achieve better utilization of the hardware (since both the servers will be used to support database transactions).  

My intention is two have two instances in the cluster with one running on node 1 and other running on node 2 and they will failover to the other nodes in case of any issues. I'm trying to get some direction to achieve this, and I'd appreciate if somebody can point me to the right direction and hopefully provide some link if they've implemented the same setup.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

In effect you will have two clusters an application cluster per instance.
Make sure to allocate resources such that both can run on the same node
i.e. if you have a 16GB RAM, make sure to allocate the maximum of 7GB to each SQL instance.
Ryan McCauleyEnterprise Analytics ManagerCommented:
If you're looking for an actual step-by-step on the cluster configuration and sql cluster installation itself, I've always loved this walk-through:

In your case, you'll want to set each instance to have a primary node so that they stay on different nodes (when both nodes are available), and then they'll fail over to the "secondary" node when the instance's primary node becomes unavailable.

We use an active/active configuration like you're describing, but have about 15 SQL instances on our cluster, with half set to have one node as the primary and half set to have the other node - that way they balance themselves when both nodes are available, but will fail over to the secondary node when one goes down unexpectedly.

this link shows windows cluster install and page 3 is where sql install starts.

After one node is primary, you can go to the secondary node and do installs the other way round.

please check on the requirement for all ip addresses related to a cluster need to be in the same subnet. Installs can fail if they are not. However, I am not sure if this is now different for win2008(r2)/sql2008(r2)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

You can install on the same node and shift them as you see fit.  Designating which node will have which instance active is part of the configuration and does not require the installation to occur on that node.

Getting better utilisation out of an existing A/P cluster is a good thing, but dont overlook the reason why a cluster was created in the first place...
Ryan McCauleyEnterprise Analytics ManagerCommented:
Steve makes a good point - when you have active instances on both nodes in your cluster, there are a few things to remember:

Don't use more memory for all instances combined than you have in any one node - that way, you don't run into sudden memory pressure and instability when all instances fail over to a single node.
In an active/passive configuration, you only need to license the SQL Server on one side, and the passive node doesn't require a SQL Server license. However, if you're going to keep instances active on both nodes, you'll need to license both as well - could get expensive.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.