Re: 4 Queries about DB clustering

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Kamal Hassan (KamalHassan_at_discussions.microsoft.com)
Date: 02/16/05 

Date: Tue, 15 Feb 2005 20:25:02 -0800

Thanks for the response and identifying my understanding with clustering. I
really appreciate it.

Our goal is to install/setup/configure 4-node clustering (SQL Server) in the
following order:

a) setup/install/Configure TWO Nodes with Server1 (Active) & Server2
(Standyby)

b) Later, Add third Node Server3 (Active) & Server2 remain Standby, we now
have Server1 & Server3 as two active nodes and Server2 as Standby providing
failover for either active nodes.

c) Later, Add fourth Node Server4 (Active) and Server2 remain Standby, we
now have Server1, Server3, Server4 as three Active Nodes and Sever2 as
Standby providing failover for either active nodes.

Hope this explain the situation better. Let me know if you need additional
information about the configuration before providing any suggestions.

I would really appreciate the suggestions/comments.

Kamal.

"Geoff N. Hiten" wrote:

> 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: Indexes Confuses me!!
    ... Kalen Delaney has a column called Inside SQL Server in SQLMag ... and performs a bookmark lookup. ... reading the actual data row costs a single logical page read. ... >>>>>performing a bookmark lookup using clustering keys (i.e., ...
    (microsoft.public.sqlserver.programming)
  • Re: Quorum Disk or Majority Node?
    ... SQL Server does not NATIVELY support MNS clustering. ... >> The chapter "Three-Site Majority Node Set Quorum in Geographically ...
    (microsoft.public.sqlserver.clustering)
  • RE: Clustering Set up
    ... Both SQL Server 2000 Failover Clustering and SQL Server 2000 Log Shipping are disaster recovery options. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Quorum Disk or Majority Node?
    ... but that would still be a MNS ... >> SQL Server does not NATIVELY support MNS clustering. ... >>> replacement for that shared storage so that the SQL data is available on ...
    (microsoft.public.sqlserver.clustering)
  • Re: Using sql server failover clustering
    ... This says that NODE1 has a local disk and NODE2 has a local disk. ... For a clustered instance of SQL Server, ... I convince my boss to work on sql server failover clustering for better ... My database size is more then 20Gb and in some cases more then 100Gb. ...
    (microsoft.public.sqlserver.clustering)