Avatar of rbmacct
rbmacct
 asked on

SQL Server 2008: Switching from Active/Passive to Active/Active

Hi,

We have an Active/Passive SQL cluster environment running on Windows Server 2008 and SQL Server 2008

My question is what will I need to do to change it to Active/Active?

Thanks
Windows Server 2008Microsoft SQL Server 2008

Avatar of undefined
Last Comment
rbmacct

8/22/2022 - Mon
SOLUTION
rbmacct

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
rbmacct

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rich Weissler

I think you might have answered your own question... but in case it isn't clear:
To make you Active/Passive cluster into an Active/Active cluster, you add a second SQL Instance to the cluster, and when everything is working 'normally', keep the instances on different nodes.
Ted Bouskill

An Active/Active cluster is actually two Active/Passive clusters.  Let's say you have
SQLServerA
SQLServerB

First you setup an ActivePassive SQL cluster like this:

SQLServerA\SqlCluster1 - Active
SQLServerB\SqlCluster1 - Passive

Then you do this

SQLServerA\SqlCluster2 - Passive
SQLServerB\SqlCluster2 - Active

The benefit is you can run services on both servers concurrently, however if there is a failover condition that the load of both instances will be placed on one server.  Considering that it should be a rare occurrence that is a good trade off.

A worse case scenario would be if both instances were running at 100% capacity then one instanced failed over so both instances were running on one server which would then cause both instances to degrade severely as one server tries to handle the full load.  However, I would never allow two servers to run consistently at a high load because you have no buffer for spikes.

If both servers are running at 60% or less then a failover occurs there will be some degradation but it would be tolerable.  I have used an Active/Active cluster successfully for a SharePoint 2010 farm where one instance hosted search databases and the other hosted the content databases.

There is another option.  Use two Active/Passive clusters in a 3 node cluster.

SqlServerA\SqlInstance1 - Active
SqlServerB\SqlInstance2 - Active
SqlServerC\SqlInstance1 - Passive
SqlServerC\SqlInstance2 - Passive

That way you have two fully function SQL servers with one server standing by for two clusters.
Mark Wills

Hmmm... I have normally stayed away from active / active relying on active / passive for failover clustering. Though... that is not to say it doesnt have a place...

Why are you wanting to replace active/passive with active/active ? What do you think you will be gaining ?

Some people think it is both high availability and load balancing, but it isnt...


worthwhile reading : http://www.mssqltips.com/sqlservertip/1554/sql-server-clustering-active-vs-passive/
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
rbmacct

ASKER
The links I posted describe and aswer my question.