You can setup an "active/active" SQL cluster between your 2 boxes.
"Active/active" is somewhat misleading as it implies load balancing, but that
is not quite accurate. If you want both nodes of a SQL Server MSCS cluster to
be working at the same time, what you have to do is install 2 instances of SQL
in the cluster. You then split up the databases between the 2 instances. What
you achieve by doing this is getting more utilization of your hardware, while
keeping the benefits of the cluster (failover in the event of a hardware
problem). Obviously, if both instances are running on one server, performance
will be impacted, but you will still be up and running.
It is important to understand some caveats about this setup, though. Pardon
the bullets:
*
If you only have one database, this solution is not going to help you.
*
It is not true load balancing. At any given time, one instance is
going to be hit harder than the other. How busy a given server is depends on
how much activity there is on the SQL instance it is hosting. You may still
wind up with one hammered server and one (mostly) idle one. This is completely
dependent upon how you split the databases up and how they are utilized.
*
A lot of folks find this setup confusing. You have 2 server names, 2
cluster names, and 2 SQL instance names*. It can be tricky for some folks
(like your DBAs) to wrap their head around. (* The first instance doesn't have
to be named, but the rest do. We have found it to be actually more confusing
if the first instance isn't named.)
*
If the databases on different instances are referenced in views, et al,
your DBA will have to take additional steps to link them (don't know too much
about the details here).
Bill Mayo
________________________________
From: Cameron Cooper [mailto:[email protected]]
Sent: Friday, February 19, 2010 10:22 AM
To: NT System Admin Issues
Subject: RE: SQL Cluster
Sensitivity: Confidential
We have two SQL servers connected to a SAN box and are clustered now. I
believe they are setup in a Failover Cluster at the moment. The issue we've
been having is that the sqlservr.exe process is spiking the CPU on the 1st
server in the cluster. From what I have read this is normal. However this has
been slowing down our database to a point where it's almost unusable and end up
restarting the server.
Suggestions?
_____________________________
Cameron Cooper
System Administrator | CompTIA A+ Certified
Aurico Reports, Inc
Phone: 847-890-4021 | Fax: 847-255-1896
[email protected] | www.aurico.com
From: Mayo, Bill [mailto:[email protected]]
Sent: Thursday, February 18, 2010 4:02 PM
To: NT System Admin Issues
Subject: RE: SQL Cluster
Sensitivity: Confidential
What are you trying to accomplish? I imagine that you could technically setup a
NLB cluster for SQL, but it would logically mean that the data is read only and
is synchronized in some other fashion. If you are looking for a multi-server
SQL solution where the data is shared, then you are doing a MSCS cluster. In
that type of situation, any given database can only be accessed by one SQL
Server at a time. Windows does not permit having 2 servers access the same
disk resource simultaneously.
A NLB cluster means that you are spreading requests to multiple servers, all of
which are active. This is commonly used for clustering web servers.
A MSCS cluster means that you are utilizing shared storage and have 2 or more
servers with resource groups spread between them. Only one server can access
any particular resource group at a time. This is commonly used for SQL Server,
and is the Exchange 2003 and earlier method for clustering as well.
Bill Mayo
________________________________
From: Cameron Cooper [mailto:[email protected]]
Sent: Thursday, February 18, 2010 4:56 PM
To: NT System Admin Issues
Subject: RE: SQL Cluster
Sensitivity: Confidential
Is there documentation out there that shows how this is setup?
_____________________________
Cameron Cooper
System Administrator | CompTIA A+ Certified
Aurico Reports, Inc
Phone: 847-890-4021 | Fax: 847-255-1896
[email protected] | www.aurico.com
From: Sean Rector [mailto:[email protected]]
Sent: Thursday, February 18, 2010 3:51 PM
To: NT System Admin Issues
Subject: RE: SQL Cluster
Sensitivity: Confidential
Yep...the Data & Log drives are on my iSCSI SAN.
Sean Rector, MCSE
From: Cameron Cooper [mailto:[email protected]]
Sent: Thursday, February 18, 2010 4:38 PM
To: NT System Admin Issues
Subject: SQL Cluster
Sensitivity: Confidential
Can you setup two SQL servers to Load-Balanced Cluster? Or is this not
suggested for SQL?
_____________________________
Cameron Cooper
System Administrator | CompTIA A+ Certified
Aurico Reports, Inc
Phone: 847-890-4021 | Fax: 847-255-1896
[email protected] | www.aurico.com
Information Technology Manager
Virginia Opera Association
E-Mail: [email protected] <mailto:[email protected]>
Phone: (757) 213-4548 (direct line)
{+}
Virginia Opera's 35th Anniversary Season <http://www.vaopera.org> The One You
Love
Celebrate with a 2009-2010 subscription:
La Bohème <http://www.vaopera.org/html/currentoperas/opera1.cfm> | The
Daughter of the Regiment <http://www.vaopera.org/html/currentoperas/opera2.cfm>
| Don Giovanni <http://www.vaopera.org/html/currentoperas/opera3.cfm> |
Porgy and BessSM <http://www.vaopera.org/html/currentoperas/opera4.cfm>
Visit us online at www.VaOpera.org <http://www.vaopera.org/> or call
1-866-OPERA-VA
The vision of Virginia Opera is to enrich lives through the powerful
integration of music, voice and human drama.
________________________________
This e-mail and any attached files are confidential and intended solely for the
intended recipient(s). Unless otherwise specified, persons unnamed as
recipients may not read, distribute, copy or alter this e-mail. Any views or
opinions expressed in this e-mail belong to the author and may not necessarily
represent those of Virginia Opera. Although precautions have been taken to
ensure no viruses are present, Virginia Opera cannot accept responsibility for
any loss or damage that may arise from the use of this e-mail or attachments.
{*}
~ Finally, powerful endpoint security that ISN'T a resource hog! ~
~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
