Several Key Techniques for SQL Server Cluster

Server clusters take advantage of the built-in cluster functions in Enterprise Edition of the Windows Server series. In fact, Windows Server 2003 is much better for clusters than Windows 2000 Advanced Server. To maximize the benefits you get from the cluster, you need the right hardware, which costs some. It is not enough to splice several servers with a shared disk. You cannot rely on the fact that a separate hardware component may exist in the Windows directory, which is previously called 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

Of course, 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 features, such as the ability to use more CPUs, distributed and updatable partitioned views, built-in log shipping, and automatic use of the index view. If you already have an Enterprise Edition license, consider the cluster: Do you have two to eight servers required to form a traditional Cluster? We will discuss a single-node cluster immediately ). If you have SQL Server 2005 Standard Edition, you can install a two-node cluster.

Windows Server 2003 Enterprise Edition and Datacenter Edition come with built-in cluster functions. 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 it on a separate non-cluster Server or on a cluster. If you choose to install a virtual instance, it can be installed on all nodes of the cluster, or on some nodes, 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 is not just 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 and effort-consuming. 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 SQL Server 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. The downtime is only the sum of the failover time and the time when the update program is added.

Add Node

Because all nodes in a cluster must be the same, you should take action immediately instead of later to get another node. If the waiting time is too long, the node may exit production. Once there was such a project that I had to recreate nodes in the SQL Server 2000 cluster. I asked the operating system/network administrator to process the basic computer build, and then I put into work, added the built computer back to the cluster and prepared to use it as an 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. Long term short, although I have prepared detailed documents on how to build a new cluster, including how to add the cluster service account and SQL Server service account to these two nodes, but obviously the Administrator 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.

It took me a long time to find out why. 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 need to reconstruct a node occasionally, if you need to reconstruct a 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 perform any other necessary custom operations to automatically complete the Security Section. In SQL Server 2005, things have been improved. The installer requires that you set a domain-Level Group for the SQL Server service account.

Of course, this makes me think more. You can create several scripts that call CLUSTER. EXE to add nodes to the 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

Sometimes, the reason for adding nodes to a cluster is not that you want to change the node. You can add more SQL Server instances to the cluster and each instance requires different disk resources. Although multiple instances can run on one node, these instances share 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 that one node does not run any services, while other nodes run an SQL Server instance on each node. In fact, this is the definition of N + 1 clusters: N instances run on N + 1 nodes. 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.

Which solution do you choose? 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 area 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 the old hardware, the new hardware usually runs 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.
After the hardware is in place, you can install and create a new virtual SQL Server, copy the production database, and then test the performance of the new system, in this way, sufficient time is left before the handover date to solve program errors. But don't forget to write the script to exit from the existing server. In the event of a catastrophic failure, it is best to visit support.microsoft.com/kb/246133to update your scripts .)

To minimize downtime, you are likely to have to use Log shipper unless your database is small and no user has established a connection for a period of time. Before the transfer, you can perform log transmission correctly. Then, delete these users, cut and send the final log, and point to the application on the new instance. For more information about how to replace log shipper, see the following database image section .) If you use a 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 can also adopt a low-cost solution, but you need to make more preparations. A cluster supports multiple SQL Server instances, 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 the SQL Server binary file on this disk resource. You can drill the system. When you are ready, close the current SQL Server, remove disk resources from the old SQL Server group, update the dependency, and then bring 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.

Another way is to put both SQL Server instances in your SAN, provided that you have sufficient disk space. Restore production backup and log transmission 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 release 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?

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 partitioned views, which are composed of tables with the same structure and mounted together with union ALL. In addition, CHECK constraints are placed at appropriate locations 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.

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, if you have used table partitions or local partitions as much as possible, but 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 clusters. 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. Making these members part of a cluster can provide you with the reliability required to support performance and achieve 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.

Database images may be the most suitable method to replace clusters. An image involves three elements: the instance that stores the image database is called the subject, and the backup server is called the image. To achieve automatic failover, a third server is also required, which is 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 set an image for each application database, but not for the system database.

An image is a separate SQL Server instance. Unlike a cluster, an 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 there are no other activities on the image except for the image transaction received from the subject. Since SQL Server is already running in an image, failover is usually faster than in a 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 an image 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

Because the distance between the image and the subject can be quite far away, it is wise to select the image for the disaster recovery (DR) plan. Cluster is your first line of defense. However, if you want to use both clusters and images, what will happen? In cluster failover, if you have a witness in your image configuration, the image will become the subject when the cluster SQL Server is transferred online. However, please note that the Failover of the new image is not automatically performed. Therefore, it is recommended that you do not enable automatic failover for your image database when used in conjunction with the cluster.

Disaster recovery is not the only cause of your use of images; it is also useful when you must apply a service package or patch to the subject. 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. The committed transactions on the new master server will wait in queue and wait until they are sent back to the new master ). 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 switch roles between the subject and the image. Failover and recovery only takes several seconds to stop. You can use this method to migrate SQL Server to another computer. However, failure recovery cannot be achieved.

Flexibility of adding virtual servers

Virtualization 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 a server running on the host 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, the guest OS does not have to have the cluster function. Therefore, you can run SQL Server Workgroup Edition in Windows Server 2005, a guest running on Microsoft Virtual Server 2003 in a cluster. Essentially, you will indirectly own the cluster Workgroup Edition

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.

1. Create an SQL Server 2000 failover Cluster
2. Upgrading and deploying SQL Server 2008
3. How to configure SQL Server Remote Backup