Transcription
SQL Server InsiderClusters help you run a highly available SQL Server, but setup requiresa whole bunch of steps. We walk you through the process, and help youmake the right configuration decisions before you click on Install.How to build aSQL Server cluster(and keep your hair, too)INSIDE 03 Cluster basics etworking 04 Nconcerns 07 Configuringstorage 09 I nstalling thecluster 13 The final setup 14 L ive talk withGeoff Hiten Brought to you bySearchSQLServer.com3
SearchSQLServer.comHow to build a SQL Server clusterHow to build aSQL Server clusterBy Geoff HitenWhen a company realizes that its databasescan be single points offailure for a major system or business process,it may look to clustering to increase availability of thedatabase’s host computers. As aresult, a DBA could find himselfas the first person in the organization given the responsibility fora server cluster. Who, me?Building a SQL Server clustercan be an intimidating experiencethe first time you tackle the task.This guide walks you through thenecessary steps for creating aworking database cluster.Cluster basicsNetworking concernsAs with any high-availabilitycomponent, the real work inclustering happens long beforean install disk is inserted intoa server. Careful planning andpreparation makes the actualinstall process no more complex than a standalone SQLServer build. However, youshould be comfortable and familiar with the standalone SQLinstallation process before youattempt to build a cluster.Let’s begin by examining acluster and learning how it isdifferent from a standalone SQLServer.“A DBA couldfind himselfas the firstperson in theorganizationgiven theresponsibilityfor a servercluster.Who, me?”øA WindowsCluster consistsof a minimum ofthree pieces. . . Configuring storageInstalling the clusterThe final setupLive talk w/Geoff Hiten73
SearchSQLServer.comHow to build a SQL Server clusterCluster BasicsA Windows cluster consists ofa minimum of three pieces: twoor more host nodes (servers)and some type of multi-connected storage system. It ispossible to build a single-nodecluster, but typically a singlenode cluster is a transition system to a multi-node cluster.Make sure you’re workingwith the right level of the operating system. Windows Server2003 supports up to eightnodes on a cluster. SQL 2000Enterprise Edition supports upto four nodes in a cluster, whileSQL 2005 offers two options.SQL 2005 Standard Editionsupports up to two nodes, whileSQL 2005 Enterprise Editioncan support the operating sys-“ Give a lot of thoughtto hardware; hardwareselection is essential.”Cluster basicsNetworking concernstem node limit. SQL Server caninstall one or more instances ona single cluster in a variety ofconfigurations.Give a lot of thought to hardware; hardware selection isessential. Since clustering addsan extra layer of complexity, itis imperative that the hardwaremeets very specific standards.To build a working cluster,choose hardware that is identified in the Windows HardwareCatalog as cluster-certified.Don’t skimp or cut corners.The hardware must be purchased as a cluster from thevendor. You cannot put it together from existing non-clustered hardware. Again, thisis to ensure that the systemoperates as a stable databaseplatform. While it is perfectlyacceptable to build a test andtraining cluster from left-overcomponents, it is a very badidea to run any critical businessprocess on such a system.Configuring storageInstalling the clusterThe core of a cluster is themulti-connected storage system. This component rangesfrom a simple hot-swap driveenclosure, costing a few thousand dollars, up to multi-million dollar storage area network(SAN) systems. What makesthis type of hardware essentialto clustering is that the enclosure is separate and independent from any individual hostnode. That allows the clusterto function with one or morenodes offline.Windows clustering supportsa maximum of two nodes ona SCSI storage system. This isbecause of the inherent limitations of a SCSI bus, not becauseof any Windows clusteringlimitation. Most commercialcontrollers only allow two address selections, thus limitingthe system to a maximum oftwo host controllers. There arealso specific startup processesto follow when using SCSI stor-The final setupLive talk w/Geoff Hiten 73
SearchSQLServer.comHow to build a SQL Server clusterIP address selections for a sample clusterage systems beyond the normalcluster startup procedure.Networking concernsOnce you select an appropriate hardware platform for yourcluster, you can start to planthe actual installation. First,you need to choose names andIP addresses for the systems.You need one name for eachcomputer in the cluster, oneøAlways set all NICs in yourcluster to the maximumspeed supported by thenetwork hardware. Donot allow the NICs toauto-detect speed. Theauto-detect processcan introduce additionallatency into thecommunications process,introducing systeminstability and possiblycausing a false clusterfailure.Cluster basicsNetworking concernsnameip addressroleclassnetwork subnetgatewayEast10.10.1.20Host .1.20Host Host .1.30Host .1name for the cluster itself anda different name for the SQLServer. Each of these namesappears on the network as anindependent computer system.Nodes are actual physical machines, while the cluster andSQL names are referred to as“virtual servers.”Each node needs two physical network cards, each witha unique IP address. One cardis used for public connectionsand the other is for private,cluster-only communications.In this context, “public” meansany computer connecting fromConfiguring storageInstalling the clusteroutside the cluster.In the tabled example, Eastand West are the node names:TheCluster is the Cluster name,and TheSQL is the SQL Servername. The table shows the IPaddress selections for a samplecluster.Notice that the private network has no gateway. DisableNETBIOS communication onthe private NIC. Under networkproperties, make sure TCP/IPis the only selected protocolon the private NIC. For a twonode cluster, a simple crossovercable is the best solution.The final setupLive talk w/Geoff Hiten 73
AdvertorialA System of ExcellenceThe Kenton County School DistrictAn IT infrastructure based on Dell platforms keeps the Kenton County SchoolDistrict at the head of the classThe Kenton County School District is a big district with an even bigger reputationfor excellence. Located in northern Kentucky, it serves over 13,000 students throughout 21 schools and has reaped many honors, including being selected as a spotlightschool district by the U.S. Department of Education in 1999 and 2000, and receivingthe national “What Parents Want” award for 12 years in a row. 8JUIPVU RVFTUJPO %FMM LFFQT PVS BQQMJDBUJPOT VQ BOE SVOOJOH CFUUFS UIBO BOZ PUIFS WFOEPS XJUI XIPN * IBWF XPSLFE JO UIF QBTU ³— Vicki Fields, District Technology CoordinatorKenton County School DistrictMaintaining such a high level of excellence in academics requires excellent IT. Inorder to support classroom activities, the district’s administrative functions, transportation, food service, and finance must run reliably and efficiently. Also, critical information such as student records, class schedules, and other data must be collected,maintained, and made readily available to teachers and other staff who require it.“We need an IT infrastructure that can support all of our departments,” says VickiFields, district technology coordinator for the Kenton County School District. “Plus, wewant our teachers to have instant access to student information so they can spendtheir time teaching instead of chasing down student records.” 73
AdvertorialA System of ExcellenceThe Kenton County School DistrictKenton County looks for a new platformA few years ago, the district wanted to improve teacher and staff access to studentinformation and other types of data. “We have information distributed across a varietyof delivery systems,” says Fields. “We wanted to provide comprehensive access to thatinformation to better facilitate teaching, administrative functions, and decision makingthroughout the district.”Also, Fields wanted to standardize on a single hardware vendor. “We have manydifferent applications throughout the district, and we wanted to find one vendor thatcould support them all,” says Fields. “In addition, we wanted a vendor with excellentsupport and services that would be with us over the long haul.”Kenton County decides on DellAfter evaluating several vendors, Fields was impressed with the flexibility of Dell offerings. “Other off-the-shelf types of technologies didn’t have the flexibility that weneeded,” says Fields. “When I approached Dell, their willingness to comply with ourrequirements was a key selling point. Dell told us we could set up our system howeverwe wanted, and that was just what we needed to hear.”To view the entire story, go to www.dell.com/sql 73
SearchSQLServer.comHow to build a SQL Server clusterSome clusters use additionalnetwork adapters for generalpublic or limited public access.These may be teamed to asingle IP address or segmentedusing a connection-specificdomain suffix and a differentIP address and subnet. Teaming is often used to increasebandwidth and availability,while segmentation dedicates a“ Teaming is often usedto increase bandwidthand availability, whilesegmentation dedicatesa portion of networkbandwidth for a specificset of clients or to isolatebandwidth-intensiveoperations, such asreplication or backup.”Cluster basicsNetworking concernsportion of network bandwidthfor a specific set of clients orto isolate bandwidth-intensiveoperations, such as replicationor backup.Do not set up additional NICsfor teaming during the initialinstallation. Segmented NICsshould be selected and enteredduring the cluster installationprocess. With multiple NICs ina system, I find it helpful to rename the network connectionson all the host nodes to matchtheir function, such as “Public,”“Heartbeat,” “Backup” or “Private.” That helps in both installation and troubleshooting.CONFIGURING STORAGEDuring the build process, themulti-connected storage unitshould not be visible to thehost computers. For small SCSIstorage units, powering themdown is adequate. With a SAN,you can create the logical unitnumbers (LUNs), but do notConfiguring storageInstalling the clustergrant access for them to anyhost node. If you are bootingfrom the SAN (which I do notrecommend), present only theboot partition LUNs to the hostnodes while you are configuringthe servers.Whew. That takes care ofhardware. We can finally beginto address the software.The first actual installationfor the cluster should be theoperating system for each hostnode. Install Windows 2003Enterprise Edition exactly asyou would for a standalone SQLServer. Apply all current service packs and patches as youwould with any other databaseserver.Once all nodes are completely installed and operational,including all network settingson the physical connections, itis time to configure the storagesystem. For this portion, you’llneed to have only one nodecontrolling the storage unit.The final setupLive talk w/Geoff Hiten 73
SearchSQLServer.comHow to build a SQL Server clusterThe easiest method is to powerdown all nodes, except the oneon which you are working.Note that you cannot dividea disk at the operating systemlevel for clustering, only at thephysical disk. A disk with multiple partitions is treated as asingle disk resource for clustering.For the example cluster, weneed three sharable drives onthe shared storage array. For aSAN, these are individual LUNs;for a SCSI array, typically theyare RAID containers. We alsoneed two clustered disk re-“ Follow thestorage vendor’srecommendationsexactly for a clusteredinstallation; you’ll besorry if you don’t.”Cluster basicsNetworking concernssources: one for the cluster andone for The SQL Cluster itself.Best practices for SQL recommend placing data and transaction logs on different physicalmedia. You can create additional disk resources for your cluster and have SQL install themall together. Microsoft Distributed Transaction Coordinatorcan reside on the cluster disk inmost cases, but in high-volumeenvironments, it should be onits own disk resource.Follow the instructions foryour specific storage system tocreate LUNs or RAID containersto present to the host nodes.Some SANs may come withLUNs created according to recommendations from a storagearchitect. They may also havespecific partitioning proceduresto maximize performance andavailability. Follow the vendor’srecommendations exactly for aclustered installation; you’ll besorry if you don’t.Configuring storageInstalling the clusterOnce the disks are createdand visible to the operating system, use the regular WindowsDisk Manager to partition andformat the disks. Assign thedisk letters you intend to usefor the clustered system to thepartitions at this time. It is common to use the letter “Q” forthe Cluster disk (often referredto as the Quorum disk).At this time, power up theother host nodes to verify diskvisibility. Since there is no cluster to arbitrate ownership, youcan write to each disk from anyhost node. Windows uses anautomatic disk cache that assumes exclusive ownership oflocal disk resources. This willlead to disk corruption if you tryto use a multi-connected storage system outside the controlof a cluster.You only have a couple moredecisions to make before starting the actual cluster installation. You need two serviceThe final setupLive talk w/Geoff Hiten 73
SearchSQLServer.comHow to build a SQL Server clusteraccounts: one for the clusterand one for the SQL service. Allaccounts must be domain-levelaccounts. A domain (active directory or NT4.0) is necessaryto provide a common securitycontext. The cluster service accounts on the nodes can worktogether as a single logicalentity in a domain.I don’t advise you to use thesame account for the clusterservice and the SQL Server.While it is possible to use separate accounts for the SQL Service and the SQL Agent, mostinstallations use a single account for both. After you createthe accounts in the domain, addøNote that the clustershown here is for a singlenode only. Once the clusteris working, you can addmore nodes.Cluster basicsNetworking concernsthe cluster service account tothe local administrator’s groupon each node. You can do thisto the SQL Service account aswell.Installing the clusterNow that we have all the building blocks in place, you canstart the actual cluster installation. You may need the OS diskfor additional files, so find itbefore you settle down.Start the cluster installationfrom the cluster administrator tool. You’ll find this toolinstalled under AdministrativeTools on all Windows Server2003 Enterprise Edition systems. File New Clusterinvokes the cluster setup wizardfrom the Cluster Administratortool.The Create New Cluster wizard asks for the cluster nameand the name of the first nodein the cluster. Fill in those fields;the wizard will validate yourConfiguring storageInstalling the clustercluster configuration. The validation step generates a detailedlog that gives excellent guidance for fixing any problem youencounter. (A Re-analyze button reruns the test after you fixit.) When the completion bar issolid green, you can click Nextto continue installing the cluster.You then enter the ClusterPublic IP address and the cluster service account information,which you chose in our earlierdiscussion. The cluster wizarddisplays a summary page before it begins.The most important elementto check here is to verify thatthe Quorum drive is the correctdisk. If a cluster-eligible diskhas drive letter Q:, the clusterwizard will use it for a quorum.Clicking “Next” launches thecluster build process. It re-runsthe cluster verification test andthen performs the cluster build.You may be surprised at howThe final setupLive talk w/Geoff Hiten 73
SearchSQLServer.comHow to build a SQL Server clusterrapidly the cluster build processruns. In a very few moments,you will have a working singlenode cluster.After the first node of thecluster is configured, the Cluster Administrator tool automatically connects to the clusterand displays its components.Take a few moments to exploreyour cluster’s groups and resources.next, we add additional nodesto the cluster. Right-click on thecluster name and select New Node. You can add multiplenodes at this time. If you usedLUN masking from a SAN, besure all your nodes have beengranted access to all clusterwide disks.Enter the node name(s) andclick Next. Once again, the cluster verification wizard does itsthing, making sure everythingmatches up with the existingnode and any new nodes. In theCluster basicsNetworking concernsnext screen, the wizard asks forthe cluster service passwordand begins adding the extranode. This will likely take longerthan did the initial cluster buildprocess. Once again, a greenstatus bar indicates completesuccess.This is an excellent time tomake sure all the groups andresources can be moved to anycluster node. The right-clickmenu option “Move Group”shifts resource groups betweenhost nodes without incrementing the group failure count.Many clusters that do nothave much distributed transac-øYou may get a warning ifyou uninstalled a clusterfrom any of the new hostnodes without reinstallingthe OS. That warning is notcritical and does not affectthe new cluster.Configuring storageInstalling the clustertion activity have the MSDTC(Microsoft Distributed Transaction Coordinator) installed inthe cluster resource group. Youcan have a cluster without aMSDTC resource, but there areoften undesirable side effects,including legacy client accessproblems. Use the cluster toolto create a new resource of type“Distributed Transaction Coordinator,” add it to the quorumgroup and make it dependenton the Quorum disk. Createa separate group with a diskresource, network name andIP address if you expect a highvolume of distributed transactions.If you are teaming usingNIC teaming, now is the timeto switch from standalone toteamed NICs.As I noted early in this article,most of the work in creating aSQL cluster happens long before starting the install application. So far, the entire exerciseThe final setupLive talk w/Geoff Hiten1073
SearchSQLServer.comHow to build a SQL Server clustercreated a stable platform forhosting a SQL cluster. Now wecan leverage that platform torun a highly available SQL Server. After one more final configuration tweak, we will be readyto install the SQL Server itself.the last step in setting up thecluster is to clean up any installation leftovers. Make absolutely sure there are no offline“ After filling out thestandard registrationdialog box, you areshown the mostcritical dialog ofthe SQL Clusterinstallation sequence.Here is where youchoose the actualcomponents of theSQL Server to install.”Cluster basicsNetworking concernsresources in your cluster. If youhave an offline resource, theSQL installer will crash beforedoing any meaningful work.In our example, we use onlyone disk for our SQL resource,but if you are building a clusterwith multiple disks, place themall in the same resource group.Delete empty resource groupsthat may be left over from earlier steps.Start by verifying that theWindows Task Scheduler is enabled and running on all clusternodes. Log out of all clusternodes, including remote consoles, except for the node thatcurrently owns the resourcegroup with the soon-to-be SQLDisk Resources. Log into thephysical console of that node oruse the /console option if youare using RDP.Use the SQL Server 2005install disk appropriate for yourhardware platform and operating system. The install processConfiguring storageInstalling the clusterfor a two-node cluster is exactlythe same as it is for an eightnode cluster. It is also similar tothe standalone install process:The installation begins with aclick-through license and thepre-install component installation, followed by the standardpre-installation system check.But that’s where the installationdiverges from the standaloneSQL installer sequence: TheSQL installer checks the localcomputer and checks all clusternodes.After filling out the standardregistration dialog box, you areshown the most critical dialogof the SQL Cluster installationsequence. Here is where youchoose the actual componentsof the SQL Server to install. Forthis example, we install SQLServer Database Services andthe Workstation tools. If youselect SQL Server DatabaseServices while on an operatingcluster node, you can select theThe final setupLive talk w/Geoff Hiten1173
SearchSQLServer.comHow to build a SQL Server clusterCreate a SQL Server FailoverCluster option. Notice that theworkstation tools installationis a separate option and is notclustered.The installer only creates theSQL tools on the local computer. You must install the toolset separately on each cluster node. Use the “Advanced”button to fine-tune which subcomponents to install, but I donot recommend altering theSQL default installation on acluster. You can also create anAnalysis Services cluster instance, but that is beyond thescope of this article.You then choose whether toinstall a default instance or anamed SQL instance. I preferto install clustered SQL Serversas Named Instances; doing soavoids naming confusions if youhave more than one instance ona cluster.You register and connectto the SQL Server as Virtu-Cluster basicsNetworking concernsØalServerName\InstanceNamewhen you use a default instance. While the two namescan be different, I prefer tofollow the SQL 2000 naming convention and keep themidentical. For our example,we have the Named Instance:TheSQL\TheSQL.the next piece of informationto provide is the network configuration. Select the networkconnection based on its name,enter the corresponding IP address for your Virtual Serverand add it to the list. You donot need to add a virtual IP tothe heartbeat network — nordo you want to do so. Note thatyou can only add one addressper network connection. Thesubnet mask and gateway areinherited from the underlyingphysical network connectionand you cannot change it here.Again, preparation is everythingin a cluster!Configuring storageInstalling the clusterYou do notneed to add avirtual IP tothe heartbeatnetwork — nordo you wantto do so.The next dialog box allowsyou to select which nodes canrun this Virtual SQL Server.After that, you enter the installation account password, whichmust be a member of the local administrator’s group oneach server. Then, you specifythe service accounts and passwords for the SQL Server services.The next dialog box is, again,unique to a clustered installation; it’s a best-practice recommendation that is now enforcedby the SQL Installer. Underprevious versions of SQL Server,The final setupLive talk w/Geoff Hiten1273
SearchSQLServer.comHow to build a SQL Server clusterthe SQL Server service accountneeded to be a local administrator on all nodes. However,since that violates the “leastprivileges” security guideline,permissions are now granted toa domain-level group that hasthe appropriate service accountas its only member. You canuse the same group for all threeservices or set them up independently. This is helpful if youever need to change the SQLService account; simply add thenew account to the group to setall the relevant permissions.In the next two dialog boxes,we are back to the normal install sequence: Security mode(with sa password) and collation selection work in exactlythe same manner as in a stand-“ This is a good time to geta cup of coffee and watchthe blinky lights.”Cluster basicsNetworking concernsalone SQL install. Then you canchoose to send error or featureusage information to Microsoftfor analysis and for future SQLimprovement.the final setup:The “Ready to Install” summary box repeats the top-levelcomponents you chose back atthe beginning. It isn’t a detaileddescription of your clusterconfiguration choices. Think ofit as more of an “are you sure?”page. Notice that there is no“Next” button; we are ready to“Install.”This is a good time to get acup of coffee and watch theblinky lights. The SQL Installerruns its list of components andshows the progress of eachitem. All SQL 2005 installationstake some time, and clustersare no exception. If anything,they take a bit longer than astandalone installation. Do notbe alarmed if you have to waitConfiguring storageInstalling the clusteras long as 30 to 60 minutes,especially if you included additional SQL components, suchas Integration Services, or if youare using older, slower hardware to build a test platform.gentlemen, start yourclusters Congratulations!You are now the proud owner,keeper, feeder, and first-person-on-call for a SQL Servercluster. From here on out, except for actual cluster repairor troubleshooting tasks, youcan treat this as you would anystandalone server, at least froman application perspective. Yousimply have a second serverconstantly monitoring yourprimary system, ready to takeover nearly instantly. Instead ofthe dreaded “Server Down” call,you will get a “Cluster FailedOver” notification. If you dideverything else right, the failure may not be noticed outsideyour team.The final setupLive talk w/Geoff Hiten1373
SearchSQLServer.comHow to build a SQL Server clusterabout the authorMicrosoft MVP Geoff Hiten has been workingwith SQL Server since 1993. In this podcast,he talks with SearchSQLServer.com’s HeidiSweeney about the benefits of clustering,how to get started and key issues to considerbefore you click “Install.” Hiten has a bachelorof science in Computer Science from theengineering department of the University ofAlabama in Tuscaloosa.He specializes in highavailability and highthroughput transactionalsolutions.ØLive talk withGeoff HitenStarting pointsfor building a SqlServer WS/SearchSQLServer Podcast Clustering2006-11-27.mp3The real workin clusteringhappens longbefore an installdisk is insertedinto a server.14Cluster basicsNetworking concernsConfiguring storageInstalling the clusterThe final setupLive talk w/Geoff Hiten73
SearchSQLServer.comHow to build a SQL Server clusterAdditional Resources from Dellystem of Excellence: IT Infrastructure Keeps the Kenton School System at the Head of the ClassØ Swww.dell.com/sqlServer 2005: Preparing for a Smooth UpgradeØ 06-20060126-Microsoft.pdf aximizing SQL Server PerformanceØ -20050272-Symantec.pdf The Scalable Enterprise Technology CenterØ er/en/setc?c us&cs 555&l en&s bizicrosoft SQL Server 2005 VirtualizationØ -20060405-Muirhead.pdfhe Definitive Guide to Scaling Out SQL Server 2005Ø liances/en/ebook landing?c us&cs 555&l en&s biz15Cluster basicsNetworking concernsConfiguring storageInstalling the clusterThe final setupLive talk w/Geoff Hiten7
SQL Server cluster As with any high-availability component, the real work in clustering happens long before an install disk is inserted into a server. Careful planning and preparation makes the actual install process no more com-plex than a standalone SQL Server build. However, you should be comfortable and fa-miliar with the standalone SQL
