Re: 4 Queries about DB clustering

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

Date: Tue, 15 Feb 2005 15:13:59 -0500

You have a fundamental misunderstanding here. Instances are always on-line
but live on a single host node at a time. Host nodes may have zero or more
instances running at a single time. If a node (computer) goes down, the
instances it is hosting shift to another node.

How many SQL servers(instances) are you going to have? Remember, each
instance is an independent installation of SQL server. There is no sharing
of data files between instances. Disks are assigned to instances.
Instances may run on any of a selected set of host nodes, but each instance
can only occupy one node at a time. 

-- 
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:913AC317-6CE6-4CF8-98A3-71268BA7805D@microsoft.com...
> Geoff,
>
> Here is some additional info:
>
> Current LUN configuration:
>
> RAID5
> 101 = 100 GB - SQL Data for Instance1
> 201 = 100 Gb - SQL Data for Instance3 (assuming Instance 2 is Standby???)
> 301=  100 GB - SQL Data for Instance4
> RAID1
> 102= 28 GB SQL Trans Log (Instance 1)
> 202= 33 GB SQL Trans Log (Instance 3 & 4)
> 10  = 5 GB Quorum (Shared by all instances)
>
> Based on above, how would you setup/configure SQL Server 4-node (3 active
1
> standby) clustering? or do have any recommendation for the above setup
before
> setting up clustering.
>
> Again, THANKS VERY MUCH...
>
> Kamal.
>
>
> "Kamal Hassan" wrote:
>
> > First, manythanks for quick reply.
> >
> > In your suggestion is the SQLinstance1 and SQLInstance refer to as
ACTIVE
> > nodes with StandyBy Server or ?
> >
> > We are not using MSDTC do we still have to setup/configure? and what do
you
> > mean don't forget to leave for expnasion?
> >
> > Thanks a lot!
> >
> > Kamal.
> >
> >
> >
> > "Geoff N. Hiten" wrote:
> >
> > > 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: 4 Queries about DB clustering
    ... installation, you determine the preferred host order for each SQL Instance. ... I support the Professional Association for SQL Server ... > Thanks for the response and identifying my understanding with clustering. ...
    (microsoft.public.sqlserver.clustering)
  • Re: 4 Queries about DB clustering
    ... All drives will need to be visible to all hosts for failover. ... I support the Professional Association for SQL Server ... > I have a similar situation where I plan to install/configure multi-instance> cluster. ... SQL binaries will live on>> the local disks of each cluster host. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Access denied when .ldb is present (only for XP-SP2)
    ... with Win XP are either SQL Server, ... > 4) not mapped drives - i connect to host pc, ... >> 3) multiple domains? ...
    (microsoft.public.access.security)
  • Re: ActiveActivepassive setup
    ... Instances are the actual SQL Servers that use a host to execute. ... So what you ask is outside the definition of SQL clustering, but you can build a system that does what you want. ... You end up with three separate SQL Server instances that can run on any of four nodes. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Re:Connection
    ... I know you host a web site on your ISP. ... You want to know you'd better host the SQL server on ...
    (microsoft.public.windows.server.sbs)