We help IT Professionals succeed at work.

SQL Active / Active Clusters

Eric
Eric used Ask the Experts™
on
Hello,

Please forgive me if my questions seems to naive but;

I am pretty familiar with Windows 2008 R2 Clustering. I have read articles, books and saw some videos. But in every one of them were about active/passive clustering.

I am installing SQL 2008 Cluster on two node and i want to make it active/active.

I know that if you configure clusters by default, it is going to be active/passive. Install the SQL 2008 on the first node and then add failover cluster node on the second.

But How can I configure Active / Active 2 node cluster on Windows 2008.

Can anybody send me a link or a book showing how to configure active/active clusters especially SQL 2008.

Thanks and regards

Fiyona
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
You simply do this by installing two instances of SQL and let first run on the one node, and the other on the second node.
Note that you can only have one default instance per cluster, all additional instances have to be named instances.
Make sure you don't "overload" the nodes; one single node will still have to be able to handle both instances, otherwise you won't have a cluster. A cluster is meant for redundancy, NOT for load balancing.
Hi Fiyona

Only oracle had a featue call RAC - real applicatino cluster that is active/active   - 1 instance
that is available on 2 phoiscal conputers
and if you have 2 instances or more they can be available from all the nodes as you wish

In SQL SERVER you have a simple active/pasive  for each instance,  so somtimes poeple say they got active/active cluster - they mean they had more then 1 instance.  one is active/pasive on 1 node
and the  2nd is active pasive on the second node.
but i called is   active/pasive    active/pasive   and not active active.

sorry if you are disapointed.
but as far as i know RAC cluster makes a lot of problems
(such as making you restart all the NODE only becase 1 problematice node in the cluster)

Good luck!
Guy H
EricSystem Admin

Author

Commented:
Hello GuyHochman & ObDA

Thanks for the answers,

Actually, this is what I was thinking. There are lots of Terminology going around in IT. I believe there are no real active /active cluster on windows.

So if I understand correctly, You can only configure MS Cluster on Windows 2008 as Active/Passive. E.g node and disk majority. Then it is matter of how to install SQL service on it.

If you install default instance of SQL 2008 on the first node and let second node configured as passive , then you have active/passive.

But if you install an instance of SQL on the first node and configure second node as passive node ,,and installing another instance of SQL  on the second node and configuring first node as passive. Then you ahve active /active clusters as they say ?  

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
You don't install SQL "on a node"; a cluster is made out of "resource groups", and each resource groups can be owned by a node in the cluster. In your case, you'd have four resource groups (Quorum, MSDTC, SQL default instance, SQL named instance), and each of these groups can be owned by either node (and can be moved to another node). How you distribute the groups is up to you.
In a W2k8 cluster, this group concept is somewhat hidden by the new management console, but under the hood, it still works like in W2k3 or earlier (open a command prompt and enter "cluster group" to see them).
Start here maybe:

Getting Started with SQL Server 2008 R2 Failover Clustering
http://msdn.microsoft.com/en-us/library/ms189134.aspx

Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1
http://www.mssqltips.com/tip.asp?tip=1687
EricSystem Admin

Author

Commented:
Thanks ObDA

I think I am understanding. This has been an issue for me long time and couldn't find any complete ! resource.

Please bear with me a  little more:)) this is just for my curiosity as I have been hearing different stuff and want it to get it straight this time :))

If i want to configure 2 named instance ( or one default and one named instance ) on top of MS CLUSTER.

I already implemented  the steps described in http://www.mssqltips.com/tip.asp?tip=1687 before. when I was done, I had 3 cluster resource ( QUROM, MSDTC, and SQL deafult instance ) working fine.

So my question starts this point on , I have no problem understanding and implementing that article

If I want to configure second SQL instance,named, resource on the cluster which has already default SQL instance resource, Should I need to follow the same steps and then distribute the instance on nodes. Or that would require different implementation steps. That s what I want to understand

Can i also use same shared disk resource for both resources ?

And finally if cluster is for high availability, why are we using as load balance and compromising availability which is its real purpose

Thanks again




Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
You'll need an additional volume on the shared storage for the named instance. The installation works exactly the same way as the default instance, only you choose the name (and a different DB locaiton, obviously) along the way.
You can load balance with a cluster, but only to a certain point. In a two-node cluster, the combined load on both nodes may never be so high that a single node can't take it; at this point, the cluster is useless, you might just as well have two stand-alone machines.
The principle in a cluster is that if a resource fails on one node, its services will be taken over by another node.
EricSystem Admin

Author

Commented:
I see

Please let me make sure that I understand you correctly by asking ;

 If i have LUNs ( assigned from SAN ) ; E for DATA files and D for Logs and F for DTC  and Q for QUROM ( and Local C Drive for SQL binaries and Windows 2008 OS ) and if i configure the first instance to use E and D for data and log files.

Then do i need to assign another two volume for Data and Log files ? such as G and H to be used by second instance.

I think i am not able to use DAta and Log drive for both instance

Thanks

 

 ;;  
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Yes, you'll need two more LUNs to add another instance with separate drives for data and log. Drives can not be shared among resource groups.
EricSystem Admin

Author

Commented:
OKAY,,

one last comment and i am good to go for sure, thanks to you :)))))

As for DTC resource group, It will serve  to both instance . So There is no reason of creating second DTC resource group on another drive or is there ?
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
You couldn't even add a second DTC resource group ...
EricSystem Admin

Author

Commented:
Good..

Thanks very much for the information,,appreciated.

Fiyona