This is a standard outline of the process that should be followed for configuring and installing a new cluster for SQL Server use and one or more SQL Server instances (SQL 2000 and 2005 information included). The outline starts with reference materials at the top, followed by a general outline of the process that will be followed and a listing of some pre-work that should be completed prior to starting with the forming of the cluster and/or SQL installation. This same process can be followed for a non-clustered SQL installation, simply ignore the cluster-specific portions.
I strongly recommend you turn on/show the Document Map when viewing within Word, it shows a great outline of the document that you can use to quickly and easily navigate within the document.
KB’s and TechNet Articles
Cluster Overview, BP’s, and Architecture
1. Technical Overview of Windows Server 2003 Cluster Services http://www.microsoft.com/windowsserver2003/techinfo/overview/clustering.mspx
2. Server Clusters: Cluster Configuration Best Practices for Windows Server 2003 http://www.microsoft.com/downloads/details.aspx?FamilyID=98bc4016-31a1-42fb-9730-4fab59cf3bf5&displaylang=en
3. Backup and Recovery Best Practices for Windows Server 2003 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/sercbrbp.mspx
4. Best Practices for Securing server clusters http://www.microsoft.com/technet/prodtechnol/windowsserver2003/library/ServerHelp/f64e46ba-2d09-4f1a-ba9c-f2b1f71821eb.mspx
5. Windows Cluster Service Troubleshooting and Maintenance http://www.microsoft.com/downloads/details.aspx?FamilyID=a2439406-4723-4a94-bc8c-2ff07721ca96&DisplayLang=en
6. Windows Server 2003 Clustering Services Technical Library http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/genclust.mspx
7. What’s new in Clustering Technologies (2003) http://www.microsoft.com/windowsserver2003/evaluation/overview/technologies/clustering.mspx
8. Interpreting the Cluster Log http://www.microsoft.com/resources/documentation/Windows/2000/server/reskit/en-us/Default.asp?url=/resources/documentation/windows/2000/server/reskit/en-us/distrib/dsdg_icl_nnti.asp
9. Server Clusters: Frequently Asked Questions for Windows 2000 and 2003 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/sercsfaq.mspx
10. Windows Server 2003 Server Cluster Architecture http://www.microsoft.com/windowsserver2003/techinfo/overview/servercluster.mspx
Network
1. Recommended private "Heartbeat" configuration on a cluster server http://support.microsoft.com/?id=258750
2. Network Configuration Best Practices for Windows 2000 and Windows Server 2003 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/clstntbp.mspx
3. Knowledge Base article 175767, “Expected Behavior of Multiple Adapters on Same Network.” http://support.microsoft.com/?id=175767
4. Windows Hardware Developer Central Paper: Windows Network Task Offload: http://www.microsoft.com/whdc/device/network/taskoffload.mspx
Setup/Install/Upgrades
1. Guide to Creating and Configuring a Server Cluster Under Windows Server 2003 http://www.microsoft.com/downloads/details.aspx?FamilyID=96f76ed7-9634-4300-9159-89638f4b4ef7&DisplayLang=en
2. Using Microsoft Virtual Server 2005 to Create and Configure a Two-Node Microsoft Windows Server 2003 Cluster http://www.microsoft.com/technet/prodtechnol/virtualserver/deploy/cvs2005.mspx
3. Installation order for SQL Server 2000 Enterprise edition on Microsoft Cluster Server http://support.microsoft.com/kb/q243218/
4. Server Clusters: Rolling Upgrades. Upgrading to Windows Server 2003 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/rllupnet.mspx
5. Installing and Upgrading on Cluster Nodes http://www.microsoft.com/technet/prodtechnol/windowsserver2003/library/ServerHelp/ca612aad-93fa-49df-a33f-d0a94ac9886b.mspx
6. Geographically Dispersed Clusters in Windows Server 2003 http://www.microsoft.com/windowsserver2003/techinfo/overview/clustergeo.mspx
7. How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP http://support.microsoft.com/kb/889654
8. Server Clusters: Security Best Practices: http://technet2.microsoft.com/windowsserver/en/library/a2a735cd-e10b-45c8-b288-186af4e1095e1033.mspx?pf=true
9. Recommended hotfixes for Windows Server 2003-based server clusters http://support.microsoft.com/kb/895092/en-us
10. Recommended hotfixes for Windows 2000 Service Pack 4-based server clusters http://support.microsoft.com/kb/895090/en-us
Storage
1. iSCSI Cluster Support: Frequently Asked Questions http://www.microsoft.com/windowsserver2003/technologies/storage/iscsi/iscsicluster.mspx
2. Microsoft Windows Clustering: Storage Area Networks http://www.microsoft.com/windowsserver2003/techinfo/overview/san.mspx
3. Server Clusters: Storage Best Practices for Windows 2000 and 2003 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/storbp.mspx
4. Server Clusters : Storage Area Networks - For Windows 2000 and 2003 http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/starenet.mspx
5. SQL Server 2000 I/O Basics http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
6. Knowledge Base article 317162 “Supported Fibre Channel Configurations” http://support.microsoft.com/?id=317162
Quorum
1. Quorums in Microsoft Windows Server 2003 Clusters http://www.microsoft.com/windowsserver2003/techinfo/overview/clusterquorums.mspx
2. Server Clusters: Majority Node Set Quorum http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/majnsfl.mspx
3. Knowledge Base article 280353, “How to Change the Quorum Disk Designation” http://support.microsoft.com/?id=280353
4. Knowledge Base article 280345 “Quorum Drive Configuration Information” http://support.microsoft.com/?id=280345
5. Server Clusters: Quorum Options - Windows Server 2003 http://www.microsoft.com/downloads/details.aspx?FamilyID=4dfb478c-83b8-48a4-8e7c-847e49271e8e&displaylang=en
MSDTC
1. Knowledge Base article 301600, “How to configure MSDTC on a Windows Server 2003 cluster” http://support.microsoft.com/?id=301600
2. Knowledge Base article 301600, “How to enable network DTC access in Windows Server 2003” http://support.microsoft.com/kb/817064
Tools
1. Clusrest.exe: Cluster Quorum Restore Utility http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/clusrest-o.asp
2. Cluster Diagnostics and Verification Tool (ClusDiag.exe) http://www.microsoft.com/downloads/details.aspx?FamilyID=b898f587-88c3-4602-84de-b9bc63f02825&DisplayLang=en
3. Cluster Server Recovery Utility (ClusterRecovery.exe) http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=2BE7EBF0-A408-4232-9353-64AAFD65306D
4. mpsreports http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-88B7-F9C79B7306C0&displaylang=en
5. ClusDiag.exe: Cluster Diagnostics and Verification Tool http://www.microsoft.com/downloads/details.aspx?FamilyID=b898f587-88c3-4602-84de-b9bc63f02825&DisplayLang=en
6. SqlIoSim http://support.microsoft.com/kb/231619
SQL Specific
1. SQL Server 2000 Failover Clustering http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
2. Microsoft SQL Server 2000 High Availability Series http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
3. SQL Server 2005 Failover Clustering White Paper http://www.microsoft.com/downloads/details.aspx?FamilyID=818234DC-A17B-4F09-B282-C6830FEAD499&displaylang=en
4. The Microsoft SQL Server support policy for Microsoft Clustering http://support.microsoft.com/kb/327518/en-us
5. How to cluster Microsoft SQL Server 2005 by using Microsoft Virtual Server http://support.microsoft.com/default.aspx/kb/891798
6. Using Microsoft Virtual Server 2005 to Create and Configure a Two-Node Microsoft Windows Server 2003 Cluster https://www.microsoft.com/technet/prodtechnol/virtualserver/deploy/cvs2005.mspx
Troubleshooting/Info
1. KB 814607, "Microsoft support for server clusters with third-party system components" http://support.microsoft.com/default.aspx/kb/814607/en-us
2. Cluster log definition, sizing, etc. http://support.microsoft.com/kb/168801/EN-US/
3. Failover Cluster Troubleshooting http://msdn2.microsoft.com/en-us/library/ms189117.aspx
4. Knowledge Base article 258469: “Cluster Service May Not Start After Restricting IP Ports for RPC” http://support.microsoft.com/?id=258469
5. Knowledge Base article 250355, “Antivirus Software May Cause Problems with Cluster Services” http://support.microsoft.com/?id=250355
6. Knowledge Base article 273673, “INF: SQL Virtual Server Client Connections Must Be Controlled by Clients” http://support.microsoft.com/?id=273673
7. Knowledge Base article 298723, “BUG: SQL Mail Not Fully Supported in Conjunction with Cluster Virtual SQL Servers” http://support.microsoft.com/?id=298723
8. Knowledge Base article 263556, “INF: How to Configure SQL Mail” http://support.microsoft.com/?id=263556
9. Knowledge Base article 289828, “PRB: SQL Server Setup in a Cluster Environment Encounters an Access Violation If You Have a Long Computer Name” http://support.microsoft.com/?id=289828
10. Knowledge Base article 197047 “Failover/Failback Policies on Microsoft Cluster Server” http://support.microsoft.com/?id=197047
11. Knowledge Base article 325343 “How To Perform an Emergency Shutdown in Windows Server 2003” http://support.microsoft.com/?id=325343
12. Knowledge Base article 286342 “Network failure detection and recovery in Windows Server 2003 Clusters” http://support.microsoft.com/?id=286342
13. Knowledge Base article 306677, "IPSec Is Not Designed for Failover" http://support.microsoft.com/?id=306677
14. Knowledge Base article 235529, “Kerberos Support on Windows 2000 Based Server Clusters” http://support.microsoft.com/?id=235529
15. Knowledge Base article 283794, "Problems Using Certificate with Virtual Name in Clustered SQL Servers" http://support.microsoft.com/?id=283794
16. Knowledge Base article 276553, "HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server" http://support.microsoft.com/?id=276553
17. Knowledge Base article 316898, "HOW TO: Enable SSL Encryption for SQL Server 2000 with MMC" http://support.microsoft.com/?id=316898
18. Knowledge Base article 301520, “SQL Server 2000 cluster does not install on Windows Server 2003-based computers where Terminal Services is installed” http://support.microsoft.com/?id=301520
19. Knowledge Base article 815431, “PRB: Installation of a named instance of SQL Server 2000 virtual server on a Windows 2003-based cluster fails” http://support.microsoft.com/?id=815431
20. Knowledge Base article 321063, “HOW TO: Troubleshoot the "Setup Failed to Perform Required Operations on the Cluster Nodes" Error” http://support.microsoft.com/kb/321063
21. Knowledge Base article 327518, “The Microsoft SQL Server support policy for Microsoft Clustering” http://support.microsoft.com/kb/327518/en-us
22. Many domain trusts in PDC, http://support.microsoft.com/kb/910070/en-us
PRE-WORK:
Before beginning, ensure all appropriate documentation and checklists have been gathered, filled out, reviewed, and are available. This includes the following:
1. config_Cluster.doc
2. config_ClusterNode.doc
3. config_Disk.doc
4. config_SqlServer.doc
Installation Order
This is the basic installation order when installing a clustered service on any version of Windows Server 2003:
1. Gather Pre-Work documentation from above
2. Install Windows Server 2003 Enterprise Edition
3. Install any necessary Windows Server 2003 service packs or hot fixes.
a. Recommended hotfixes for Windows Server 2003-based server clusters http://support.microsoft.com/kb/895092/en-us
b. Recommended hotfixes for Windows 2000 Service Pack 4-based server clusters http://support.microsoft.com/kb/895090/en-us
4. Complete any server cluster pre-installation tasks required.
5. Finalize “checklist_ServerCluster_preInstall.doc” checklist
6. Create the server cluster.
7. Create the clustered MS DTC.
8. Complete any server cluster post-installation tasks required.
9. Finalize “checklist_ServerCluster_postInstall.doc” checklist
10. Finalize “checklist_SqlServer_preInstal.doc” checklist
11. Install SQL Server 2000. SQL Server 2000 SP4 is recommended.
a. Or Sql 2005.
12. Install appropriate Sql Server service pack
a. NOTE: SQL Server 2000 Service Pack 2 and earlier versions are not supported under Windows Server 2003.
13. Install any necessary SQL Server hotfixes.
14. Perform Post-SQL Install Tasks
15. Verify the SQL services
16. Finalize “checklist_SqlServer_postInstall.doc” checklist
Windows 2003 Server hot fixes
Ensure to update the base OS with all latest SP’s and appropriate hotfixes here. Run windows update to capture critical updates, and ensure to review the following KB’s:
- Recommended hotfixes for Windows Server 2003-based server clusters http://support.microsoft.com/kb/895092/en-us
- Recommended hotfixes for Windows 2000 Service Pack 4-based server clusters http://support.microsoft.com/kb/895090/en-us
Can also run the MBSA on the system prior to installing the cluster or continuing – here is the website: http://www.microsoft.com/technet/security/tools/mbsahome.mspx. This will pick up any necessary security vulnerabilities.
Pre-Cluster Installation Tasks
See the ‘checklist_ServerCluster_preInstall.doc’ document for additional info.
Run ClusDiag.exe to verify node configuration.
http://www.microsoft.com/downloads/details.aspx?FamilyID=b898f587-88c3-4602-84de-b9bc63f02825&displaylang=en
http://technet.microsoft.com/en-us/library/aa996161.aspx
Run syscompare.exe to verify node synchronization (if available).
Software Installation
Ensure ALL 3rd party and proprietary software is installed, configured, and signed off on by all other appropriate teams prior to cluster configuration (with exceptions for any software that requires cluster interaction and/or inclusion). This includes:
- Anti-virus software
- Backup software and solutions
- Custom email, communications, etc. software
- Security, audit, encryption, etc. software
- Monitoring systems and solutions, including agents
- HBA/Disk management software, drivers, muti-pathing drivers, etc.
- Network card configuration software (i.e. teaming software/drivers)
- Etc.
Network Card Configuration
Public Network
1. From your desktop, right-click My Network Places, and select Properties. Under Windows Server 2003, you might need to enable the Classic Start menu view from the Properties menu on the taskbar to see this.2. In the Network And Dial-up Connections (Windows 2000 Server) or Network Connections (Windows Server 2003) window, select the network card. Rename this to something recognizable and usable, such as Public Network, by selecting it, right-clicking, and selecting Rename. This is the same value on all nodes3. Select the Public network card, right-click, and select Properties.4. Select Internet Protocol (TCP/IP) and click Properties. Set the static IP address of the card to a valid IP address on the externally facing network. This address is different for each node of the server cluster. These addresses will all be on the same subnet, but a different subnet from the private network. Click OK.5. Make sure the Subnet mask is correct.6. Enter your default gateway.7. Enter your primary and secondary DNS servers.8. Click OK to return to the Public Network Properties dialog box. Click Configure.9. In the Properties dialog box for the network card, select the Advanced tab.10. For the External PHY property, set the value for the correct network speed. Set this to be the same on each node. Click OK.11. Click OK to close the Public Network properties dialog box.
Private Network
1. From your desktop, right-click My Network Places, and select Properties. Under Windows Server 2003, you might need to enable the Classic Start menu view from the Properties menu of the taskbar to see this.
2. In the Network And Dial-up Connections (Windows 2000 Server) or Network Connections (Windows Server 2003) window, select the network card. This network card is located only on the private network on the approved subnets. Rename this to something recognizable and usable, such as Private Network, by selecting it, right-clicking, and selecting Rename. This is the same value on all nodes.3. Select the Private network card, right-click, and select Properties.4. Make sure that Client For Microsoft Networks, Network Load Balancing, File And Printer Sharing For Microsoft Networks, and any other options are not selected5. Select Internet Protocol (TCP/IP) and click Properties. Set the static IP address of the card to a valid IP address on the externally facing network. This address is different for each node of the server cluster, and must be in the proper class. These addresses will all be on the same subnet, but a different subnet from the public network. Click OK.6. Make sure the subnet mask is correct.7. Do not enter a default gateway.8. Do not enter any DNS servers.9. Click Advanced.10. Select the WINS tab of the Advanced TCP/IP Settings dialog box, shown in Figure 5-14, and select Disable NetBIOS Over TCP/IP if you are not on an MNS cluster. Click OK.11. Click OK to return to the Private Network Properties dialog box. Click Configure.12. Click Advanced. In the Properties dialog box for the network card, select the Advanced tab.13. For the External PHY property, as shown in Figure 5-12, set the value for the correct network speed. Set this to be the same on each node. Click OK.14. Click OK to close the Private Network Properties dialog box.15. If you are on a Windows 2000 server, add the following registry key and its associated values only if you are using a crossover cable:HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\ParametersValue Name: DisableDHCPMediaSenseData Type: REG_DWORDData: 1 WARNING: Do not perform this step if you are using Windows Server 2003.
Network Priority/Binding Order (server level)
Configure the networks so that they have the right priority and one will not impede the other. At the server level, the public networks should have the priority. You can configure the network by following these steps:
1. On your desktop, right-click My Network Places and select Properties. If you are using Windows Server 2003, you might need to enable the Classic Start menu view from the Properties menu on the taskbar to see this.
2. From the Advanced menu, select Advanced to open the Advanced Settings dialog box
3. All public, or externally faced, networks should have priority over the private ones. If this is not the case, set the proper order and click OK. If you have multiple networks, set them in the proper order. This order is the same on all nodes.4. Close the Network Connections (Windows Server 2003) or Network And Dial-Up Connections (Windows Server 2000) window.5. Repeat this procedure for each node of the cluster.
Network Data Throughput
Configure Data Throughput to ‘Maximize Data Throughput for Network Applications’ for each appropriate Network Connection (note that you will only configure this for any non-private networks in a cluster configuration).
1. Network Connections, right click on appropriate connection, properties
2. Select ‘File and Print sharing for MS Networks’, properties
3. Server Optimizations tab, select ‘Maximize Data Throughput for Network Apps’, OK
4. Note that you’ll need to reboot for effect
Network Task Offloads
If the system load isn’t going to be network IO bound, consider disabling any offloading capabilities on the NICs. If offloading is required, or will produce potential performance boosts that are desired, consider disabling only “large send offload” (TCP Segmentation Offload) properties on t he NICs.
See the following KB for possible issues and fixes that should be implemented where appropriate: http://support.microsoft.com/kb/KB909665/en-us
For an overview on NDIS offload capabilities available with NDIS 5 (and later) miniport drivers, go here: http://www.microsoft.com/whdc/device/network/taskoffload.mspx
OS Performance Options
Configure the OS performance options appropriately for the server.
1. Right-click on My Computer and select Properties
2. Go to the Advanced Tab, then click the Settings button under the Performance group
3. On the Visual Effects tab, choose the option ‘Adjust for Best Performance’
4. On the Advanced tab, set the Processor Scheduling option to Background Services and the Memory Usage option to Programs
8.3 Naming Structures Enabled
Ensure the 8.3 naming structure is enabled. Verify by opening a command prompt and typing “DIR /X” – this should display both long and 8.3 formatted names where appropriate. If no 8.3 names are returned, edit the following registry key:
HKLM\SYSTEM\CURRENTCONTROLSET\CONTROL\FileSystem\NtfsDisable8dot3NameCreation
The NtfsDisable8dot3NameCreation value must be 0; if the value needs to be changed, a reboot will be needed to make the change take effect. Any directories or components installed prior to making this change will need to be removed and/or uninstalled and recreated/reinstalled before continuing.
UPPERCASE Node/Machine Names
Be sure to configure all nodes/machines in the to be cluster with ALL UPPERCASE machine names. See the following KB:
http://support.microsoft.com/kb/285100
Page File Configuration
Configure the page file on the system/boot partition appropriately (2048MB min, 2048 max) – recommend a single page file per physical volume for maximum performance, although this is not generally a consideration for dedicated SQL Servers, as the database engine will not page at all most of the time (especially with AWE enabled, as all SQL Server managed memory resides in the non-paged pool at the OS level). There is practically no need for pagefile size to be greater than 2GB (to allow for a memory dump in case of a bug check). Start with a relatively small page file size, but allow to grow if necessary to minimize startup time.
Automatic Updates
Turn off and disable the automatic update service. Generally speaking, any updates applied to a SQL Server box will be handled during maintenance windows and specific schedules.
Creating a Boot Delay
In a situation where all the cluster nodes boot up and attempt to attach to the quorum resource at the same time, the Cluster service may fail to start. This may occur for example when power is restored to all nodes at the exact same time after a power failure. To avoid such a situation, increase or decrease the ‘Time to Display list of operating systems’ setting on each node to a different value, with the node to be the primary owner of the cluster having the lowest setting. To find this setting, right-click My Computer, and then click Properties. Click the Advanced tab, and then click Settings under Startup And Recovery.
Screen Saver
Turn off the screen saver on each node in the cluster – configure the screen to turn off instead of using a screen saver (you’d be surprised how many CPU cycles a screen saver can consume on a busy server)
Event Logs
Update the allowed size of the application, system, and security logs to an appropriate size based on the requirements of you environment. Additionally configure the appropriate overwrite settings.
Temp File Locations
Change the Environment Variables for TEMP and TEP from local setting paths to other location (c:\temp or something) for the SQL SERVICE ACCOUNT. This speeds access to temporary data files and locations, rather than having to traverse a buried temp file location (NOTE: This does not have anything at all to do with the temp database or files associated with that database)
Unnecessary Services
Stop and disable any services that are not needed on the server in question. Generally, this includes services such as IIS, Print Spoolers, Auto Updates, etc., but could include any of the following (BE SURE TO VALIDATE WHICH SERVICES ARE REQUIRED IN YOUR ENVIRONMENT PRIOR TO STOPPING/DISABLING):
1. Simple Network Management Protocol (SNMP)
