Tip: Maximizing the hardware performance of a SQL Server cluster is very similar to maximizing the hardware performance of a non-clustered SQL Server.
Explanation: When specing out new hardware for a SQL Server cluster, assuming you want maximum performance, you may want to consider purchasing the following “minimal” hardware configuration:
Minimum four CPUs, each with a 2MB L2 cache.
4GB RAM (ECC).
Fibre Channel/RAID controller with enough channels to maximize I/O.
The SQL Server executables (and operating system) should be placed on mirrored drives (RAID 1) and have its own controller channel.
Logs should be placed on mirrored drives (RAID 1) and have its own controller channel.
Tempdb should be placed on mirrored drives (RAID 1) and have its own controller channel.
Quorum disk should be placed on mirrored drives (RAID 1) and have its own controller channel.
SQL Server data files should be placed on drives configured for RAID 0+1 with multiple controller channels (as dictated by maximum of number of physical drives supported per channel).
Disk drives should be 10,000 RPM or higher. Consider a SAN option.
Two 100MBs network cards per node, minimum. Should consider gigabit network cards for public network connection.
Of course, you may want to purchase a system even bigger than the “minimum” one described above.
Version: 7.0, 2000
Date Added: 6-26-2001
*****
Tip: If the SCSI or Fibre Channel controllers you use to access a shared disk array (or SAN) resides on the cluster nodes, then ensure than writeback caching has been turned off on them. If the controller resides on the disk array or SAN, then writeback caching can be left on.
Explanation: If you don’t turn off writeback caching on local controllers, it is very likely that data corruption will occur during failover because the controller does not understand clustering, and it will attempt to overwrite data on the disk. In addition, any transactions stored in the cache, but not yet written to disk, would be lost.
Version: 7.0, 2000
Date Added: 6-26-2001
*****
Tip: When installing SQL Server 7.0 or SQL Server 2000 clustering onto a Windows 2000 cluster, it is important that all non-essential services be stopped. If they are not, then the installation may abort.
Explanation: All non-essential services, whether they are built-in to Windows 2000, or are provided by third parties, must be stopped on all cluster nodes before installing SQL Server clustering. This even includes other instances of SQL Server, the Distributed Transaction Service, IIS, SNMP, and more. In fact, it is easier to list the services not to be stopped than it is to list the ones to stop.
Below is a list of the services on Windows 2000 Advanced Server and Windows 2000 Datacenter Server that should NOT be turned off. (Not all of these services will be found on all servers.) ALL other services should be turned off before installing SQL Server clustering.
Alerter
Cluster Service
Computer Browser
Distributed File System
Distributed Link Tracking Client
Distributed Link Tracking Server
DNS Client
Event Log
IPSEC Policy Agent
License Logging Service
Logical Disk Manager
Messenger
Net Logon
Plug and Play
Process Control
Remote Procedure Call (RPC) Locator
Remote Procedure Call (RPC) Service
Remote Registry Service
Removable Storage
Security Accounts Manager
Server
Spooler
TCP/IP NetBIOS Helper
Windows Management Instrumentation Driver Extensions
Windows NT LM Security Support Provider
Windows Time Service
Workstation
Note: The “Distributed Transaction Coordinator” should be turned off, but not by using the “Services” screen. Instead, it should be set to “offline” using the Cluster Administrator.
Version: 7.0, 2000
Date Added: 6-26-2001
*****
Tip: When installing software on a cluster that is to run SQL Server 2000 under Windows 2000, the order the software is installed in is very critical. If you don’t follow the correct order, your clustering installation most likely will fail, or at the minimum, cause constant headaches.
Explanation: Below is the order you need to install SQL Server 2000 when using Windows 2000 Advanced Server or Windows 2000 Datacenter Server.
Install Windows 2000 on all nodes
Install Windows 2000 SP2 on all nodes
Install all of the relevant Microsoft Internet Explorer 5.5 Updates on all nodes
Create the necessary Cluster Service and SQL Server Service accounts, and add them to the local administrators group on all nodes
Disable NetBIOS on the internal private network
Install Windows 2000 Clustering on all nodes
Create the MS DTC Cluster Resource on all nodes
Stop all unnecessary services on all nodes
Install SQL Server onto all nodes (SQL Server, and SQL Server Clustering, are installed at the same time)
Install SQL Server 2000 SP1 onto all nodes
This is not meant to be a complete description of how to install clustering, only a reminder of the importance of order when installing and configuring SQL Server clustering.
Version: 2000
Date Added: 6-26-2001