Scalability and high availability of Microsoft SQL Server 2000 (Part 1)

Thursday Feb 13th 2003 by Marcin Policht

Marcin Policht presents a number of solutions that provide high availability and load balancing of Microsoft SQL Server 2000, focusing on solutions available natively in the SQL Server 2000 and Windows 2000.

In this series of articles, I will present a number of solutions that provide high availability and load balancing of Microsoft SQL Server 2000. I will focus on solutions available natively in the SQL Server 2000 and Windows 2000, such as clustering and federated servers.

Microsoft offers three clustering technologies within the Windows 2000 platform: server clusters, Network Load Balancing clusters, and COM+ Component Load Balancing clusters (the last one available only with Application Center 2000). Out of these three, only server clusters are suitable for clustering SQL Server 2000, although the other two can be used as part of multi-tier configurations, where requests targeting the front-end servers are load balanced using the Network Load Balancing and COM+ Component Load Balancing and redirected in the load balanced fashion to the back-end, server-clustered SQL servers.

Clustering of SQL Servers requires Windows 2000 Advanced Server (with the support for up to two servers in a cluster) or Windows 2000 Datacenter Server (up to four servers in a cluster) and SQL Server 2000 Enterprise Edition. Clustering also requires strict compatibility with hardware requirements provided by Microsoft in the form of Hardware Compatibility List. Its regularly updated version is available on the Microsoft Web Site at A copy of the HCL is also provided in the Support folder on the Windows Server Installation CDs. When reviewing it, make sure that you search for your configuration in the product category, (instead of individual component category), since this is the only way to guarantee that your system will be supported. In addition, make sure that you use identical hardware on all servers participating in the cluster (especially network adapters and storage controllers).

Clusters can operate in active/passive or active/active configuration, depending on the number of SQL Server installations running simultaneously within the cluster. For example, in a two-node cluster with active/passive configuration, one installation of SQL server is active, while the other one, (residing on the other server), becomes active only if the first one fails. In active/active configuration, both installations run simultaneously, actively servicing client requests. In this case, if one of the servers fails, the other one takes on the double load. Obviously, when running Windows 2000 Datacenter Edition on the four-node cluster, you have more flexibility when deciding on the number of active and passive nodes.

As you can imagine, both types of configurations (active/active and active/passive) have their benefits and drawbacks. Having all nodes active fully utilizes your hardware and software investment, but places an extra load on cluster nodes in case one of them fails. This means that when estimating cluster utilization, you need to make sure that at no point the cumulative load on all of the servers does not exceed 100% of their capacity. Otherwise, the failure of one of them might overwhelm the remaining ones and cause them to rapidly degrade or even fail. For example, you should set a limit for the maximum amount of memory available to SQL server. Sum of "max server memory" values on each node should be less than the amount of memory on failover nodes. Otherwise, a SQL server instance might fail to start after failover due to insufficient amount of memory. Using active/passive configuration eliminates this concern, but leaves one or more of your cluster nodes idle during normal operations.

Even though each SQL Server 2000 installation can contain one or more (up to 16) instances of SQL Server, it is recommended to create only a single instance per node. Each instance, in turn, needs to be setup as part of a separate virtual server, (A virtual server is a cluster group that contains a unique network name, IP address, and disk resources). This means that during the planning phase, you will need to ensure that you have allocated additional IP address and network name for each instance of SQL server. Purchase a sufficient number of hard disks to have a separate drive for each instance. It is a common practice to place database and transaction logs on two separate drives; first one, used for database, consisting of 3 or more disks set up as RAID 5 configuration and the other, used for transaction logs, consisting of two disks set up as RAID 1). The names of instances have to be unique across the entire cluster. Clients access an instance using a combination of the name of its virtual server name and the instance name, separated by a backslash. The only exception to this rule is the default instance, which is accessed simply by using the virtual server's name.

Clustered SQL Server 2000 instance, once installed as part of a virtual server, will contain:

  • SQL Network Name resource (one per virtual server)
  • SQL IP Address resource (one per virtual server)
  • Physical Disk resource where database and logs are stored (one or more per virtual server)
  • SQL Server resource (one per instance)
  • SQL Server Agent resource (one per instance)
  • SQL Server Fulltext (Microsoft Search Service Instance) resource (one per instance)

Each instance can function separately from the others, although they have common dependencies on the following components:

  • Microsoft Search Service (that SQL Fulltext resource relies on) - a standard service that needs to be running on each individual node.
  • Microsoft Distributed Transaction Coordinator - limited to a single instance per server cluster. This component is required for distributed queries, two-phase commit transactions, and some replication features.
  • Microsoft Message Queuing - limited to a single instance per cluster.

You should keep BUILTIN\Administrators account in SQL Server System Administrators server role. This group is used by a Cluster service account to communicate to SQL server instances. Also, make sure that the Cluster Service account has permissions on the Computers container in Active Directory sufficient to create the computer object for the virtual server and default SQL Server instance.

SQL Server 2000 is tightly integrated with the clustering software. For example, SQL Server service can be stopped from SQL Administrative tools (like Enterprise Manager or Service Manager), rather than from the Cluster Administrator. The best example of this integration though is the setup process, which I will cover in details in the next article of this series.

As you can see, clustering technology introduces additional cost and complexity, but both are fully justified if you consider scalability and high-availability benefits you can derive from it.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved