Re: 4 Queries about DB clustering
From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 02/16/05
- Next message: Geoff N. Hiten: "Re: SAN drive config for SQL Cluster"
- Previous message: MikeH: "Collation and Win2K3 Clustered Environment."
- In reply to: Kamal Hassan: "Re: 4 Queries about DB clustering"
- Next in thread: Kamal Hassan: "Re: 4 Queries about DB clustering"
- Reply: Kamal Hassan: "Re: 4 Queries about DB clustering"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Feb 2005 10:05:20 -0500
You still seem to have a disconnect here.
Instances and servers(nodes) are totally separate entities. You install an
instance to the cluster using cluster resources. Instances then are hosted
on a specific node, with other nodes providing failover capability. After
installation, you determine the preferred host order for each SQL Instance.
The number of instances is not limited by the node count. Also, after
installation, there is no real difference between nodes as far as the
instance is concerned except for the preferred failover order that you
choose.
So what you would really do is the following
Create a two node cluster. (Node1, Node2)
Install a SQL instance to the cluster (SQL1) At this time, you decide the
Later you want to:
Add a Host Node (Node3)
Add an Instance (SQL2)
You can do them in either order, but it is simpler to add the node first.
When you add the node, there are some extra installation steps to install
the SQL binaries to new host computer. This process is documented in BOL.
You can then add more instances and/or nodes as you choose. Ultimately it
sounds like you want three virtual SQL servers running on a four node
cluster. This works very well. I have such a beast in production now.
-- 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:17F04053-E8DA-46F4-848E-E3AB28FBA24E@microsoft.com... > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: Geoff N. Hiten: "Re: SAN drive config for SQL Cluster"
- Previous message: MikeH: "Collation and Win2K3 Clustered Environment."
- In reply to: Kamal Hassan: "Re: 4 Queries about DB clustering"
- Next in thread: Kamal Hassan: "Re: 4 Queries about DB clustering"
- Reply: Kamal Hassan: "Re: 4 Queries about DB clustering"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
