SQL Server 2005 Clustering Tips/References

I have copied this over from an older blog. I have cleaned it up a bit to clarify a few areas and added some links.

SQL Server 2005 Clustering Tips/References

This information will supplement the clustering information I wrote in chapter 10 of SQL Server 2005 Practical Troubleshooting: The Database Engine

--- Handy cluster related info

select SERVERPROPERTY('IsClustered') as _1_Means_Clustered

, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as CurrentNode

, SERVERPROPERTY('Edition') as Edition

, SERVERPROPERTY('MachineName') as VirtualName

, SERVERPROPERTY('InstanceName') as InstanceName

, SERVERPROPERTY('ServerName') as Virtual_and_InstanceNames

, SERVERPROPERTY('ProductVersion') as Version

, SERVERPROPERTY('ProductLevel') as VersionNameWithoutHotfixes

select * from sys.dm_io_cluster_shared_drives

select * from sys.dm_os_cluster_nodes

Best practices

Setup (RTM, Service Pack, Cumulative Update, or hotfix)

· The account you use to launch setup must be a local admin on all nodes. However, it is not required that the account you choose to assign as the service account for each service be a local admin. Only setup requires local admin permissions.

· Install and cluster DTC before installing any SQL instance. For Windows 2003, DTC should ideally go in its own group with its own disk and IP address. Second best is to place it in the cluster group and make it depend on the quorum disk and IP address. For Windows 2008 see this blog.

· For SQL 2005, the client tools, SSIS, NS, and RS are only installed on the node where setup was run because they are not cluster aware. If you are installing as many or more instances than nodes, you can run the install program for each instance from a different node so the non cluster aware components are installed on each node. Otherwise install the non cluster aware components later on the other node(s). This means that the service packs and hotfixes must be installed on each node as well so that the non cluster aware components are updated. Setup is run once per instance and that will update all the cluster aware and components for that instance. The first time setup (RTM, SP, hotfix) is run from each node it will also update any non cluster aware components on the box such as the tools, SSIS, NS, and RS.

· All nodes should be configured identically.

· Windows level policies should be the same on all nodes.

· Remote Registry, Cryptography services, and Task scheduler must be started on all nodes during the setup process.

· No Terminal Services users can be logged in on any remote nodes during setup.

· Do not use quotes in the password for SQL service account.

· Do not allow <, >, ‘, “, & in the cluster group names.

· Virtual Server name should be 14 characters or less.

· NIC name cannot have trailing spaces.

· Stop any non-essential applications or services as they may hold open files that setup needs to modify or may otherwise interfere with setup.

· All nodes must have access to setup files without prompting for credentials.

· All disks in all groups must be online during setup.

· All other, existing instances in the cluster must have valid, non-UNC paths for the SQL Server registry keys

· If there are many trusts for the domain the nodes/service account reside in, see KB 910070 before running setup

· For Windows 2003, the complete node must be on HCL, for Windows 2008 each node in the cluster must pass a validation.

· Copy the setup files to the “primary” node or at least make sure all nodes can access the setup files without being prompted for credentials.

· Any mounted drives must have an associated drive letter and be clustered, even if they will not be used by SQL Server. Do not use mounted drives anywhere on a cluster if SQL Server 2000 will exist in the cluster. A mounted drive must be in SQL resource group and SQL Server must depend on them if you want that instance of SQL Server to use them for data or log files.

· Verify you have not installed terminal services, there are no compressed drives, and no node is a domain controller.

· Disable netbios on private NICs.

· Pre-create the domain groups needed by setup.

· Make sure the SQL Server resource is set to "affect the group". Often it’s best to leave SQL Agent, FTS, and DTC to not "affect the group" but that depends on your business needs.

· Test failover of all groups before any SQL setup, test again before any service packs or hotfixes. If any group gets an error during failover, address that problem before running SQL setup.

After setup

· Go back to the cluster administrator and take the SQL Server resource offline. Make SQL Server dependent on the disk(s) and mount points in their group (any disk or mount point where you need to create data files, log files, or full text catalogs) then bring SQL Server, SQL Agent, and FTS back online.

· For the SSIS service, %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml must have the SQL virtual server name instead of “.”

· Configure memory to handle instances moving due to failover.

· If you installed NS, you now have to configure it separately.

· Leave SQL services set to manual in the services applet.

Maintenance

· Use add/remove programs to add/remove nodes.

· Add/remove non-cluster aware components must be done from cmd line.

· Can rename the virtual server name but not the instance name.

· Make IP changes in cluster admin instead of setup.

· Cluster service account must have a login in SQL, must be sysadmin only for FTS.

· Clustered SQL Server startup account can only be a local admin if SQL 2000/7.0 is not installed side-by-side with 2005. Even if a lower version is not installed side-by-side it is best not to make the SQL Server startup account a local administrator or otherwise give it excessive permissions.

· After adding new node, fail to that node to apply SPs/hotfixes.

· For Kerberos on a cluster, you need two SPNs per instance, one with and one without the port. They must belong to the current SQL Server startup account and not to any other accounts.

· If you use SSL encryption, install certificates on all nodes before turning on the SSL encryption.

· If antivirus is installed, see https://support.microsoft.com/kb/250355

References:

· Updated Books Online: https://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

· 915846 Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster https://support.microsoft.com/default.aspx?scid=kb;EN-US;915846

· 819546 SQL Server 2000 and SQL Server 2005 support for mounted volumes https://support.microsoft.com/default.aspx?scid=kb;EN-US;819546

· 913815 Error message when you install a SQL Server 2005 failover cluster on a node: "The drive specified cannot be used for program location" https://support.microsoft.com/default.aspx?scid=kb;EN-US;913815

· 922670 How to use the Add or Remove Programs item in Control Panel to add or remove components for stand-alone installations and clustered installations of SQL Server 2005 https://support.microsoft.com/default.aspx?scid=kb;EN-US;922670

· 910230 How to install SQL Server 2005 Analysis Services on a failover cluster https://support.microsoft.com/default.aspx?scid=kb;EN-US;910230

· 910233 Migrate a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster https://support.microsoft.com/default.aspx?scid=kb;EN-US;910233

· 912397 The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid https://support.microsoft.com/default.aspx?scid=kb;EN-US;912397

· 910851 You receive error messages when you try to set up a clustered instance of SQL Server 2005 https://support.microsoft.com/default.aspx?scid=kb;EN-US;910851

· 926621 Error message when you try to install SQL Server 2005 in a cluster environment: "SQL Server Setup could not validate the service accounts" https://support.microsoft.com/default.aspx?scid=kb;EN-US;926621

· 327518 The Microsoft SQL Server support policy for Microsoft Clustering https://support.microsoft.com/default.aspx?scid=kb;EN-US;327518

· 254321 Clustered SQL Server do's, don'ts, and basic warnings https://support.microsoft.com/default.aspx?scid=kb;EN-US;254321

· 942176 Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service https://support.microsoft.com/default.aspx?scid=kb;EN-US;942176

· 922209 The SQL Server 2005 Setup program does not remove all IP address cluster resources when you uninstall SQL Server 2005 https://support.microsoft.com/default.aspx?scid=kb;EN-US;922209

· 295732 How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installed https://support.microsoft.com/default.aspx?scid=kb;EN-US;295732

· 263712 How to impede Windows NT administrators from administering a clustered instance of SQL Server https://support.microsoft.com/default.aspx?scid=kb;EN-US;263712

· 932881 How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult https://support.microsoft.com/default.aspx?scid=kb;EN-US;932881

· 934749 BUG: Error message when you try to install SQL Server 2005 Service Pack 1 or SQL Server 2005 Service Pack 2 from the existing active node: "The product instance <InstanceName> been patched with more recent updates" https://support.microsoft.com/default.aspx?scid=kb;EN-US;934749

· 283811 How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005 https://support.microsoft.com/default.aspx?scid=kb;EN-US;283811

· 910070 FIX: The SQL Server 2005 Setup program may take much longer than expected to finish running https://support.microsoft.com/default.aspx?scid=kb;EN-US;910070

· 909967 How to uninstall an instance of SQL Server 2005 manually https://support.microsoft.com/default.aspx?scid=kb;EN-US;909967

Windows 2003 SP2 is better than SP1 because:

· 918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005 https://support.microsoft.com/default.aspx?scid=kb;EN-US;918483

· 922658 SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer https://support.microsoft.com/default.aspx?scid=kb;EN-US;922658

· 904160 Network performance is slower than expected in Windows Server 2003 SP1 https://support.microsoft.com/?id=904160

Cluster specific:

· 923830 Recommended hotfixes for Windows Server 2003 Service Pack 1- based server clusters https://support.microsoft.com/default.aspx?scid=kb;EN-US;923830