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 http://www.microsoft.com/hcl. 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 BUILTINAdministrators 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.
