Re: 4 Queries about DB clustering

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 14:26:01 -0500

All drives will need to be visible to all hosts for failover. Drive letters
are assigned cluster-wide for shared resources. The easiest way is to
pretend you are building a multi-instance stand-alone system where SQL Data
and Logs from multiple instances cannot intersect on the same LUN. Also
remember to add a drive for MSDTC and for the Quorum. Don't forget to leave
room for expansion.

Example
C for local boot, Z for local CD-ROM (Old Novell habit)
Q for Quorum, M for MSDTC
K,L for SQLInstance1 data and logs respectively
R,S for SQLInstance2 data and logs respectively

lather, rinse, repeat. 

-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kamal Hassan" <KamalHassan@discussions.microsoft.com> wrote in message
news:5D8375C6-8269-46FC-B619-BD75E0F8A6CD@microsoft.com...
> I have a question for Geoff.
>
> I have a similar situation where I plan to install/configure
multi-instance
> cluster.
>
> My question is
>
> a) as we plan to use/assign drive letters what care shoud be taken for
> multi-instance cluster (3 active 1, passive/standby)?
>
> Thanks.
>
> Kamal.
>
>
>
>
> "Geoff N. Hiten" wrote:
>
> > You are getting closer.  More comments inline.
> >
> > -- 
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Pankaj" <pankajchitriv@rediffmail.com(donotspam)> wrote in message
> > news:1D8FA145-7AF2-4EBE-9FA1-FD9822233F81@microsoft.com...
> > > Hi Geoff.
> > >
> > > Thankyou very much for your help. It is just great. I really really
> > > appreciate it.
> > >
> > > Further, to summarise our discussion what I understood to implement
the
> > SQL
> > > Db Cluster is:
> > > 1. I need to have SCSI array, a Fiber array,  or a SAN as a shared
device
> > > "on which I'll install SQL Server Database".
> > The database(s) will live on the shared storage.  SQL binaries will live
on
> > the local disks of each cluster host.
> > >
> > > 2.  Say in a 2 node cluster, both the nodes would be typically Windows
2k3
> > > Server systems, with the SQL Server Instance reference (SQL Client) to
the
> > > shared device on which SQL Server is installed.
> > A basic two-node SQL cluster will have four systems visible.  There are
the
> > two physical host nodes.  There is the cluster vrtual server itself.
There
> > is the SQL Server virtual server.  Each virtual system must have a
minimum
> > of one unique IP address, one unique network name, and a physical disk
> > device located on the shared array.  You access the SQL server by
connecting
> > to the virtual server by name or IP address.
> > >
> >
> > > 3. Say in Active / Passive mode, only 1 Win2k3 system will be utilized
and
> > > in case of faliure, the other win2k3 server node will be active.
> > >
> > Correct.  The current terms are single-instance and multi-instance.
Each
> > new instance of SQL will have its own disk(s), IP address(es), and
Network
> > name.  Once an instance is installed, you can set the preferred host
order
> > to determine the "normal" home host for that instance.  You can have up
to
> > 16 instances on a single cluster.
> >
> > > 4. I need to install SQL Client on both the nodes and Sql Server on
shared
> > > device.
> > >
> > First you install MSCS (Clustering).  Windows 2003 has an excellent
> > clustering wizard that makes it very difficult to create a non-working
> > cluster.  Then you install a clustered instance of SQL server, running
the
> > install CD from the node that currently owns the disk resource you wish
to
> > use as the first SQL disk.  You can assign additional disks after
> > installation.  The installer writes the executable files to each host
node
> > you choose as part of the installation.  This includes server and client
> > components.
> >
> > > 5. Just wondering whether stored procedures of SQL DB will be executed
on
> > > the  shared drive or on the Node. Does the shared drive have
processing
> > > capabilities?
> >
> > The binaries are on each host node.  The memory and CPU resources come
from
> > the host node.  The data is stored on the shared array.  SQL uses a
"Shared
> > Nothing" model so after installation you can run any instance from any
host
> > node, regardless of whether any other node or instance is running.
> >
> > >
> > > With this queries clarified , I'm ready to shoot :-).
> > >
> > > I really appreciate your help and request your comment on above
queries.
> > >
> > > Regards,
> > > Pankaj A. Chitriv
> > >
> > >
> > >
> > >
> > >
> > > "Geoff N. Hiten" wrote:
> > >
> > > > Server clustering is available on Windows Server 2003 Enterprise
> > Edition.
> > > >
> > > > "Single Data Store" is not a service, it is a description.  All data
> > resides
> > > > on a single logical device physically connected to all host nodes.
This
> > can
> > > > be a SCSI array, a Fiber array,  or a SAN.  Ownership of this device
is
> > > > arbitrated by the cluster service.  Microsoft SQL DB clustering
exists
> > as a
> > > > failover technology only and has no distributed computing
capabilities.
> > It
> > > > is not a scale-out technology.  One server owns the entire database
and
> > no
> > > > other server can access the data without moving the entire SQL
instance
> > to
> > > > another host.  Active-Passive and Active-Active are not accurate
> > > > descriptions of the current SQL clustering technology but are
somewhat
> > > > misleading holdovers from an earlier version.
> > > >
> > > > Again, clustering is for availability and does not allow multiple
> > > > simultaneous connections to the data store from different host
nodes.
> > > >
> > > > -- 
> > > > Geoff N. Hiten
> > > > Microsoft SQL Server MVP
> > > > Senior Database Administrator
> > > > Careerbuilder.com
> > > >
> > > > I support the Professional Association for SQL Server
> > > > www.sqlpass.org
> > > >
> > > > "Pankaj" <pankajchitriv@rediffmail.com(donotspam)> wrote in message
> > > > news:4A51BB01-D028-4B3B-9244-12EFA8469987@microsoft.com...
> > > > > Thanks Geoff.
> > > > >
> > > > > "Server Cluster" is a inbuild clustering service provided by
Windows
> > 2003
> > > > > Server.
> > > > >
> > > > > Just wondering, whether that Single Data Store is a seperate
windows
> > > > system?
> > > > > If so what if that system breaks down? Is that the case that we do
DB
> > > > > clustering only to distribute the processing capabilities?
> > > > >
> > > > > What I understood by Single Data Store means is there will be a
system
> > on
> > > > > which SQL DB will be created and there would be multiple servers
> > accessing
> > > > > the same database. But this cannot be called as DB clustering as
we
> > don't
> > > > > have to configure any VIP to access the single data store. Can you
> > clarify
> > > > > this please?
> > > > >
> > > > > Thanks a lot  again for your answers.
> > > > >
> > > > > Regards
> > > > > Pankaj A. Chitriv
> > > > >
> > > > > "Geoff N. Hiten" wrote:
> > > > >
> > > > > > Answers Inline
> > > > > >
> > > > > > -- 
> > > > > > Geoff N. Hiten
> > > > > > Microsoft SQL Server MVP
> > > > > > Senior Database Administrator
> > > > > > Careerbuilder.com
> > > > > >
> > > > > > I support the Professional Association for SQL Server
> > > > > > www.sqlpass.org
> > > > > >
> > > > > > "Pankaj" <pankajchitriv@rediffmail.com(donotspam)> wrote in
message
> > > > > > news:847A2BE3-74E1-4B9E-9D61-EEE40D117155@microsoft.com...
> > > > > > > Hi, Below are few queries:
> > > > > > >
> > > > > > > 1. Say for 2 nodes cluster, Do we need to have 2 copies of
> > > > Databases( 1 on
> > > > > > > each node)
> > > > > > No.  Data is on shared storage and is controlled by one node at
a
> > time.
> > > > > > >
> > > > > > > 2. Hows does the data Synchronization between different nodes
of
> > > > databases
> > > > > > > takes place?
> > > > > > There is no synchronization.  There is a single data store.
Access
> > to
> > > > this
> > > > > > store is arbitrated by the cluster software.
> > > > > > >
> > > > > > > 3. In Active/Passive mode, does the passive node database is
> > exactly
> > > > in
> > > > > > the
> > > > > > > same state (in terms of data) as that of Active?
> > > > > > >
> > > > > > Clustering is failover not scaleout technology.
> > > > > >
> > > > > > > 4. Is 'Server Cluster' specifically for DB clustering ?
> > > > > > I am not sure what you mean by "Server Cluster"?
> > > > > > >
> > > > > > > Thanks in advance,
> > > > > > > Pankaj A. Chitriv
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Relevant Pages

  • Re: SAN drive config for SQL Cluster
    ... Take SQL Server offline, make SQL Server dependent on the new drive and ... existing cluster and then read the SP readme on how to apply SP's to it. ... but the new DRIVES does not appear Enterprise Manager. ... >> cluster in SAN. ...
    (microsoft.public.sqlserver.clustering)
  • Cant get new LUN/Logical Drive visible from within SQL Server on cluster.
    ... We have had a working SQL Server 2000 SP3 on two node MS ... Windows 2000 Advanced Server with MS Cluster servcie working for over a year ... The shared Q: and E: drives are actually two seperate LUNS from a SAN ... Configured it as basic disk, drive letter F: It show up in Disk Manager ...
    (microsoft.public.sqlserver.clustering)
  • Re: What do I need to cluster SQL under Windows 2003
    ... The Cluster service has another IP address and each Virtual ... SQL Server has yet another IP address. ... Enterprise Edition of SQL is the only edition that has the ability to create ... And when you move a clustered instance from one host to another, ...
    (microsoft.public.sqlserver.clustering)
  • Lost data drive from 2003 cluster
    ... Here's the setup: ... however I cannot connect to cluster via Cluster Administrator. ... connect to my virtual instances via SQL Server Enterprise Manager. ... both the Quorumand Datadrives. ...
    (microsoft.public.sqlserver.clustering)
  • 2003 Cluster Lost Data Drive
    ... Here's the setup: ... however I cannot connect to cluster via Cluster Administrator. ... connect to my virtual instances via SQL Server Enterprise Manager. ... both the Quorumand Datadrives. ...
    (microsoft.public.windows.server.clustering)
Loading