Tip: If you build a four-node SQL Server cluster, ideally, three of the nodes should be running instances of SQL Server, and the remaining node should be used as the primary failover node.
Explanation: While it is possible to run SQL Server on every node of a four-node SQL Server cluster, this scenario presents a problem should one of the cluster nodes fail. If this happens, then one of the remaining nodes must take over for the failed node. Because this server is already under a load (SQL Server is running on it), it will now have to run two loads, which may be difficult, especially if both servers are already very busy (the failed server and the failover server).
To prevent performance problems should a node fail in a four-node SQL Server, the best overall solution is to only run SQL Server on three of the nodes, leaving the remaining node to be the primary failover node. It is also important that this failover node have very similar hardware as the other three servers in the node, otherwise there may be a significant performance problem when failover occurs.
Version: 2000
Date Added: 8-29-2001
*****
Tip: Backing up SQL Server data from a SQL Server cluster should be done with the utmost diligence. Remember, the shared disk array is not clustered, and is in fact a single point of failure.
Explanation: The point of SQL Server clustering is not to protect data, but to ensure that SQL Server hardware and software is running. Should a clustered node’s hardware fail, or a SQL Server instance fail, then SQL Server will automatically failover to the backup node. But if your shared disk array should fail, SQL Server clustering will be of no value.
Even though your shared disk array most likely is redundant (supporting some version of RAID), it is still possible to lose your data. Because of this, making backups is very important.
The most efficient way to make backups from a clustered SQL Server is to perform disk backups, then to sweep the backups from disk to tape for offsite storage. You can either make the disk backups directly to the shared disk array, then move them to tape, or you can make the disk backups to a network share, then move them to tape.
However you decide to do this, consider creating a redundant backup job. For example, your backup job might consist of multiple redundant steps. A first step might be used to perform a disk backup of your databases to the local shared disk array, and a second step might be to move these backup files to another server. But what if the server you are copying the backup files is down, or has ran out of disk space? One option would be to create a third step that only runs if the second step fails, and is used to copy the backup files to another server. This way, should the file copy part of the job fail, then there is logic in the job to try another alternative. This is just a simple example, but I think you can see the potential of this idea.
Version: 7.0, 2000
Date Added: 8-29-2001
*****
Tip: If your shared disk array hardware supports it, consider making snapshot backups of your SQL Server databases stored on your cluster’s disk array.
Explanation: SQL Server 2000 supports what are called snapshot backups. Essentially, a snapshot backup involves the mirroring of the disks that contain your SQL Server 2000 databases, and then breaking the mirror and using them as a backup.
This option allows you to make backups quickly and without affecting SQL Server’s performance. Of course, your hardware must support this type of backup.
Version: 2000
Date Added: 9-5-2001
*****
Tip: To prevent the mssqlserver and the sqlserveragent services from being failed over because a less critical service on the server failed, configure the less critical services not to failover the cluster.
Explanation: For example, let’s say you are running the Full-Text Search service on your clustered server. Should it fail, you most likely don’t want your entire cluster (running mssqlserver and sqlserveragent) to failover because of it. Be default, this will happen unless you take explicit steps to prevent this. Of course, you may want the key SQL Server services to fail in this case, but many DBAs don’t want this to happen.
Here’s how to prevent this from happening. Start Cluster Administrator, and then right-click on the non-critical service that you want to prevent from forcing SQL Server to failover, and choose “Properties,” then choose the “Advanced” tab.
On this tab, deselect the “Affect this group” check box, then click OK. You are done. Now, should this service stop, then it will not cause the critical SQL Server services to failover. You will want to do this for each non-critical service that affects SQL Server’s services.
Version: 7.0, 2000:
Date Added: 9-5-2001
*****
Tip: Turn off any unnecessary operating system services on your clustered SQL Server that you will not be using. This will reduce the cluster’s overhead and speed performance.
Explanation: Not all of the default services that are turned on when Windows NT Server or Windows 2000 is installed are necessary. The same goes true for SQL Server 7.0 and 2000. There are some SQL Server services, such as the Full-Text Search service, that aren’t required for many servers, and shouldn’t be turned on if they are not needed.
If your cluster servers don’t need IIS or any of the other related web services, be sure they are also turned off. I have often found these services have been installed on servers that don’t need them.
The more of these unnecessary services you can turn off, the less overhead your cluster will use, and the better the cluster’s performance. Keep in mind that you need to turn these services off on all servers in the cluster using the Services utility. In most cases, you can get these non-essential services to Manual or Disabled, in order to turn them off. In other cases, you may be able to install them.
Here are some of the services you might want to consider turning off. This is not meant to be a comprehensive list, nor should you turn them off if you should really have a need for them. You will want to test the effects of turning off these services on a non-production server before trying it on a production server.
NetLogin
Browser
Server
Print Spooler
Task Scheduler
License Logging Service
SNMP
Messenger
Version: 7.0, 2000
Date Added: 9-5-2001