Some key tips on SQL Server clustering

A server cluster allows you to connect to many physical servers (or nodes) and serve as a failover partner for each other. The redundancy provided by the cluster gives you more uptime for your critical operations. During the 13 years of using SQL Server, I implemented many clusters, each with its own set of problems. These experiences have enabled me to accumulate a number of skills that will help you achieve clustering easily and successfully.

Server clusters take advantage of the built-in clustering features in the Enterprise Edition of the Windows Server family. In fact, for clustering, using Windows Server 2003 is much better than Windows Advanced server. To maximize the benefits you get from the cluster, you need the right hardware, which involves some expense. Just using a shared disk to put together several servers is not enough, you cannot rely on the fact that separate hardware components may exist in the Windows® directory (formerly known as the Hardware Compatibility List). The system as a whole must exist in the Windows directory. But don't worry, there are other approved, low-cost cluster solutions available. Figure 1 shows a typical cluster configuration.

Figure 1 A Typical cluster

Of course, clustering requires more than hardware-you also need to select the appropriate version of SQL Server 2005. Enterprise Edition supports clustering and other useful features, such as the ability to take advantage of more CPUs, distributed and updatable partitioned views, built-in log shipping, and automatic use of indexed views. If you already have a enterprise Edition license, consider clustering: Do you have two to eight servers necessary to make up a traditional cluster (we'll talk about a single node cluster right away). If you have SQL Server Standard Edition, you can install a two-node cluster.

The Windows Server 2003 Enterprise Edition and Datacenter Edition are shipped with the built-in clustering feature. Installing a cluster requires only running Cluster Administrator. You can add all nodes at the same time, or you can add one node at a time. Similarly, when you install SQL Server, you can choose to install on a separate, non-clustered server, or you can choose to install the virtual instance on a cluster. If you choose to install a virtual instance, you can install it on all nodes of the cluster, or on a subset of nodes, or even on a single node.

Finally, in order to achieve the real goal of the cluster, high availability, you need to provide qualified personnel and the process of walkthrough that follows when a problem occurs. Although clustering is a powerful safeguard against hardware failure, it cannot prevent users from making errors. At midnight, for example, a sleepy-asleep DBA deletes an important table.

Single node cluster

Even though you have only one server at the moment, consider creating a single node cluster. If you do this, you can choose to upgrade to the cluster at a later time without . However, make sure that the hardware you select is located in the cluster portion of the Windows directory.

This is done not just to implement the high availability of nodes that can be added later. If you find that your server does not have the necessary functionality, guess what will happen. This means you need to migrate-both time-consuming and laborious. If you have a single node cluster, the migration process becomes easier and has much less downtime. You need to add new nodes to the cluster, add SQL Server binaries and service packs to the new node, and then failover to the new node. Next, add any updates after any service packs, and then delete the old nodes. Downtime is only the sum of time between the failover time and the addition of the update (if any).

Add node

Because all nodes in a cluster must be the same, you should take action immediately (rather than later) to get another node. If the wait time is too long, the node may be out of production. There was a project where I had to  a node in a SQL Server 2000 cluster. I asked the operating system/network administrator to handle the basic computer build, and then I went to work to add the built computer back to the cluster and prepare it as a SQL Server node. Everything went well until I needed to failover to the new node. But to my great dismay, it did a direct recovery. To make a long story short, although I have prepared detailed documentation on how to build a new cluster, including how to add the Cluster service account and the SQL Server service account to these two nodes, it is clear that the administrator did not follow the document. The administrator did not add these service accounts to thenode, so the permissions they had before would no longer exist.

 section. In SQL Server 2005, things have improved. Setup requires that you set a domain-level group for the SQL Server service account.

Of course, it makes me think more. You can create several scripts that call CLUSTER.EXE to add nodes to a Microsoft Cluster Server (MSCS) cluster. You simply provide the node name for the script, and then the script handles the rest of the work. In an emergency, automation is indeed your friend.