SQL Server Clustering get pre-installation information and choose the best practices for your environment
Before installing the SQL Server cluster there must be a checklist in order to ensure you haven't compromised on any hardcore requirement. In this regard Microsoft suggests the hardward that must be listed on this Windows Catalog and Hardware Compatibility, otherwise you may not be able to utilize or install full set of clustering on your chosen hardware. This is because the special hardware compatibility is necessary when you have to implement the failover and that too on using SAN, these 2 blog links here [Clustering-SAN_BestPractices & RAID_Deployment_Clustering] talks more about setting up the SAN & usage of RAID for your setup, further if your cluster involves in geographical based then make sure to have a thorough checkout on the list such as network latency and shared disk support.
As you may be aware the quorum disk resource is very important for the sharing of cluster source, as it contains a master copy of the server cluster configuration. As the documentation refers depending on the type of server cluster you implement, the quorum disk might or might not be a physical disk on the shared cluster disk array. Although it is best to reserve a complete cluster disk for use as the quorum disk, resources other than the quorum resource might be permitted to access the quorum disk.
So lets walk through the set of best practices that I have found very useful within my journey of SQL Server 2005 installation:
- By default you can only install the SQL Server 2005 binaries with RTM, ensure the account you are using must be a part of local admin group on all the nodes that are involved in this installation.
- MSDTC service, this must be installed with cluster before installing the SQL instance binaries. By default it should go on its own group, IP address & disk. If its not possible then make sure to place it in a cluster group that depends upon quorum disk & ip address.
- Not all of the tools are cluster-aware, in this regard Integration Services, Reporting Services & Notification Services (if used) will be installed on the first node where setup runs. So you have to install these tools on each of the nodes that are part of cluster group, coming to the Service Pack or hotfix patches issue they must be installed on each of these nodes where tools are initiated. This is to ensure all the nodes are identical in terms of tools usage & patch levels.
- The Security policies (local & domain) must be similar on all of the nodes, few special services such as Remote Registry or Cryptography services must be started on all of the nodes before installing the SQL.
- All the terminal services connections must be disconnected on the server/nodes when installing.
- When it comes to naming convention ensure the quotes are not used in password for any of service account, also no special characters on group names. Also due to the netbios limitation the Virtual Server name must be less than 14 characters only, make sure to type in with UPPERCASE when node name is defined.
- As per the Brad McGeehee's Clustering Best Practices article you must stop any non-essential services or applications on the server, to avoid any open files or registry keys that are dependant on clustering. All the nodes must be available online and installation account must have proper privileges (ADMIN) to access the credentials.
- If installing between the domains then ensure the TRUST is enabled between the domains for the node service account (use similar name account with password).
- Best to copy the SQL setup files on the first node from the media, in order to reduce any contention during the setup & access is set to local disks from the other nodes too.
- Usage of mounted drives should be associated with the drive letter along with clustered, in a part of SQL resource group.
- Ensure to check that no external drives or compressed volumes exists on the server and cluster should not be installed on a Domain Controller server.
- If you are using any Private NIC components then ensure to disable NETBIOS services and ensure to setup the domain groups before the setup runs.
- Once the SQL Server is installed ensure to check the connectivity using the Managment Studio on all the nodes, also perform the manual failover to see or avoid any initial errors on the setup tasks.
- To do this you have to open the Cluster Administrator (CLUADMIN) and put the SQL Server resources as offline and then get back to online on the node where you want to test the failover to go.
- A note on SSIS service usage on the nodes, ensure to change the MsDtsSrve.ini.xml file to have the virtual server name instead of '.' (which is by default). You can find the specific XML file under the [%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\] directory.
- If you have usage of Notification services then you have to install the that setup seperately. I recommend to review http://msdn2.microsoft.com/en-us/library/ms171338.aspx page before going through this path.
- If you need to remove or add nodes then you must use 'Add/Remove programs' from Control panel mode, in case any non-cluster aware services are to be installed or removed then better to go with command line operation.
- Few gotchas on maintenance are:
- you can only rename the VirtualServer name and if you need rename instance name then you have to reinstall complete cluster again!
- if needed to change ip address then best to use with Cluster Admin program.
- always failover to other node to install the service pack or hotfixes.
- if you are using the SSL encryption within your application then ensure to install the relevant certificates on all nodes before setting it up.
- By default the Cluster service account must be a local admin and SYSADMIN on SQL Server instance, if you have previous versions installed on the same server (due to limitations) then ensure to have a seperate account for this cluster account, to avoid any excessive permissions on other instances you may not need to.
Using the following TSQL you can get clustering information (as the Technet article SQL Server 2005 Practical Troubleshooting)
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
Also the DMVs for Cluster will get more information, ensure to update the local copies of Books Online with latest version. I hope to cover most of the chat related to Cluster here and if any further information will be updated here.