SQL Server 2005 Clustering Best Practices

Security Best Practices

  • Cluster nodes and storage should be physically secure.
  • Cluster should be behind a firewall.
  • Do not install antivirus or antispyware on your cluster. Instead, run scans remotely on a daily basis.
  • The cluster service and SQL Server service accounts need to be a member of the Local Administrators group of each node, but they should not be a member of the Domain Administrators group.
    • The above accounts must be domain accounts and a member of the Domain Users group.
    • The passwords should be set not to expire.
  • When the Cluster Service is installed and configured, the Setup Wizard automatically grants the Local Administrator’s group additional, local privileges, listed below:
    • Act as part of the operating system (required for Windows 2000 or later).
    • Back up files and directories.
    • Increase quotas.
    • Increase scheduling priority.
    • Load and unload device drivers.
    • Lock pages in memory.
    • Log on as a service.
    • Restore files and directories.
  • Do not manually remove these privileges; otherwise, the Cluster Service will not function properly.
  • Use the same cluster service and SQL Server service accounts for all clusters in the same domain.
  • The cluster service and SQL Server service accounts should only be used for their own specific purposes.
  • Don’t create any file shares on the shared array, other than if needed for replication.
  • Only trained cluster administrators should have access and permission to use Cluster Administrator.
  • By default, all local administrators of cluster nodes can use Cluster Administrator. This means that all Domain Administrators are SQL Server Administrators by default.
  • Do not let any non-SQL Server Administrators have access to any nodes of the cluster. Or at least, minimize any outside access.
  • Do not remove the Local Administrators group from the Cluster Service Security Configuration.
  • Do not use local accounts on clustered nodes. Always use domain accounts for clusters. This is because during a failover, local account information is not failed over.
  • If the BUILTIN/Administrators account is removed, ensure that the account that the Cluster Service is running under can log into SQL Server for the IsAlive check. If it cannot, the IsAlive check fails.
  • The MSCS Cluster Service account must have sysadmin rights to SQL Server.
  • Don’t access e-mail or browse the Web from a cluster node.

Cluster Administration Best Practices

  • Do not delete or rename the default cluster group, or remove any of the resources from this group.
  • Do not delete or rename any resources from the SQL Server resource group.
  • If you must make a change, then uninstall SQL Server and/or Cluster Services and reinstall them.
  • All SQL Server services should be turned off and on through Cluster Administrator, although not mandatory.
  • If you are running a cluster, keep in mind that if a failover occurs, a single node will have to carry more load. The total workload of each node should not exceed 100%.
  • Review logs on a daily basis.
  • Set up alerts to be sent to you automatically. Don’t install an e-mail client on cluster nodes for this.
  • If you are going to implement replication from a SQL Server cluster, and the SQL Server cluster participates as a Publisher and a Distributor, use a file share located on the cluster disk resource as the snapshot folder. This way, replication will failover when SQL Server fails over. Limit access to this share point.
  • To ensure failover will work when needed, it is important to schedule test failovers periodically in order to test if failover is really working.
  • Don’t run regrebld, which is used to rebuild SQL Server’s registry, on a clustered SQL Server.
  • To prevent the mssqlserver and the sqlserveragent services from being failed over because a less critical service on the server fails, configure the less critical services not to failover the cluster.
  • When you install SQL Server Clustering, the Full-Text Service is installed. If do not plan to use this service, you should configure it so that its failure will not cause SQL Server to failover.

Summary

You might want to consider using the above checklist as your “master list” when building your cluster. In addition, you may want to modify it to meet your own unique needs.

]]>

Leave a comment

Your email address will not be published.