Several key tips on SQL Server CLUSTERS

Overview:
Run SQL Server on the Cluster Hardware and software requirements Add a node to a cluster Cost-saving methods

Server clusters allow you to connect many physical servers (or nodes) as failover partners for each other. The redundancy provided by the cluster brings more normal operations for your key operations.

Time. During the 13 years of using SQL Server, I implemented many clusters, each of which had its own series of problems. These experiences have accumulated many skills that help you easily and successfully implement clusters.

The Server cluster uses Windows Server
^{Built-in cluster functions in Enterprise Edition. In fact, for clusters, the use of Windows Server 2003 is better
Windows 2000 Advanced Server
Much better. To maximize the benefits you get from the cluster, you need the right hardware, which costs some. It is not enough to use a shared disk to piece together several servers.
The fact is that a separate hardware component may exist in WindowsDirectory (formerly known as the hardware compatibility list. The system as a whole must exist in the Windows directory. But don't worry, there are some other approved, low-cost cluster solutions available. Figure 1 shows a typical cluster configuration.}

Figure 1 A typical cluster (click the image to get A smaller view)
Figure 1 A typical cluster (click the image to get A larger view)

When
However, clustering requires more conditions than hardware-you also need to select the appropriate version of SQL Server 2005. Enterprise Edition
Supports clustering and other useful functions, such as using more CPU, distributed and updatable partitioned views, built-in log transfer, and automatic use of the index view. If you already have
Enterprise Edition license, consider the cluster: Whether you have two to eight servers necessary to form a traditional cluster (we will discuss a single-node cluster as soon as possible ). If you have
SQL Server 2005 Standard Edition, you can install a two-node cluster.

Windows
Server 2003 Enterprise Edition and Datacenter Edition
Comes with the built-in cluster function. To install a cluster, you only need to run the Cluster Manager. You can add all nodes at the same time or one node at a time. Similarly, when installing SQL Server
You can choose to install on a separate non-cluster server, or you can choose to install the virtual instance on the cluster. If you choose to install a virtual instance, it can be installed on all nodes of the cluster, or on some
Or even only one node.

Finally, in order to achieve the true goal of the cluster, that is, high availability, you must provide qualified personnel and the pre-drill process that is followed when a problem occurs. Although the cluster is a powerful guarantee to prevent hardware faults, it cannot prevent user errors. For example, at midnight, a sleepy DBA deleted an important table.

Single-node cluster

Although you only have one server at the moment, you can also consider creating a single-node cluster. If you do this, you can upgrade to the cluster later without rebuilding. However, make sure that the hardware you selected is in the cluster section of the Windows directory.

This
The sample is not only used to achieve the high availability of adding nodes later. If you find that your server does not have the necessary functions, you can guess what will happen. This means you need to migrate-
Time-consuming and laborious. If you have a single-node cluster, the migration process becomes easier and the downtime is much lower. You need to add a new node to the cluster
Add the binary file and service package to the new node and then fail over to the new node. Next, add the update program after any service package and delete the old node. Downtime is only the failover time and Addition
The sum of time of the new program (if any.

Add Node

By
All nodes in a cluster must be the same. You should immediately (rather than later) Take action to obtain another node. If the waiting time is too long, the node may exit production. Once there was such a project, I
Have to go to SQL Server 2000
Node reconstruction in the group. I asked the operating system/network administrator to process the basic computer build. Then I put into work, added the built computer back to the cluster, and prepared to use it as SQL Server.
Node. Everything went smoothly until I needed to fail over to a new node. However, I am very frustrated that it directly implements fault recovery. To put it short, although I have prepared
Detailed documentation, including how to bind the cluster service account and SQL Server
The service account is added to these two nodes, but the Administrator obviously does not follow this document. The Administrator did not add these service accounts to the Reconstruction node, so the permissions they had before the reconstruction no longer exist.

Me
It took a long time to find the cause. One day, I suddenly thought of checking the identity of the Local Group members. After I added these two accounts, the Failover went smoothly. So I started to think. Although you only
It is only occasionally necessary to re-build the node, but if you need to re-build the node, it is in an emergency. Although I have provided documents, people do not use them. You only need to write a short script to add the two accounts and
Any other necessary custom settings can automatically complete the Security Section. In SQL Server 2005, things have been improved. The installer requires that you use SQL Server
Set a domain-Level Group for the service account.

Of course, this makes me think more. You can create several scripts that call CLUSTER. EXE to add nodes to Microsoft^{Cluster Server (MSC) Cluster. You only need to provide the node name for the script, and then the script processes the remaining work. Automation is indeed your friend in an emergency.}

N + 1 Cluster

Yes
The reason for adding a node to the cluster is not that you want to change the node. You can
Instances are added to the cluster and each instance requires different disk resources. Although multiple instances can run on one node, these instances share the CPU and
RAM, which may cause performance degradation. Ideally, only one instance is running on one node. But how can we ensure this in the case of failover? Simple: the answer is, there is a node that does not
Run any service, while other nodes run an SQL Server instance on each node. In fact, this is the definition of N + 1 clusters: Running N on N + 1 nodes
Instances. The additional nodes are slave nodes.

Upgrade SQL Server

Upgrading the SQL Server cluster instance is not timid: Build the cluster for only one reason-you need to run properly. However, SQL Server 2005 provides many enhancements you want to use. Therefore, if you are preparing to upgrade, you can continue without too much downtime.

You
Which scheme will be selected? First, let's take a look at the most costly solution: creating the entire cluster. This means creating several new servers or creating a new storage Region network.
(SAN ). You may retain the existing network switches, but this is about all you want to keep. Obviously, this method is costly, but it has some advantages. Compared with old hardware
The rows are usually much better, because the disk capacity and speed have increased. Therefore, your performance will be rapidly improved by using only new hardware. You may even rent equipment to stay ahead.

Hard
After installation, you can create a new virtual SQL
Server, copy the production database, and then test the performance of the new system, so that there is sufficient time before the handover date to solve the program error. But don't forget to write the script to exit from the existing server.
(In case of a catastrophic failure, you 'd better visit support.microsoft.com/kb/246133 to update the login build script .)

Is
To minimize the downtime, you are likely to have to use Log transfer unless your database is quite small and no user has established a connection for a period of time. Before the transfer, you can perform log transmission correctly. Connect
, Delete these users, cut and send the final log, and then point to the application on the new instance. (For more information about the log transfer substitution method, see the following database image section .) If you use
DNS alias, you may not even need to point to the application on the new instance, but only need to update the DNS
Alias. The advantage of this method is that if you have only performed a part of the migration, but you must roll back to the original state, you have at least the original file.

You
You can also adopt a low-cost solution, but you need to make more preparations. One cluster supports multiple SQL servers
Instance, but each instance must have its own disk resources. Therefore, when dividing the San, set aside a lun for future upgrade. To perform the upgrade, Install SQL on this disk Resource
Server binary file. You can drill the system. When you are ready, disable the current SQL Server and remove disk resources from the old SQL Server
Remove from the group, update the dependency, and make the new SQL server instance online. Connect to the database in the old instance, and then start and run. (You have backed up all the data in advance, right ?)

This is a low-cost method, which requires some risks. If a fault occurs, you cannot separate the database from the new instance and return it to the original location. Your operation has been simplified to restoring from backup-this means a long downtime.

Also
One way is to put both SQL Server instances in your San
If you have enough disk space. Restore the production backup (and log transfer) to a new instance and continue with the previous steps. But now you have a retreat. In addition, once the migration is completed, you can
Releases the San resources occupied by the old instance. You only need to add additional disks.

Load Balancing

Let's first expose such a common misunderstanding. The MSC cluster is used for high availability, not for load balancing. In addition, SQL Server does not have any built-in and Automatic Load Balancing functions. You must achieve Load Balancing through the physical design of the application. What does this mean?

With
As Tables grow, you may expect lower performance, especially when table scan operations are involved. When the number of rows reaches millions or billions, the traditional solution uses the partitioned view, which consists of several
A table with the same structure and associated with union all. In addition, CHECK
Constraints to differentiate these Member tables, which prevents data replication across partitioned views. If the columns used in the CHECK constraint are also part of the primary key, the view is updatable.

For example
If the member table is in its own file group, if the files in these file groups are located on different physical drives, you will get better disk performance. These tables can even be located in different databases.
However, in SQL Server 2005, as long as all data is in the same database, you can use table partitions, which is much easier to implement.

However
Yes. If you have used table partitions or (local) partitioned views as much as possible, the performance is still low. If you have SQL Server 2000 or SQL Server
2005, you can use the distributed partition view. The main difference is that the Member tables can be located on different SQL Server instances, and these instances can be installed on N + 1
Cluster. Why are you encouraged to do so? If any member table in the partitioned view is transferred offline, the entire view is also transferred offline. Supporting these members as part of the cluster
Performance and reliability required for load balancing.

Do you really need a cluster?

Maybe you have some backup servers that have nothing to do, but these servers are not in the cluster section of the Windows directory. If you only need to purchase new servers to support clusters when these servers are available, this is a waste of shame.

Quantity
The database image may be the most suitable method to replace the cluster. The image involves three elements: the instance that stores the image database is called the subject; the backup server is called the image; To achieve automatic failover, the third
A server called the witness. In short, transactions in the database on the subject will run again in the image. When the subject fails, if there is a witness, the database will automatically fail over to the image. You must
Application database, but not the system database.

The image is a separate SQL Server
Instance, unlike the cluster, the image can be located thousands of miles away. The cache is filled with the update activity that occurs because the transaction is replicated from the subject. Of course, it can also be assumed that in addition to receiving image tasks from the subject
There are no other activities on the image. Since SQL Server
It is already running in the image, so failover is usually faster than in the cluster. Because at least some of the cache is ready, the initial performance is not as low as in the cluster solution. Note that
When a database fails over, the roles of the subject and image will be exchanged.

The disadvantage of database images is that the total disk capacity required is twice that of the cluster. If you want to run in synchronization mode and do not want to lose any data, you need more CPU processing capabilities. As I said, high cost is required to achieve high availability.

Combination Method

By
The distance between the image and the subject can be quite far away, so for disaster recovery (DR)
It is wise to select an image. Cluster is your first line of defense. However, if you want to use both clusters and images, what will happen? In cluster failover, if your image configuration is
If you have a witness, when the cluster SQL Server
When it is transferred to the online status, the image will become the subject. However, please note that failover is not automatically performed for the new image returned from the new subject (cluster. Therefore, it is best not to use your image
The database enables automatic failover.

Disaster recovery is not the only cause of your use of the image; when you must provide the subject application service package or patch,
Images are also very useful. In this case, you can manually fail over to the image. When the application service package or patch is used, the old master server is temporarily offline and committed transactions occur on the new master.
Will wait in queue, waiting for the new image to be sent back (old subject ). After the service package or patch is installed, it will be synchronized. In the end, the two servers will be completely synchronized. Now you can
And the image. Failover and recovery only takes several seconds to stop. You can use this method
Migrate to another computer. However, failure recovery cannot be achieved.

Flexibility of adding virtual servers

Virtual
It allows you to run one or more operating systems concurrently on one physical server. Virtualization software adds another layer of functionality to the cluster concept because you can add the software to the cluster. Therefore, if the host is on it
If a running server fails, the host and its guest OS will fail over to the backup node. This may be the easiest way to migrate the guest server. In addition, Guest OS
You do not need to have the cluster function. Therefore, you can run Windows on a guest running on Microsoft Virtual Server 2005 in a cluster.
Server 2003 runs SQL Server Workgroup Edition. Essentially, you will indirectly own the cluster Workgroup
Edition (see figure 2 ).

Figure 2 Using a virtual server (click the image to get a smaller view)
Figure 2 Using a virtual server (click the image to get a larger view)

Under Control

If you are responsible for implementing SQL Server, make sure that your Server is always available. Server Group Assembly helps ensure that your server is always available. This article provides some hard-won tips to help you get started. You can find more useful information in the "cluster resources" sidebar.

Tom Moreau, PhDZeng
He has a Bachelor of Science and doctorate degree and has MCSE and mcba certification. Is he a free consultant or SQL Server?
Database management, design, and implementation experts now reside in Toronto. Tom has been using SQL Server since, and has served
MVP. He has written more than 100 articles and has co-authored books on SQL Server. Thanks to SQL Server MVP Geoff
Hiten provides useful information.