 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Clustering / June 20054 Queries about DB clustering
Hi, Below are few queries: 1. Say for 2 nodes cluster, Do we need to have 2 copies of Databases( 1 on each node) 2. Hows does the data Synchronization between different nodes of databases takes place? 3. In Active/Passive mode, does the passive node database is exactly in the same state (in terms of data) as that of Active? 4. Is 'Server Cluster' specifically for DB clustering ? Thanks in advance, Pankaj A. Chitriv Answers Inline  SignatureGeoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org > 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 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 > Answers Inline > [quoted text clipped - 20 lines] > > Thanks in advance, > > Pankaj A. Chitriv 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. SignatureGeoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org > Thanks Geoff. > [quoted text clipped - 40 lines] > > > Thanks in advance, > > > Pankaj A. Chitriv 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". 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. 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. 4. I need to install SQL Client on both the nodes and Sql Server on shared device. 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? 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 > Server clustering is available on Windows Server 2003 Enterprise Edition. > [quoted text clipped - 61 lines] > > > > Thanks in advance, > > > > Pankaj A. Chitriv You are getting closer. More comments inline. SignatureGeoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org > Hi Geoff. > [quoted text clipped - 5 lines] > 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 :-). > [quoted text clipped - 68 lines] > > > > > Thanks in advance, > > > > > Pankaj A. Chitriv 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. > You are getting closer. More comments inline. > [quoted text clipped - 138 lines] > > > > > > Thanks in advance, > > > > > > Pankaj A. Chitriv 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. SignatureGeoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org > I have a question for Geoff. > [quoted text clipped - 152 lines] > > > > > > > Thanks in advance, > > > > > > > Pankaj A. Chitriv 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. > 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 [quoted text clipped - 210 lines] > > > > > > > > Thanks in advance, > > > > > > > > Pankaj A. Chitriv Active\Active and Active\Passive are not accurate terms. Each SQL instance is its own virtual server. SQLInstance1 and 2 refer to the SQL virtual servers\instances. The host nodes are all configured identically. You will have to add disk storage sometime down the road. Leave empty drive letters for adding new disk resources. SignatureGeoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org > First, manythanks for quick reply. > [quoted text clipped - 222 lines] > > > > > > > > > Thanks in advance, > > > > > > > > > Pankaj A. Chitriv 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. > First, manythanks for quick reply. > [quoted text clipped - 222 lines] > > > > > > > > > Thanks in advance, > > > > > > > > > Pankaj A. Chitriv 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. SignatureGeoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org > Geoff, > [quoted text clipped - 245 lines] > > > > > > > > > > Thanks in advance, > > > > > > > > > > Pankaj A. Chitriv 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. > 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 [quoted text clipped - 314 lines] > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > Pankaj A. Chitriv 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. SignatureGeoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org > Thanks for the response and identifying my understanding with clustering. I > really appreciate it. [quoted text clipped - 300 lines] > > in > > > > > message news:847A2BE3-74E1-4B9E-9D61-EEE40D117155@microsoft.com... > > > > > > > > > > > > Hi, Below are few queries: > > > > > > > > > > > > [quoted text clipped - 33 lines] > > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > > Pankaj A. Chitriv Thanks a lot! I really appreciate your answers/responses. Regards, Kamal. Hassan > You still seem to have a disconnect here. > [quoted text clipped - 418 lines] > > > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > > > Pankaj A. Chitriv Hi Geoff, You may recall you have responded to my questions and I really appreciated your comments/feedback. I was wondering if you can provide any advice/feedbackon following: We are using (Lab/Test) EMC-CX300. The first enclosure has a capacity of 15 divres. Currently the configuration looks like this: Windows 2003 Enterprise Edition SQL 2000 Enterprise Edition SQL Clustering (currenlty configured for 1 instance, plan to add two more) a) 6 drives (three RAID-5 LUNS, 100 GB each for Data for three INSTANCES) b) 4 drives (three RAID-1 LUNS, 33 GB, 28 B (Log) 5.0 GB for QUORUM) We are discussing/debating weather should we go with RAID 10 or not. As we understand and you also stated the RAID 10 is the fastest we plan to go that route but not sure how to CARVE the disk/luns for BEST performance for 3 SQL Server instances. I would really appreciate your comments/feedback. Thanks. Kamal. > Thanks a lot! > [quoted text clipped - 426 lines] > > > > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > > > > Pankaj A. Chitriv I prefer to map LUNS to RAID sets on an EMC SAN. You have better control over your IO management that way. On an EMC system, the first five drives are 'magic' so I avoid using them for any high-IO storage per EMC best practices. I RAID-5 stripe the leftover space and slice Quorum and temporary LUNS from that RAID set. In your case, that leaves 10 drives for 6 instances. RAID-1 will give 5 LUNS max from such a configuration so this won't work. You will have to use the vault drives or purchase an extra enclosure or carve multiple LUNS from a single RAID SET. RAID 10 is much faster than RAID5 on an OLTP system. If you are using a DSS system where data gets loaded and is largely static until queried, then the performance differences aren't too much. Definitely make sure your LUNS are stripe-aligned with the OS partition. If your install engineer doesn't know how, make him download and read the best practices guide from the EMC PowerLink site. This one step will do more than anything else to increase maximum performance. As for the specific RAID SET and LUN layout, that is an engineering task well beyond the scope of a newsgroup posting. Your vendor should provide an architect that will ask about your specific needs and design a layour that meets them. If not, keep shopping. EMC has many resellers and the prices/services vary considerably. This sounds like your first step into Enterprise Computing and your decisions should not be driven by price alone. Geoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator > Hi Geoff, > [quoted text clipped - 529 lines] >> > > > > > > > > > > > > > Thanks in advance, >> > > > > > > > > > > > > > Pankaj A. Chitriv You are great! Thanks very much for your feedback and responses. As you know, we have currently configured/setup/running SQL Clustering using EMC-CX300 (Windows 2003 EE & SQL Server 2000 EE). As a result of some of your feedback (THANKS A MILLION...) and performance monitoring we plan to reconfigure the drives/RAID. What would be the best way to accomplish this task? I would prefer to do a clean installation but not sure of any potential issues with that? Is the UNINSTALLATION/REMOVAL of SQL Clustering is EASY and painless process? As always, I would appreciate your feedback. Kamal Hassan > Thanks a lot! > [quoted text clipped - 426 lines] > > > > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > > > > Pankaj A. Chitriv SQL Clustering uninstalls pretty well as long as all nodes are up and running. Here is what to do if the uninstall breaks and does not remove SQL cleanly: How to manually remove SQL Server 2000 default, named, or virtual instance http://support.microsoft.com/default.aspx?scid=kb;en-us;290991 Geoff N. Hiten Microsoft SQL Server MVP > You are great! Thanks very much for your feedback and responses. > [quoted text clipped - 515 lines] >> > > > > > > > > > > > > > Thanks in advance, >> > > > > > > > > > > > > > Pankaj A. Chitriv Hi Geoff, As always, THANKS for your answers... I have a simple question. Currently we have (in LAB) 2-Node SQL Cluster (Windows 2003 EE, SQL Server 2000 EE) setup/configure using SAN (CX300). Recently, we purchased additional Tray and additional drives and plan to re-configure the SAN meaning different LUNS/RAID configuration. As a result, I plan to re-configure Windows and SQL Clustering. What would be the steps (in order) to UnCluster/Uninstall (SQL and Windows Clustering) and then re-install/re-configure Windows/SQL Clustering to avoid any issues. As I said I do not mind (in fact prefer) to clean installation of Windows & SQL Clustering but definitely like avoid any potential issues. THANKS A LOT! Kamal Hassan > SQL Clustering uninstalls pretty well as long as all nodes are up and > running. Here is what to do if the uninstall breaks and does not remove SQL [quoted text clipped - 222 lines] > >> > > > > > > > > > really > >> > > > really Ouch. That is a BIG task. Unless you plan to leave the Qourum drive intact, I would do a complete re-install, especially since this is a lab environment. I would also image (Norton Ghost or your favorite imaging tool) a baseline of each OS both before and after clustering, just to have a good fallback position. If you want to keep any user databases, normal backup and restore works just fine. If you plan on reusing any host names, be sure and wipe any trace from AD, DNS, and the SAN or you will have problems with name resolution and authentication. If the Quorim drive is going to be left alone, a simple SQL uninstall works just fine and saves a lot of work re-introducing the SAN to the host computers. If things go bad, here is how to completely wipe a SQL installatio from a host computer How to manually remove SQL Server 2000 default, named, or virtual instance http://support.microsoft.com/default.aspx?scid=kb;en-us;290991 Good luck, Geoff N. Hiten Microsoft SQL Server MVP BTW, you may want to start a new thread with new questions. I almost didn't catch this last one. > Hi Geoff, > [quoted text clipped - 281 lines] >> >> > > > > > > > > > really >> >> > > > really Thanks a Billion! > Thanks a lot! > [quoted text clipped - 426 lines] > > > > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > > > > Pankaj A. Chitriv |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.