Export (0) Print
Expand All

Implementing Failover Clustering

Updated : November 14, 2002

This chapter teaches you how to increase the availability of a Microsoft SQL Server data center by using failover clustering. After reading this chapter, you will be able to configure each component in this high-availability solution, including the storage device, network, Microsoft Cluster Service (MSCS), Microsoft Distributed Transaction Coordinator (MS DTC), and Microsoft SQL Server 2000.

The implementation steps include annotation to help you understand the selected configuration choices. The configuration options presented in this chapter have been designed using input from the field, reviewed thoroughly by Microsoft Product Support Services (PSS), and tested by an independent team to ensure accuracy and functionality.

Failover clustering is only part of achieving a highly available data center. To achieve a highly available data center, you must also follow the processes outlined in the accompanying Planning Guide.

On This Page

Understanding Failover Clustering
Deploying Failover Clustering
Failing Over to a Secondary Node
Failing Back to the Original Node
Restoring a Node After a Catastrophic Failure

Understanding Failover Clustering

Failover clustering is a high-availability solution provided by SQL Server 2000 Enterprise Edition that uses the clustering services provided by either Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter Server.

Failover clustering is the best solution for simple, quick, and automatic failover. It is the only high-availability solution that provides absolute transactional currency and consistency and makes failover virtually transparent to the client. Consider using failover clustering as your first choice for high availability ahead of log shipping and transactional replication.

Failover clustering maintains at least one standby server in an MSCS cluster in case the primary data center server fails. Windows 2000 Advanced Server supports two-server clusters, and Windows 2000 Datacenter Server supports up to a four-server cluster. When the MSCS detects that the primary server has failed, it automatically starts the cluster resources on a standby server that were running on the failed server. MSCS then redirects all client traffic to the standby server. You can also manually failover to a standby server. With failover clustering, all committed transactions are always available through the standby server after the primary server fails.

Note: Failover clustering does not permit widely separated nodes (stretch clusters) or remote disk mirrors without hardware and software assistance provided by third-party hardware vendors.

To implement a failover clustering solution, you need to have certain things in place:

  • You need people in the following roles:

    • Database administrator

    • Shared disk/network storage administrator

    • Network administrator

  • You need the following software:

    • Windows 2000 Advanced Server or Windows 2000 Datacenter Server

    • SQL Server 2000 Enterprise Edition

  • You need systems and system components that are certified to work together and with the chosen Windows 2000 operating system edition, and are certified for failover clustering. Certification of systems and system components for failover clustering is more stringent than operating system certification. In addition, Windows 2000 Datacenter Server has more stringent certification requirements than Windows 2000 Advanced Server. To find systems and system components that are certified by Microsoft for failover clustering with either operating system, search the "Hardware Compatibility List" on the Microsoft Web site at http://support.microsoft.com/kb/131900.

For more detailed information about failover clustering, see "SQL Server 2000 Failover Clustering" on the Microsoft Web site at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx.

Deploying Failover Clustering

You will be successful at implementing failover clustering if you avoid shortcuts and parallel installations. If the setup program fails or you cancel the setup program for any reason, completely remove the previous setup before attempting another, and remove any Domain Name System (DNS) entries relating to the failed setup. Also, verify that the service names and IP addresses you plan to use for MSCS and SQL Server are unused immediately before you install MSCS and SQL Server.

This chapter assumes the following with respect to each participating server in the failover clustering solution:

  • Windows 2000 Advanced Server or Windows 2000 Datacenter Server is installed on a mirrored volume.

  • The most recent operating system service pack is installed.

    Note: Restoring disks to a cluster in the event of SAN failure requires at least Windows 2000 Service Pack 3.

  • MSCS has not been installed.

  • The server is a member of the same Windows domain as each other server.

  • A SAN or disk array is connected.

  • All disks are formatted for New Technology File System (NTFS) and are not configured as dynamic disks.

  • At least two network interface cards (NICs) are installed.

  • The server is connected to the corporate network and is connected to each server on a private network.

  • SQL Server (any version) is not installed.

The logical design of the failover cluster built in this chapter is described below, followed by the steps required to build this high availability solution.

Understanding the Logical Design

Figure 1.1 shows the logical design of the failover cluster that is described in this chapter. Although a storage area network (SAN) is not required to implement a failover cluster solution, it is frequently used for shared storage.

Cc917704.haso0101(en-us,TechNet.10).gif

Figure 1.1: Logical design diagram of a single instance cluster using a SAN

Each node in the Windows 2000 failover cluster runs Windows 2000 Advanced Server or Windows 2000 Datacenter Server. MSCS maintains a heartbeat between the nodes by using a dedicated network to ensure each node remains alive. One or more SQL Server virtual servers run within the Windows 2000 failover cluster. The active node for the virtual server exclusively accesses and maintains the data and the log files for that virtual server. The SAN is connected to each node using fibre. Within the SAN, the SAN software maintains mirrored copies of the data and log files as well as the temporary and backup files.

In a multiple instance cluster, each virtual SQL Server requires a separate group of disks in the SAN for the data, log, temporary, and backup files. Each virtual SQL Server requires exclusive control of its disks. MSCS assigns control to the active node for a virtual SQL Server.

The generalized steps required to implement this logical design are below, followed by the steps in detail.

To implement a failover cluster with SQL Server

  1. Create and configure domain user accounts, determine unique server and instance names, and obtain an allocation of corporate IP addresses.

  2. Configure the network.

  3. Configure the storage devices.

  4. Install and configure MSCS.

  5. Upgrade MS DTC.

  6. Install and configure a SQL Server 2000 virtual server instance.

  7. Apply the most recent SQL Server 2000 service pack.

  8. Perform additional configuration tasks.

Configuring Accounts and Names and Obtaining IP Addresses

Before configuring a Windows 2000 failover cluster with SQL Server, you must create and/or configure domain user accounts for SQL Server, configure domain user account permissions for MSCS, obtain server names for MSCS and SQL Server, and allocate IP addresses for the heartbeat and the corporate network NICs. Use the following information to configure accounts and names, and obtain IP addresses.

  • Domain user accounts — Create and configure the following domain user accounts:

    • A domain user account to administer the failover cluster — Make this account a member of the Administrators local group on each node of the failover cluster.

    • A domain user account for the SQL Server and the SQL Server Agent services — This account does not need to be a member of the Administrators local group in any node of the failover cluster.

  • Server names — Have the IT department approve the following names:

    • A virtual cluster name — This name must be unique within the domain.

    • A virtual SQL Server name for each SQL Server instance — These names must be unique within the domain and on the local subnet if multiple domains reside together on the same subnet.

    • An instance name for each SQL Server instance — These names must be unique within the failover cluster.

  • New static IP addresses — Have the IT department allocate static IP addresses for each of the following:

    • A virtual cluster IP address

    • A virtual IP address for each SQL Server instance

    • An IP address for each corporate network NIC

These addresses must be unique on the corporate network and must be on the same subnet.

Note: In a cluster, each SQL Server instance must have its own virtual IP address, virtual server name, and SQL Server instance name. This enables a SQL Server instance to fail over to a secondary node without affecting other SQL Server instances on the same physical node.

Configuring the Network

Before you install MSCS, you must assign the allocated IP addresses for each corporate network NIC, including the default gateway and preferred DNS server for each NIC. You must also assign static IP addresses to the NICs in each server that will carry the heartbeat and other internal cluster communications among the servers in the failover cluster.

The heartbeat NICs should be on their own network. This reduces the contention between cluster administration and user traffic. You can use any IP addresses you want because there will be no other NICs on this network.

To configure a NIC that connects to the corporate network

  1. On the desktop, right-click My Network Places, and then click Properties.

  2. In Network and Dial-up Connections, right-click the network connection for the NIC that connects to the corporate network, and then click Properties.

  3. In Connection Properties for this corporate NIC, click Internet Protocol (TCP/IP), and then click Properties.

  4. In Internet Protocol (TCP/IP) Properties, type the IP address allocated to this corporate NIC.

    Change the subnet mask only on the advice of a network specialist if your network uses subnetting.

  5. Type the IP address of the default gateway.

  6. Click Use the Following DNS Server Addresses, and then type the DNS server address.

  7. Click OK to save the new TCP/IP properties.

  8. Click OK to save the new connection properties for this NIC.

  9. In Network and Dial-up Connections, rename the connection to Corporate to clearly identify its function.

Note: Repeat this process if you use a second corporate NIC that provides redundant connectivity to the corporate network. Name this redundant corporate connection Corporate2.

To configure the NIC that connects to the private network

  1. On the desktop, right-click My Network Places, and then click Properties.

  2. In Network and Dial-up Connections, right-click the network connection for the NIC that connects to the private network, and then click Properties.

  3. In Connection Properties for this private NIC, click Internet Protocol (TCP/IP), and then click Properties.

  4. In Internet Protocol (TCP/IP) Properties, type an IP address for this private NIC.

    You must use an IP address for each private NIC that is on the same subnet as the IP address you use for the other private NICs in the cluster. Use the automatically completed subnet mask. It is common to use an IP address of the form 10.1.0.x for the private segment of the network.

  5. Click OK to save the new TCP/IP properties.

  6. Click OK to save the new connection properties for this NIC.

  7. In Network and Dial-up Connections, rename the connection to Heartbeat to clearly identify its function.

Note: Repeat this process if you use a second private NIC that provides a redundant heartbeat. Name this redundant private connection Heartbeat 2.

Configuring Storage Devices

Each vendor has different procedures for configuring its storage devices. Follow the procedures provided by the vendor and configure the SAN as follows:

  • Use the SAN for SQL Server only, not for Windows 2000.

  • Create a mirrored volume for the quorum.

  • Create three mirrored volumes for each SQL Server instance — one each for data files, transaction log files, and temporary files.

Before configuring the storage devices for MSCS to use, turn off all but one of the nodes to avoid configuration errors.

Preparing the Drives

To prepare the storage device for use, you must write disk signatures, create partitions, and format drives. Initialize the drives used in a cluster as basic drives.

To initialize a drive for use in a cluster

  1. Right-click My Computer, and then click Manage.

  2. Expand Storage, and then click Disk Management.

  3. Right-click a drive that you want to initialize, and then click Initialize.

  4. In Initialize Disk, select the disk(s) to initialize, and then click OK.

To create a partition and format a drive

  1. In Computer Management, right-click the drive that you want to partition, and then click Create Partition.

  2. On the Welcome to the Create Partition Wizard page, click Next.

  3. On the Select Partition Type page, click Primary Partition, and then click Next.

  4. On the Specify Partition Size page, click Next to use all of the disk space on the drive for this partition.

  5. On the Assign Drive Letter or Path page, assign a drive letter, and then click Next.

  6. On the Format Partition page, click Format Partition.

  7. In Formatting, set the formatting options as follows:

    1. In File System to Use, specify NTFS.

    2. In Allocation Unit Size, specify Default.

    3. In Volume Label, specify a meaningful name

    Do not select the Enable File and Folder Encryption check box.

  8. Click Next.

  9. On the Completing the Create Partition Wizard page, click Finish.

To format a partitioned but unformatted drive

  1. Right-click My Computer, and then click Manage.

  2. Expand Storage, and then click Disk Management.

  3. Right-click a drive that you want to format, and then click Format.

  4. In Format Disk, click OK.

Note: Repeat these procedures for each drive.

Specifying Drive Letters

Specify drive letters for each drive in the cluster to ensure that they do not change dynamically based on the drives that are online. If a drive letter that SQL Server uses for a data or log file changes after SQL Server is installed, SQL Server cannot attach the database when SQL Server starts.

Note: If you created a new partition in the preceding procedures and assigned a drive letter, you do not need to complete the following procedure.

To specify drive letters

  1. Right-click My Computer, and then click Manage.

  2. Expand Storage, and then click Disk Management.

  3. Right-click a drive that the cluster uses, and then click Change Drive Letter and Paths.

  4. In Change Drive Letter and Paths, click Edit.

  5. In Edit Drive Letter or Path, assign a drive letter from the list of available drive letters, and then click OK.

  6. Click Yes to confirm that you want to change the drive letter.

  7. Repeat this process for each drive that the cluster uses.

When you add more nodes to the cluster, they inherit these drive letters from the cluster configuration information stored on the quorum drive.

Installing and Configuring MSCS

When you install and configure MSCS, set up only one node at a time. Turn off all other nodes that are connected to the shared storage device while you install MSCS on the first node. You risk disk configuration errors if you attempt to configure MSCS on the first node while other nodes are connected and running. After you install MSCS on the first node and the drives in the shared storage device are allocated, you can install MSCS on additional nodes without turning off nodes on which MSCS is running.

Note: If you are reinstalling MSCS or recovering from a failed MSCS installation, remove any entries in the DNS server that relate to the failed MSCS installation before you reinstall. Stray or duplicate IP addresses or names can prevent a clean installation.

Install MSCS on all nodes in the cluster. The first node of an MSCS installation requires more setup than an additional node in the same cluster.

To add MSCS on the first node

  1. On the first server on which you are installing MSCS, click Start, point to Settings, and then click Control Panel.

  2. In Control Panel, double-click Add/Remove Programs.

  3. In Add/Remove Programs, click Add/Remove Windows Components.

  4. On the Windows Components page, select the Cluster Service check box, and then click Next.

  5. The Internet Information Services check box is automatically selected with some Internet Information Services components.

  6. If Terminal Services is installed, click Next on the Terminal Services Setup page to run Terminal Services in remote administration mode.

  7. When prompted, insert your Windows 2000 Advanced Server or Windows 2000 Datacenter Server CD, and then click OK.

  8. After the clustering components install, click Next on the Welcome to the Cluster Service Configuration Wizard page.

  9. On the Hardware Configuration page, click I Understand to acknowledge that only certified configurations listed in the Cluster category of the HCL are supported by MSCS; then click Next.

To define the cluster

  1. On the Create or Join a Cluster page, click The First Server in the Cluster, and then click Next.

    If the Cluster Service Configuration Wizard does not detect disks that meet the criteria for clustering, you cannot continue.

  2. On the Cluster Name page, type the name for the new cluster (up to 15 characters), and then click Next. Use a name that is unique on the network and within the Windows domain.

To specify the setup program administrator account

  • On the Select an Account page, type the domain user account, password, and domain name in the appropriate text boxes, and then click Next.

    Use the domain user account you previously added to the Administrators local group on this node.

To define the cluster storage

  1. On the Add or Remove Managed Disks page, specify the disks that you want to belong to this cluster, and then click Next.

    By default, all disks that meet the criteria for clustering are selected.

  2. Remove any disks that you do not want this cluster to use.

    Do not remove any disks that you plan to use for any SQL Server instance on this cluster.

  3. On the Cluster File Storage page, click the drive belonging to the cluster that you want to use as the quorum drive.

    Select a mirrored drive with a minimum size of 500 MB. If the quorum drive fails, the cluster fails. Do not use this drive for SQL Server.

To define the networks

In this procedure, you tell Cluster Services which NICs are to be used for internal cluster communication such as the heartbeat, and which NICs are to be used for client access. The Network Connections page is displayed once for each NIC.

  1. On the Network Connections page, select the Enable This Network for Cluster Use check box if this NIC is used in the cluster. Select the Internal cluster communications only (private network) check box for each heartbeat NIC, or select the Client access only (public network) check box for each corporate NIC. Click Next.

  2. On the Cluster IP Address page, type the IP address in the IP Address text box, and confirm that the subnet mask is correct for your configuration.

    If your network employs subnetting, change the subnet mask only on the advice of a network specialist.

  3. Click the corporate network, and then click Next.

To finish the installation

  1. Click Finish to apply the settings for the cluster, and then start MSCS on this first node.

  2. When the message informs you that MSCS has started, click OK.

  3. Click Finish to close the Cluster Configuration Wizard.

  4. Close Add/Remove Program and Control Panel.

Installing and Configuring MSCS on Additional Nodes in the Cluster

Installing MSCS on additional nodes in a cluster requires less setup than installing MSCS on the original node because additional nodes inherit many settings from the first node.

To install MSCS on an additional node in the cluster

  1. On each additional server on which you want to install MSCS, click Start, point to Settings, and then click Control Panel.

  2. In Control Panel, double-click Add/Remove Programs.

  3. In Add/Remove Programs, click Add/Remove Windows Components.

  4. On the Windows Components page, select the Cluster Server check box, and then click Next.

    The Internet Information Services check box is automatically selected with some Internet Information Services components.

  5. If Terminal Services is installed, click Next on the Terminal Services Setup page to run Terminal Services in remote administration mode.

  6. When prompted, insert the Windows 2000 Advanced Server or Windows 2000 Datacenter Server CD, and then click OK.

  7. After the clustering components install, click Next on the Welcome to the Cluster Service Configuration Wizard page.

  8. On the Hardware Configuration page, click I Understand to acknowledge that only certified configurations listed in the Cluster category of the HCL are supported by MSCS; then click Next.

To join an existing cluster

  1. On the Create or Join a Cluster page, click The Second or Next Node in the Cluster, and then click Next.

    If the Cluster Service Configuration Wizard does not detect disks that meet the criteria for clustering, you cannot continue. Disks must be formatted for NTFS and must not be configured as dynamic disks.

  2. On the Cluster Name page, type the name of the existing cluster that you want this node to join. Leave the Connect to cluster as checkbox cleared, and then click Next.

    The Cluster Service Configuration Wizard verifies that a heartbeat is detected from the first node and then configures each NIC on the node as either a corporate NIC or a heartbeat NIC.

To specify the setup program administrator account

  • On the Select an Account page, type the domain user account, password, and domain name in the appropriate text boxes, and then click Next.

    Use the domain user account you created earlier that is an administrator in each node.

To finish the installation

  1. Click Finish to apply the settings for the cluster, and then start MSCS on this additional node.

  2. When the message box informs you that MSCS has started, click OK.

  3. Click Finish to close the Cluster Configuration Wizard.

  4. Close Add/Remove Program.

  5. Close the Control Panel.

Upgrading the MS DTC

You must upgrade MS DTC to work in a cluster before installing SQL Server in the cluster. Distributed queries, two-phase commits, and certain types of replication use DTC. Even if you do not currently use these features, upgrade DTC because you may need to use them in the future. Upgrading now prevents the need to fix errors later.

To upgrade DTC

  1. At one node in the cluster, open a command prompt.

  2. At the command prompt, type comclust, and then click Enter.

  3. Close the command prompt.

Note: Repeat this procedure on all other nodes participating in the SQL Server virtual server.

Installing and Configuring SQL Server on the Cluster

When you install SQL Server on a cluster, the setup program creates a virtual server. A virtual server consists of a virtual name, an IP address, and a port. Clients connect to these virtual resources, and MSCS points to the actual resources on the node in the cluster that is currently active. The SQL Server setup program installs SQL Server program files on each node and SQL Server system databases on a cluster resource disk. To successfully install a virtual server, you must properly configure and start MSCS.

Note: If you are reinstalling SQL Server or recovering from a failed SQL Server installation, remove any entries in the DNS server that relate to the failed MSCS installation before you reinstall. Stray or duplicate IP addresses or names can prevent a clean installation.

Creating a Single Cluster Group

Before you install SQL Server, place the disk resources for the SQL Server instance into a single cluster group. The group should contain a mirrored volume for the data files, a volume for the log files, and a volume for the backup and temporary files. The setup program adds additional resources to this cluster group when the virtual server installs.

Note: Create a separate cluster group for each SQL Server instance you plan to install.

To create a single cluster group

  1. Click Start, point to Administrative Tools, and then click Cluster Administrator.

  2. In Cluster Administrator, expand Groups and locate the disk groups containing the disks that you plan to use for this SQL Server instance.

    Initially, each disk (or set of mirrored disks) belonging to the cluster is in a numbered disk group, such as Disk Group 1, Disk Group 2, and so on. The quorum drive is in the Cluster Group.

  3. Right-click one of the disk groups, and then click Rename.

  4. Type a descriptive name for this resource group (such as VirtualServer1Group).

  5. Click the Resource node to display the additional drives owned by the cluster.

  6. Right-click a drive for SQL Server, point to Change Group, and then click the newly renamed cluster group.

  7. Close the Cluster Administrator.

Note: Repeat this procedure for each additional drive that will be used by this SQL Server instance.

Installing SQL Server

After creating the cluster group, you can begin installing SQL Server. Use the server name and IP address allocated by your IT department when you create the virtual server. Follow these procedures for each instance you install. Before you begin, you may want to create a folder on each clustered drive to easily identify all files belonging to SQL Server.

Note: You must install SQL Server from the server that owns the shared disks. To change the server that owns the shared disks, use Cluster Administrator.

To start the installation and define the virtual server name

  1. Insert the SQL Server 2000 Enterprise Edition CD into the CD-ROM drive.

  2. On the SQL Server 2000 Enterprise Edition page, clickSQL Server Components, and then click Install Database Server.

  3. On the Welcome page, click Next.

  4. On the Computer Name page, type a virtual name, click Virtual Server, and then click Next.

  5. On the Product Key page, enter the product key for SQL Server 2000 Enterprise Edition, and then click Next.

  6. On the Software License Agreement page, click Yes to accept the terms of the License Agreement.

  7. On the Virtual Server Information page, enter the IP address allocated for the virtual server, click the corporate network, click Add, and then click Next.

To define disk resources

  1. On the Select the Cluster Disk Where the Data Files Will Be Placed page, select the cluster group (or a drive within that cluster group) for this SQL Server.

    This step defines the cluster group that will contain the resources for the virtual SQL Server. The actual drive locations for executables and system databases are selected later. Do not select the cluster group containing the quorum drive.

  2. On the Cluster Definition page, specify the nodes supporting the virtual server, and then click Next.

    By default, all nodes in the cluster are selected. Remove any nodes that you do not want to support SQL Server.

  3. On the Remote Information page, type the domain user administrator account, password, and domain name in the appropriate text boxes.

    The setup program uses this user account to install the program files on each node. Use the administrator account you configured to administer the failover cluster. For more information, see "Configuring Accounts and Names and Obtaining IP Addresses" earlier in this chapter.

  4. On the Instance Name page, click Next to install a default instance. To install a named instance, clear the Default Instance check box, type the name for the named instance, and then click Next.

  5. On the Setup Type page, click the appropriate setup type for your installation.

  6. Specify the location for the program files on each node.

    The default location is the system volume. Change this default if appropriate, and ensure that sufficient space exists for these files. The setup program installs the program files into this location on each node.

  7. Specify the location for the data files on the cluster.

    The data files must be on one of the cluster disks and should not be on the quorum disk. The setup program installs the system databases on this drive.

    If you have created a folder for this SQL Server instance on the data drive, browse to this folder, and then click Next.

To complete the installation

  1. On the Services Accounts page, type the domain user account, password, and domain name in the appropriate text boxes, and then click Next.

    The SQL Server and SQL Server Agent services use this account. Use the domain user account you configured for the SQL Server and SQL Server Agent services. For more information, see "Configuring Accounts and Names and Obtaining IP Addresses" earlier in this chapter.

  2. On the Authentication Mode page, click the authentication mode for SQL Server, and then click Next.

    Windows Authentication Mode is more secure than Mixed Mode. If you select Mixed Mode, you must provide a password for the sa account or agree to use a blank password. Do not use a blank password because it is highly insecure, even in a development environment, and can lead to poor coding practices.

  3. On the Licensing Mode page, specify the appropriate licensing mode for your environment, and then click Next.

  4. Click Finish to install SQL Server. When installation is complete, restart all nodes in the cluster.

Adding Cluster Disks as SQL Server Dependencies

To ensure that the failover process operates properly, manually add each disk on which SQL Server depends to the list of SQL Server dependencies. If a disk is not included in the SQL Server dependencies, SQL Server cannot use it after a failover. Add the mirrored volumes used for the transaction log files, backup files, and additional data files. This procedure requires all disk resources to be members of a single cluster group.

To add additional cluster disks as dependencies

  1. Click Start, point to Programs, and then click Cluster Administrator.

  2. In Cluster Administrator, click the cluster group containing the SQL Server virtual server.

  3. Right-click SQL Server, and then click Take Offline.

  4. SQL Server must be offline to change SQL Server dependencies.

  5. Right-click SQL Server, and then click Properties.

  6. On the Dependencies tab in SQL Server Properties, review the default resource dependencies.

    The Network Name resource and at least one disk resource appear.

  7. Click Modify.

  8. In the Available Resources list, double-click each additional disk resource on which SQL Server depends, and then click OK.

  9. Click OK to close SQL Server Properties.

  10. Right-click the group containing the SQL Server resources, and then click Bring Online.

Applying the Most Recent SQL Server Service Pack

Before using SQL Server, install the most recent SQL Server service pack.

To install the most recent SQL Server service pack

  1. Log on to one of the nodes in the cluster by using a domain account that is a member of the Administrators local group on that node.

  2. Insert the CD containing the service pack into the CD-ROM drive.

    If it does not start automatically, navigate to the \X86\Setup directory, and then double-click setupsql.exe.

  3. On the Welcome page, click Next.

  4. On the SQL Server Name page, type the name of the SQL Server virtual server, and then click Next.

  5. On the Authentication Mode page, click Next to connect to SQL Server using Windows authentication.

  6. On the Domain Administrator page, type a domain administrator name, password, and domain name, and then click Next.

    The setup program must use a domain administrator account to install the service pack on all nodes of the virtual server.

  7. After the service pack installs, click OK.

  8. Click Yes, and then click Finish to restart the node.

  9. Manually restart each additional node on which the service pack has been installed.

  10. Back up the master and msdb databases.

    They have been modified by the service pack installation.

Note: This procedure is for SQL Service 2000 Service Pack 2. Check the ReadMe file on the CD for later service pack procedures.

Performing Additional Configuration Tasks

After you have installed the virtual server, review these additional configuration options and apply the settings that are appropriate for your environment. These options include the following:

  • Set memory allocation between virtual servers

  • Set new database properties

  • Set the TCP/IP port that SQL Server uses

  • Set advanced failover options

  • Set SQL Server properties in the cluster

Use the following guidelines when performing these tasks.

Allocating Memory

If you are running multiple instances of SQL Server on separate nodes in the cluster, you must determine the total amount of physical memory required for each server and determine the appropriate allocation of memory for each SQL Server instance. Performance of SQL Server is directly related to the amount of physical memory allocated to it by the operating system. SQL Server provides two modes for memory management: dynamic and fixed. Dynamic memory allocation lets SQL acquire and release memory from the operating system as needed. Dynamic memory allows you to set a maximum amount of physical memory that the operating system can allocate to SQL Server. This allows the remaining memory to be reserved for other purposes, such as the operating system and other SQL Server instances. Fixed memory allows you to set a specific amount of physical memory that SQL Server can use. This amount neither grows nor shrinks.

Configure each server with the same amount of physical memory, and then allocate memory to each SQL Server instance to ensure that sufficient memory is available to support the failover of a SQL Server instance from another node. If a server does not use a lot of memory compared to the available memory on the server, specify dynamic memory and set the maximum amount of memory that the operating system can allocate. By doing so, you leave sufficient memory for the failover to occur quickly and successfully. If an instance uses all of the available physical memory, failover will occur more slowly and can fail if memory is not released quickly enough.

To determine the total amount of memory to install on each server, test your applications by fixing the maximum amount of memory available to SQL Server, and then evaluate the performance. Install enough memory on each server to enable all instances to operate on that server and meet the minimum service level for each instance. Using dynamic memory allows the SQL Server instance on each node to use most of the memory on that node. If you use fixed memory, the additional memory will only be used during a failover.

In a system under continuous heavy load, install sufficient memory to ensure that each SQL Server instance can meet its required minimum allocation. Use a fixed memory allocation to ensure that the SQL Server instance does not expand into memory reserved for the failover of other instances. To determine the appropriate setting, initiate a failover and test the performance level of each virtual server. If the operating system is unstable after a failover, decrease the memory allocated to each instance to increase the memory available to the operating system.

To specify a fixed amount of memory for each instance, use SQL Server Enterprise Manager or the sp_configure system stored procedure. When determining the amount of memory to allocate to SQL Server, remember to allow sufficient memory for the operating system. With large databases, allow 2 gigabytes (GB) of memory for the operating system.

If the amount of memory installed on each node is greater than 4 GB, add the /PAE switch to the Boot.ini file, and enable Address Windowing Extensions (AWE) in SQL Server. For more information, see article 268363, "Intel Physical Addressing Extensions (PAE) in Windows 2000," in the Microsoft Knowledge Base at http://support.microsoft.com/default.aspx?scid=kb;en-us;268363&sd=tech and article 274750, "Configure Memory for More Than 2 GB in SQL Server," in the Microsoft Knowledge Base at http://support.microsoft.com/default.aspx?scid=kb;en-us;274750.

Setting New Database Properties

You can specify the database location for all new databases on the cluster disks you created for the data and log files.

To set new database properties

  1. On one of the nodes, click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. Expand Microsoft SQL Servers, and then expand SQL Server Group.

  3. Right-click the SQL Server instance, and then click Properties.

  4. On the Database Settings tab, in the Default Data Directory text box, type or browse to the drive and folder you want for the data files.

  5. In the Default Log Directory text box, type or browse to the drive and folder you want for the log files, and then click OK.

  6. Close SQL Server Enterprise Manager.

Setting the TCP/IP Port SQL Server Uses

The default instance of SQL Server usually listens on port 1433. If you are configuring a multiple instance cluster, you must use a different port for each additional instance. Although SQL Server can set this port dynamically at startup, you might want to set each named instance to listen on a specific, unused port. Check with a network administrator for a recommendation. Specify a static port if you have to open ports on a firewall and do not want the SQL Server ports to change.

To specify the TCP/IP port for an instance

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Server Network Utility.

  2. In SQL Server Network Utility, click the instance of SQL Server for which you want to set the port.

  3. In the Enabled Protocols list, click TCP/IP, and then click Properties.

  4. In the Default Port text box, type the appropriate port, and then click OK.

  5. Click OK to close the SQL Server Network Utility dialog box.

  6. Repeat these steps for each node participating in the SQL Server virtual server.

Setting Advanced Failover Options

Advanced failover options determine the failover behavior of SQL Server in the MSCS cluster.

To specify failover behavior

  1. Click Start, point to Programs, and then click Cluster Administrator.

  2. In Cluster Administrator, click the cluster group containing SQL Server.

  3. Right-click SQL Server, and then click Properties.

  4. On the Advanced tab, use the following information to configure the advanced cluster failover properties. The defaults should not be changed without careful consideration and testing:

    • Do Not Restart/Restart — Click Restart to fail over to a secondary node if a failure is detected. This is the default.

    • Affect the Group — Select this check box to have the failover of SQL Server cause a failover of all resources in the cluster group.

    • Threshold — Type the number of attempts MSCS should make to restart SQL Server before failing over to a secondary node.

    • Period — Type the seconds between retry attempts. This value is ignored if the threshold is set to 0.

    • Looks Alive Poll Interval — Click Use Value from Resource Type to use the SQL Server default of 5,000 milliseconds (5 seconds).

    • Is Alive Poll Interval — Click Use Value from Resource Type to use the SQL Server default of 60,000 milliseconds (60 seconds).

    • Pending Timeout — Specify the time SQL Server has in either the Offline Pending or the Online Pending state before MSCS places the resource in Offline or Failed status. The default is 180 seconds.

  5. When you are finished, click OK to close SQL Server Properties in the Cluster Administrator.

Configuring Service Properties in a Cluster

In a cluster environment, you must use the correct management tool to configure SQL Server services, as follows:

  • Use Cluster Administrator to start and stop the services.

  • Use the Properties dialog box for the SQL Server instance in Enterprise Manager to set the service accounts and passwords for the services.

Failing to use the appropriate tools to configure SQL Server properties can cause SQL Server startup problems. In addition, in a cluster, do not set SQL Server Agent to restart automatically. If SQL Server Agent is set to restart automatically, it can conflict with the cluster services during a failover.

Failing Over to a Secondary Node

Failover is automatic when using MSCS and SQL Server. You can control how long MSCS takes to detect a failure, and you can take steps in your applications to reduce the time to bring up the standby service. Before making any changes to MSCS or to a client application, test the failover cluster to become familiar with how it fails over under different conditions.

Testing Failover

Use Cluster Administrator to initiate a planned failover. This simulates taking a node offline for planned maintenance.

To initiate a planned failover

  1. Click Start, point to Programs, and then click Cluster Administrator.

  2. Click the cluster group containing the SQL Server virtual server.

    Notice the cluster that owns each resource in this cluster group.

  3. Right-click the cluster group containing the SQL Server virtual server, and then click Move Group.

Watch each resource in the group switch from online to offline to online pending and then to online in the new group. The entire process takes 1 to 2 minutes for a typical environment. Also notice that the resource owner in this cluster group changes to the secondary cluster.

Use one of the following options to simulate an unplanned failover.

To simulate an unplanned failover

  1. Do one of the following:

    • Unplug the corporate network cable from the node that owns SQL Server.

    • Reboot the owning node.

    • Unplug the fibre connected to the SAN (if a SAN is used).

    • Turn off the power on the owning node.

  2. Use Cluster Administrator on the secondary node to watch each resource in the group switch from online to offline to online pending and then to online.

    The entire process takes less than 1 minute. Notice that the resource owner in this cluster group changes to the secondary cluster.

Minimizing Failover Time

If the time it takes SQL Server to fail over from one node to another needs to be decreased, use the following information to determine your options to reduce time to availability.

Understanding Time to Availability

The time it takes from the occurrence of a fault to SQL Server being available on the standby node consists of the following:

  • The time to detect and confirm the fault, which you can control

  • The time to take the resources offline, which is controlled by MSCS and which you cannot control

  • The time to bring the resources online, which MSCS controls and which you can control by ensuring that sufficient SQL Server memory and processor resources are allocated

  • The time for SQL Server to initialize, which you can control by limiting long transactions

SQL Server is usually available within 1 minute or less.

Controlling the Time Required to Detect and Confirm the Fault

The LooksAlive and IsAlive poll intervals for SQL Server determine the time that it takes MSCS to detect a failure. Smaller intervals create more overhead and increase the possibility of false failure detections, and therefore result in unnecessary failovers. The default for the SQL Server IsAlive interval is 60,000 milliseconds (60 seconds), and LooksAlive is 5,000 milliseconds (5 seconds). Change these values on the test platform first, and test them thoroughly under a realistic load. If you set the interval lower, be prepared to measure the impact on the application.

Controlling the Time Required to Bring the Resources Online

If the failover node has insufficient available memory at the time of failover, it will take longer to bring SQL Server online after a failover. Although this condition should not occur in a single-instance cluster, this can be a problem in a multiple-instance cluster. In normal operation, each node runs one instance of SQL Server, and each instance uses memory as needed. If a node does not have sufficient memory to run multiple instances simultaneously during a failover, use a lower maximum memory setting to reserve memory for failover.

If at least 30 MB of unallocated physical memory is available, the instance starts in dynamic memory allocation mode. For monitoring purposes, a low-memory condition creates an entry in the SQL Server event log warning you that the operating system did not fulfill the SQL Server memory request. No entry is made in the Application Log. This does not mean that you should attempt to operate SQL Server with only 30 MB of available physical memory.

Controlling the Time Required to Initialize

At startup, SQL Server must verify the condition of each database, rolling forward completed transactions and rolling back any uncommitted transactions that it finds. If client applications use very long transactions, the incomplete transactions can take a long time to roll back. Use a series of short transactions whenever possible. Determine each applications worst-case transaction size, and test the recovery time when this transaction is rolled back in a failover.

Reconnecting Clients

When the SQL Server service activates on the failover node, the application must reconnect to continue operating. The manner in which clients reconnect depends on whether an application maintains state or not and on the ability of the application to reissue the steps in a transaction. Clients reconnect in the following ways:

  • In a Web application, application state is usually not maintained. When a user submits a request to the Web server, the application usually makes a new connection. In this environment, no additional retry logic is required. However, the Web application needs to gracefully handle the error response when the application cannot connect to SQL Server, however.

  • In a non-Web application, a connection is often held for a whole session. You must implement logic in the application that detects a broken connection and that attempts to reconnect at appropriate intervals (for example, 30 seconds). The application should be prepared to restart the transaction that was in progress when the connection broke. In some cases, you may have to rely on the application user to retry the submission.

Failing Back to the Original Node

In a single-instance cluster, little reason exists to fail back to the original node. If you have symmetrical servers that are dedicated to the same application, performance is identical on each. Failing back only causes more downtime.

In a multiple-instance configuration, two instances of SQL Server run on the same node after a failover, so performance decreases for both. Choose a failback time to minimize the impact on clients.

You can set MSCS to automatically fail back to the original node; however, doing so incurs another service outage when the service is moved to the other node. In addition, if the failed node is not ready for the service to fail back, additional downtime occurs. Therefore, enabling automatic failback is not necessarily effective in maintaining high availability.

Automatic failback can be set to occur at a time when an additional outage is acceptable.

To configure automatic failback in a specific time slot

  1. Click Start, point to Programs, and then click Cluster Administrator.

  2. In Cluster Administrator, right-click the group containing SQL Server.

  3. Choose Properties.

  4. On the Failback tab, set the time of day during which you want to allow failback.

Manual failback enables you to control when to stop the service and move it back to the original node when doing so is least disruptive to your clients.

To initiate manual failback

  1. Click Start, point to Programs, and then click Cluster Administrator.

  2. In Cluster Administrator, right-click the group containing SQL Server.

  3. Choose Move Group.

Restoring a Node After a Catastrophic Failure

If one of the cluster nodes suffers a catastrophic failure, you might have to rebuild the server. Use the steps in the following procedures to recover from a catastrophic failure.

To rebuild the server

  1. On the surviving node, remove the failed node from the virtual server. If you have more than one virtual server on a node, doing so updates all instances.

  2. On the surviving node, evict the failed cluster node.

  3. Rebuild the failed node.

  4. Install SQL Server.

To remove a node from a virtual server

  1. Insert the SQL Server 2000 Enterprise Edition CD into the CD-ROM drive.

  2. On the SQL Server 2000 Enterprise Edition page, click SQL Server Components, and then click Install Database Server.

  3. On the Welcome page for the Microsoft SQL Server Installation Wizard, click Next.

  4. On the Computer Name page, type the virtual server name, click Virtual Server, and then click Next.

  5. Leaving Advanced Options selected, click Next.

  6. Leaving Maintain a Virtual Server for Failover Clustering selected, click Next.

    Note: If the server is unavailable, you will receive the following error message: "One or more nodes of Virtual Server are unavailable. Setup cannot modify the virtual server's IP address resource."

  7. On this error page, click OK.

  8. On the Virtual Server Information page, click Next.

  9. On the Cluster Management page, click the failed node, and then click Remove. Click Next.

  10. On the Remote Information page, type the login name and password of an account that is an administrator for all nodes in the cluster. Click Next.

  11. In the message box, click Yes.

  12. Click Finish.

To evict a node from a cluster

  1. Click Start, point to Programs, and then click Cluster Administrator.

  2. In Cluster Administrator, right-click the failed node, and then click Evict Node. Click OK.

  3. Close Cluster Administrator.

To rebuild a node

  1. Reinstall Windows, join the server to the domain, and apply the most recent Windows service pack on the new hardware.

  2. Configure the domain user account to administer the cluster. For more information, see "Configuring Accounts and Names and Obtaining IP Addresses" earlier in this chapter.

  3. Configure the corporate and private NICs.

  4. Install and configure MSCS as an additional node. For more information, see "Installing and Configuring MSCS on Additional Nodes in the Cluster" earlier in this chapter.

To add the rebuilt node to the virtual server

  1. Insert the SQL Server 2000 Enterprise Edition CD into the CD-ROM drive of any node in the cluster.

    You can add a rebuilt node to the virtual server from any node in cluster.

  2. On the SQL Server 2000 Enterprise Edition page, click SQL Server Components, and then click Install Database Server.

  3. On the Welcome page for the Microsoft SQL Server Installation Wizard, click Next.

  4. On the Computer Name page, type the virtual server name, click Virtual Server, and then click Next.

  5. On the Installation Selection page, click Advanced Options, and then click Next.

  6. Leaving Maintain a Virtual Server for Failover Clustering selected, click Next.

  7. On the Virtual Server Information page, click Next.

  8. On the Cluster Management page, click the new node, click Add, and then click Next.

  9. In the Username text box, specify a domain user account that is an administrator on each node participating in the virtual server configuration. Type the password in the Password text box, type the domain name in the Domain text box, and then click Next.

  10. On the Services Accounts page, type the domain user account, password, and domain name in the appropriate text boxes, and then click Next .

  11. Click Finish when setup completes. Repeat this process for each instance.

Note: After you install SQL Server 2000 and join it to the virtual SQL Server, apply the appropriate SQL Server service pack to the rebuilt node. All nodes run at the same service pack level.

References

For more information about topics covered in this chapter, refer to the following:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft