Just think, you have experienced a motherboard failure on a critical SQL Server and you do not know how long it will take to fix it. This of course will affect your business due to the interruption in the abilty to process sales as well as the productivity of your business. Moreover, this situation will leave you totally stressed in not knowing what to do and how to fix this problem. Being a SQL server database administrator is a very stressful and a demanding job at the same time. The stress levels can mount up as the entire business of a company can depend on the availability of SQL server. Hence, it is always wise to know how to handle different casues of failure. Clustering SQL server is an option to boost the uptime of SQL Server. The main advantage of clustering SQL Server is that even if SQL Server fails on one cluster, the other cluster will immediately take over the operation and you will not be forced to stop the operations of your business. First, let’s understand what clustering SQL Server is. It can be defined as a technology in which, if a server fails, the responsibilities or the tasks of that physical server will be taken over by another server automatically. To explain it more distinctively, SQL server is mande up of nodes which are a group of more than two servers which work simultaneously but represent as a single server in the network. To be more precise, when you are in cluster SQL servers, you get a feeling that there is only one SQL server but technically it is made up of more than one server.
Due to some problem, if one of the cluster SQL servers fails, the other server in that cluster takes over the entire responsibility and the end user will not notice any difference during the process of failover and takeover of the responsibilities.
With the help of cluster SQL servers, you can performance tune SQL server to the maximum. Apart from the shared disk and the software which has failed, you can recover all the data from the hardware of the failed server. However, the cluster SQL servers do not protect you from the power surges and other major problems that could damage the motherboard of the server.
With cluster SQL servers you can only reduce the downtime of the application but to performance tune SQL server, you’ll need to have a shared disk array and need to have backups regularly on tapes and build the firewall to provide extra protection.
Once you have decided to have a cluster SQL Server, you will need to choose from any one of the configuration out of Active/Passive cluster or an Active/Active cluster. There are advantages as will as disadvantages in using either of the clusters SQL server types.
In an Active/Active SQL Server, you’ll be running the server in both the nodes and every server will act as independent SQL Server. It is basically one physical cluster SQL server but will have two nodes working in it. Hence, when one fails, the other takes over the operation from the failed server instantly and the user will not even know any difference while working on it.
Where as in an Active/Passive SQL Server there will be two physical servers and one will be working and the other does not operate until there is any problem in the first one. Once the working cluster SQL server fails, the other server takes over from it and starts functioning as regular SQL server without hampering the workflow.
When comparing these two cluster SQL servers on performance, the active/passive is always a superior solution. However, it is high on the cost factor because you will need to have two separate physical servers at the same time. However, judging by the advantages we still recommend this type since it is also easy to configure and administer an Active/Passive server, although it is comparatively heavy on your budget.
To monitor the working of clustering is a technology which is very complex and therefore we shall focus only on the major characteristics in this article. While dealing with a two cluster node, the first SQL server is denoted to as primary node and the second one is denoted to as the secondary node. Whenever the primary node tends to fail the secondary node is designed to take over from the primary node.
Moreover, you may tend to experience the downtime during the period of failover because the failover period lasts for many minutes and it depends on the database size and numbers which are running on the SQL server. During this failover period you may not be able to access the server. Hence it always recommend to performance tune SQL server for smooth operations and access.
However, the reaction of the software purely depends on the kind of software been used. Few of the software briefly halt the operations for the period of the failover and then continue to work without any problem as if nothing had happened. Some other types of software may give you error message on a dialogue box stating the loss of connection with the server and it will need to be started all over again.
There are many advantages if you performance tune SQL server to go along with cluster SQL Server. However, there are some disadvantages as well. Let’s start with the advantages first.
Advantages of cluster SQL server
The downtime during failover period is reduced to minimum.
The response is automatic when the server fails and you don’t need to adjust anything manually.
Upgrades can be performed without hampering the work of the client and forcing other users to logoff during that period.
Enormous reduction in downtime is possible with clustered SQL server when compared with the other routines servers.
You don’t need to rename the servers while clustering because whenever the failover occurs, the users or client machines will not feel any change in the performance in spite of the system failure.
Once the primary server is fixed you can quickly return back to it without wasting much time.
Disadvantages of cluster SQL server
Expensive when compared to other alternatives of failover as stand-by servers and log shipping will cost you more.
Setup time is more when compared to other options.
Maintenance will be more when it is compared to other options.
You need to hire experienced network administrators and database administrators to handle the clustering.
Hardware requirement Clustering performance tune SQL server:
The minimum hardware requirement you’ll need if you are using two clusters SQL Servers are:
CPU with Pentium III processor and above and minimum two servers having at least 256MB RAM
A shared disk array which can support either RAID 5 or RAID 10 with fiber channel or SCSI channel
Both servers need to have one SCSI hard disk at least to control its SCSI.
A fiber channel adapter or a SCSI adapter to control the shared disk array.
Two PCI network cards are required for each server.
For a SQL server to perform without any problems, you’ll need to performance tune SQL servers by having the same hardware, software, configurations and drivers. You’ll be facing lesser problems if you optimize the compatibility of the hardware similarity in the physical servers.
The major considerations you’ll need to keep in mind while you are selecting hardware for your servers in order to performance tune SQL servers is, primarily they’ll need to be on Hardware Compatibility list of Microsoft. The practical reason for this is that if you face any problems, the Microsoft will not provide services to you even if you pay them for their services.
Basic management and setting up of SQL Server Cluster
This topic is so vast that you can write a pretty big book on this. However regrettably, there is limited space left for us to elaborate too much. Therefore, without going too deep into this topic we will discuss some basics that might come handy to you.
It is always suggested to buy a cluster system which is recommended to be bought at least one or two months before you plan to set up the cluster SQL server. In this one or two month’s time, you can actually study all the basic requirements as well as problems you could be facing and believe me you will surely be getting problems.
I would suggest you consider a training session on clustering or at least hire a consultant who has a thorough knowledge about clustering who will help you to plan and implement it without many setbacks. There is no much information available on clustering for you to look around. Hence you’ll need to stand up for yourself during these times.
Before starting the production and after setting up two-cluster systems, you’ll need to test it thoroughly. You can use some clients and databases you were planning to use under it and adjust it well before you start your production. You need to have a plan with which you’ll need to monitor the cluster regularly and ensure that it works as per your requirements.
Taking the backups of the necessary data is better than the best recovery plans and you’ll need to have the regular backups done. Building a disaster recovery plan is necessary because you may get into trouble in case both the nodes fail together.
]]>