The objective of this article is to explain the SQL Server clustering internal process. This article will explain the functions used to interact with the cluster components. The article doesn’t really show how to cluster SQL Server. Most of this article talks about SQL Server 2000 but the same information applies to later versions of SQL Server. The article doesn’t talk about SQL Resources and their functionality in a clustered environment. The functionality of SQL Server resources are the same as in a standard environment (e.g. without clustering). The focus of this article is more on the Resource DLLs used by SQL Server in a clustered environment.
Windows Clustering and SQL Server
Microsoft introduced its first version of clustering software in Windows NT 4.0 Enterprise Edition. Microsoft has significantly improved the clustering software in Windows 2000, Windows Server 2003 and Windows Server 2008. There are two types of clustering technologies: Server Cluster (formerly known as MSCS) and NLB. MSCS or Server Cluster is basically used for High Availability and NLB is used to load balance the TCP/IP traffic. The MSCS or Server Cluster is also known as Failover Clustering. SQL Server supports Failover Clustering. Clustering support for SQL Server was first introduced in Version 6.5 Enterprise Edition. Windows Clustering includes many components such as Cluster Service, Resource Monitors, Node Manager, Membership Manager, Event Log Processor, Failover Manager, and Cluster Database Manager and so on. The whole purpose of Failover clustering is to provide high availability of application resources. Clustering doesn’t really need to decide/know how much CPU and Memory should be utilized by an application. An application running in the clustering environment must be cluster-aware. A cluster-aware application supports the functions executed by the cluster service or its components as mentioned below in the Figure 1.1. There is no way for Cluster Service to know about the availability of resources of an application in the cluster unless the application is cluster-aware. For example, if a node holding the application resources fails, the Cluster Service running on the failed node must be notified in order to start the failover process for the application resources. Cluster Service does this by receiving the responses from Resource Monitor. The Resource Monitors monitor the application resources with the help of Resource DLLs of the application. The Resource DLLs for SQL Server are SQSRVRES.DLL and SQAGTRES.DLL. The SQL Server 6.5 is not a fully cluster-aware application because it didn’t include its own resource DLL and it used generic resource DLL of Windows Clustering software. On other side, SQL Server 2000 and 2005 are fully cluster-aware applications. It supports/responds all the functions executed by cluster service. The main Resource DLLs of SQL Server, which supports all the functions, is SQSRVRES.DLL. Every cluster-aware application uses its own resource DLL to monitor the status of its resources. If application is not cluster-aware then it can’t respond to the functions executed by the clustering software. As an example, SQSVRRES.DLL is the SQL Resource DLL which is used by the Resource Monitor to check the resources availability by performing two checks: IsAlive and LooksAlive. These two are the basic checks performed by the Resource Monitor for every cluster-aware application and are supported by the Resource DLL (SQSRVRES.DLL). A cluster-aware application should implement application specific functions in its Resource DLL. Clustering software doesn’t need to know about application-specific functions. Cluster Service just executes its functions and these functions are supported by the Resource DLLs. SQL Server implements many other functions in its Resource DLL. These functions are SQL application-specific and not related to cluster in anyway. FIGURE 1.1–Cluster Components and SQL Resource DLLs.
In Figure 1.1 you can see the relationship between clustering components and Resource DLLs of the SQL Server. There are three Resource DLLs shown in above figure which controls the SQL Resources: SQSRVRES.DLL, SQAGTRES.DLL and GATHERCL.DLL. The above DLLs are installed when the SQL Server setup realizes that is it going to operate in a clustered environment along with the following files:
VTWDBLIB.DLL SQSRVRES.DLL is the core Resource DLL which controls all the SQL Resources except SQL Server Agent Service resource and SQL Server Full-text search. The SQL Server Agent Service resource is controlled by the SQAGTRES.DLL Resource DLL. The SQL Server Full-text search resource is controlled by the GATHERCL.DLL. Other DLLs just help in failover process but not related to cluster. The SQSRVRES.DLL which sits between Resource Monitor and SQL Resources plays an important role in failover process. Without this the SQL can not function as a cluster-aware application. Also if this DLL is missing the whole purpose of SQL clustering also fails. The above Figure also shows the functions defined in the SQSRVRES.DLL. The SQL-Specific functions are mapped with the cluster-specific functions. For example, Cluster’s IsAlive and LooksAlive functions are mapped with SQL’s IsAlive and LooksAlive respectively. However, there is no static mappings defined in the SQSRVRES.DLL but it knows which function to execute. SQL’s IsAlive and LooksAlive functions, the basic functions, are executed by SQL Resource DLL at a predefined interval defined in the Resource DLL. LooksAlive is executed every 5 seconds and IsAlive is executed every 60 seconds. By default, the default interval for LooksAlive and IsAlive of GATHERCL.DLL is 5 seconds and 30 seconds respectively. Both the queries play an important role for monitoring of the SQL Resources. You can change the LooksAlive and IsAlive internal on the property of the Resource DLL as shown in below figure 1.2 FIGURE 1.2-Changing the default IsAlive and LooksAlive polling interval for SQL Resource DLLs