Active/Active or Active/Passive

Hello,
We are going to design our SQL Server databases on either Active/Active or
Active/Passive of Windows 2003 clusters.
Has any one seen real time risk and their real experiences of using
Active/Active clusters?
Thanks, John
The only real issue is when one node dies and its SQL instance(s)
failover to the other node so a single node has multiple instances
running on it. You just need to make sure each node has enough
resources (CPU and memory primarily and to a lesser extent NIC
throughput and SCSI controller (or SAN HBA) capacity) to support both
instances simultaneously.
It also depends on whether it's acceptable to your business to run both
SQL instances at a degraded performance level until you can get the dead
node back on its feet (assuming the business doesn't want to fork out
the cash to have more CPU & memory in each node in order to adequately
run both instances on one node).
Personally, where I work we run 4 production clusters (each with 2 SQL
instances on it) and an off-site DR cluster (with 2 SQL instances on
it). I can't remember a problem in the last 5 years that we've had on
any instance on our clusters that wouldn't have also happened if we'd
only had a single instance on each cluster (the issue were all OS level
or hardware level issues - to be honest, our stand-alone SQL boxes were
always more stable but obviously didn't have the failover capacity if
something went wrong). However, we tend to run cluster nodes with a
little resource overhead in them in case of a failover.
*mike hodgson*
http://sqlnerd.blogspot.com
John wrote:

>Hello,
>We are going to design our SQL Server databases on either Active/Active or
>Active/Passive of Windows 2003 clusters.
>Has any one seen real time risk and their real experiences of using
>Active/Active clusters?
>Thanks, John
>

Active/Active clustering solution for SQL SERVER

Basically, I need to have real active/active cluster
solution on SQL server(any MS platform).
The windows failover clustering tech is share nothing
clustering , so can not do the load balancing stuff(
distribute transaction traffic between servers).
Only product I know on the market is PolyServ's Matrix
server, any suggestions from microsoft?
Thanks
David Liu
MCSE/MCDBA
DBA
www.moneris.com
The purpose of MS Cluster Servers is to provide high availability not load balancing for SQL Server. If you require both you need multiple clusters as a federation of SQL Servers. Another option is a single 4-node cluster with 4 SQL Instances in a feder
ation configuration. As your 4-nodes reach peak utilitisation, scale up the four servers. To explore how federated solutions work start by searching BOL for "federated database servers".
If you only need read-only access to SQL Server data you could build a solution using replication. As your load increases add more subscribers to handle the load.
|||As I know, The federated database is only support
partition data, do not support share database( exactly the
same database, as oracle's real application cluster).
In a word, failover and load balance at the same time;
and can quickly scale out by simply adding a new server.
From BOL, microsoft say do not support this type of
clustering.
Thanks for your reply
David Liu
>--Original Message--
>The purpose of MS Cluster Servers is to provide high
availability not load balancing for SQL Server. If you
require both you need multiple clusters as a federation of
SQL Servers. Another option is a single 4-node cluster
with 4 SQL Instances in a federation configuration. As
your 4-nodes reach peak utilitisation, scale up the four
servers. To explore how federated solutions work start by
searching BOL for "federated database servers".
>If you only need read-only access to SQL Server data you
could build a solution using replication. As your load
increases add more subscribers to handle the load.
>.
>
|||Thanks David for your interest in Polyserve !
Unfortunately, SQL server itself is a shared nothing application,
which cannot benefit from a clustered file system.
A clustered file system allows all the nodes in the cluster to see
(share) the same file with full read/write access,
but unlike IIS / CIFS applications which can use a clustered file
system to load balanced access (scale out) across all the nodes in the
cluster,
a SQL server instance data and log files is not shared with other SQL
servers instances on other nodes.
But, there are some benefits to gain from the use of a shared file
system for SQL HA
1) a clustered file system is mounted on all the nodes, this
guarantee a seamless transition of the instance in case of disruption,
rather than a typical "failover" scenario with a forced unmount, etc.
2) Large, heterogeneous, node count (16 node SQL Server clusters that
can mix 2p, 4p, and 8p servers)
3) No drive letter limitations
4) Easy storage management
5) Easy management of raw data file for ETL . I.e. you can store all
your
exports in one folder which is seen by all the nodes.
6) Off host backup. All the instances can backup into one location,
and one node can copy the backup to tape.
For more information : http://www.polyserve.com/sql
Tomer Meshorer
Database Engineer
Polyserve
"David Liu" <david.z.liu@.moneris.com> wrote in message news:<197f901c41cb5$ff95c440$a501280a@.phx.gbl>...
> Basically, I need to have real active/active cluster
> solution on SQL server(any MS platform).
> The windows failover clustering tech is share nothing
> clustering , so can not do the load balancing stuff(
> distribute transaction traffic between servers).
> Only product I know on the market is PolyServ's Matrix
> server, any suggestions from microsoft?
> Thanks
> David Liu
> MCSE/MCDBA
> DBA
> www.moneris.com
|||SQL Server does not support multiple machine access to SQL Server database
files. This is a protection mechanism which ensures that processes from
different machines can not step on each other and introduce corruption into
the data files.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com