TechNet Home > Product & Technologies > SQL Server TechCenter Home > SQL Server 2000 > Deploy > SQL Server 2000 High Availability Series

SQL Server 2000 High Availability Series

Implementing Network Load Balancing

Updated: November 12, 2002

This chapter teaches you how to increase the availability of a data center by creating a Microsoft Network Load Balancing (NLB) cluster. After reading this chapter, you will be able to create an NLB cluster to work with secondary servers maintained by using log shipping, transactional replication, or database backups.

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

*
On This Page
Understanding Network Load BalancingUnderstanding Network Load Balancing
Deploying NLBDeploying NLB
Changing the Primary Server with NLBChanging the Primary Server with NLB

Understanding Network Load Balancing

Network Load Balancing is a component of Windows clustering technology that comes with Microsoft Windows 2000 Advanced Server and Microsoft Windows 2000 Datacenter Server. An NLB cluster provides a way to provide a single server name and an IP address through which clients connect to one or more servers. You usually use NLB to automatically distribute (load balance) client requests across a number of servers providing the same service to the client, such as Internet Information Services (IIS). With a production database that allows updates however, only one server can be active.

In a high-availability environment, you create an NLB cluster to easily route client requests from the original primary server to a promoted secondary server without having to update each client directly. Although NLB requires you to manually reconfigure the NLB cluster to point clients to a secondary server, manually modifying the NLBis much faster than manually changing the connection information for each client.

You can use NLB with a standby server that participates in log shipping, with a subscriber that participates in transactional replication, or with a secondary server that contains a database restored from backup. NLB setup is simple and switching to an alternative server is quick; however, NLB requires that each server that participates in an NLB cluster be on the same subnet.

To implement an NLB solution, you need to have certain things in place:

•

You need the network administrator.

•

You need the Windows 2000 Advanced Server or Windows 2000 Datacenter Server. Microsoft SQL Server is not required for NLB, although you must install SQL Server on each server in the NLB cluster as part of the overall high availability solution.

•

You need systems and system components that are certified to work together and with the chosen Microsoft Windows 2000 operating system. 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, search the "Hardware Compatibility List" on the Microsoft Web site at http://support.microsoft.com/kb/131900.

For more information about NLB, see "Microsoft Clustering Solutions" on the Microsoft TechNet Web site at http://www.microsoft.com/technet/archive/community/columns/tips/w2kclust.mspx.

Also see "Network Load Balancing Technical Overview" on the Microsoft TechNet Web site at http://www.microsoft.com/technet/prodtechnol/windows2000serv/deploy/confeat/nlbovw.mspx.

Deploying NLB

You will successfully implement an NLB cluster if you avoid shortcuts and parallel installations. If the setup program fails or you cancel the setup program, remove any Domain Name System (DNS) entries that relate to the failed setup before you continue. Also, verify that the server name and IP address you plan to use for NLB are unused immediately before installing NLB. You can check for conflicts by pinging the service name and proposed IP address.

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

•

Windows 2000 Advanced Server or Windows 2000 Datacenter Server is installed.

•

The most recent operating system service pack is installed.

•

NLB has not been installed.

•

The server is a member of the same Microsoft Active Directory service forest as each other server.

•

At least one network interface card (NIC) is installed.

•

The server is connected to the corporate network and the server may be connected another server on a private network to expedite log shipping or replication traffic.

•

The server is on the same subnet as each other server.

•

SQL Server 2000 is installed, although NLB does not require its installation.

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

Understanding the Logical Design

Figure 4.1 shows the logical design of an NLB cluster. The design shows a secondary server in the local site and a secondary server in a remote site. You need not implement both secondary servers. The remote site provides redundancy in case the local site becomes unusable. The virtual local area network (VLAN) connecting the local and remote sites allows NLB NICS to be on the same subnet, which is a requirement for an NLB operation.

Client traffic and NLB management traffic use the same network. You can use a second network within the local site for log shipping or replication traffic between servers to reduce the load on the public network within the local site. When you deploy a remote site, you must ensure that the connection to the remote site supports sufficient bandwidth for all network traffic between the sites.

Clients use the NLB cluster address to connect to SQL Server. In the configuration shown in Figure 4.1, the NLB cluster address is the x.y.z.5 IP address. NLB is configured so that only the current primary server responds to the cluster address. When a secondary server is promoted to become the primary server, use NLB management commands to change the server that responds to client requests directed to the NLB cluster address.

Figure 4.1: Logical design diagram of an NLB cluster

Figure 4.1: Logical design diagram of an NLB cluster
See full-sized image.

On each server (node) that will be part of the NLB cluster, an instance of Windows 2000 Advanced Server or Windows 2000 Datacenter Server is installed. The steps required to implement this logical design are below, followed by the steps in detail.

To implement an NLB cluster

1.

Obtain an allocation of a public IP address and server name for the NLB cluster, and determine the port to be used by SQL Server. The IP address must be on the same subnet as each node in the cluster.

2.

Configure the NLB NIC, and then install and configure NLB on each node participating in the NLB cluster.

3.

Enable the primary server.

Obtaining the Public IP Address, Server Name, and Port

Before configuring an NLB cluster, obtain a server name, IP address, and port number for the NLB cluster.

•

Server name — Have the IT department approve the NLB cluster name.

•

IP address — Have the IT department allocate a static IP address for the NLB cluster and its subnet mask. If you have not yet allocated a static address for the NLB NIC, obtain an allocation for its IP address and subnet mask.

•

Port number — With the network specialist, determine the port to be used by the SQL Server instances that the NLB cluster will support. A default instance of SQL Server uses port 1433. Each named instance of SQL Server requires a unique number. Each standby instance of SQL Server must use the same port number as its corresponding primary instance in the cluster.

You will use this information when configuring the NLB cluster.

Configuring the NLB NIC and Installing and Configuring NLB

Before you install NLB on each node, you must configure the IP address for the NIC that you plan to use for NLB communication. If you have multiple NICs in the server, choose the NIC connected to the corporate network. You will install NLB on only one NIC in the server. If the NLB cluster is geographically dispersed and you are using a virtual private network (VPN) to connect the sites, you must also configure the default gateway and preferred DNS server for the NIC.

After you configure the NLB NIC, install and configure NLB on the NLB NIC. Use the same cluster parameters for each node in the NLB cluster. Use a unique host IP address for each node, but be sure each node is on the same subnet. Use port rules to determine how traffic will be distributed across the nodes in the cluster. For SQL Server databases that are not read-only, you must configure the port rules so that only one server (the current primary server) will handle all network traffic for SQL Server.

Before you begin, log on by using an account that is a member of the Administrators local group.

To configure the NLB NIC

1.

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

2.

In Network and Dial-up Connections, right-click the NIC you plan to use for NLB, click Rename, and then type NLB NIC.

3.

In Network and Dial-up Connections, right-click NLB NIC, and then click Properties.

4.

In NLB NIC Properties, click Internet Protocol (TCP/IP), and then click Properties.

5.

In Internet Protocol (TCP/IP) Properties, type the IP address allocated to the NLB NIC, and then change the subnet mask if your network uses subnetting.

6.

Type the IP address of the default gateway. (Your network administrator defines this address.)

7.

Click Use the Following DNS Server Addresses, and then type the DNS server address. (Your network administrator defines this address.)

8.

Click OK to save the new TCP/IP properties.

9.

Click OK to save the new connection properties for the NLB NIC.

To install NLB on a node

1.

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

2.

In Network and Dial-up Connections, right-click NLB NIC, and then click Properties.

3.

In NLB NIC Connection Properties, click Network Load Balancing.

4.

In Select Network Component Type, click Service, and then click Add.

5.

In Select Network Service, click Network Load Balancing, and then click OK.

6.

After NLB is installed, click Close.

To configure the NLB cluster

1.

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

2.

In Network and Dial-up Connections, right-click the connection for the NLB NIC, and then click Properties.

If Properties is unavailable, select Network Load Balancing, and then click Properties.

3.

In Network Load Balancing Properties, use the following information to complete the information requested on the Cluster Parameters tab:

1.

Primary IP address — This is the cluster address. The cluster address is the same for all the NICs participating in the NLB cluster. Type the allocated address obtained from the IT department for the NLB cluster.

2.

Subnet mask — Change the automatically calculated subnet mask if your network uses subnetting.

3.

Full Internet name — Use the server name allocated for the NLB cluster plus the full domain name.

4.

Multicast support — Select this check box only if you are using a single NIC for all traffic on this server. If you use a second NIC to support log shipping or transactional replication traffic, do not select multicast support.

5.

Remote password — Type a password to control NLB on this node from another node.

6.

Confirm password — Repeat the remote password.

7.

Remote control — Select this check box to allow you to control NLB on this node from another node.

4.

Use the following information to complete the information requested on the Host Parameters tab:

1.

Priority (unique host ID) — Specify a unique number between 1 and 32.

2.

The node with the lowest number handles all client traffic not assigned by port rules.

3.

Initial cluster state — Do not select the Active check box for a normal SQL Server scenario. Select the Active check box if all SQL Server nodes are read-only.

4.

Dedicated IP address — Type the allocated address obtained from the IT department for the node.

5.

Subnet mask — Type the appropriate subnet mask for your network.

5.

Use the following information to complete the information requested on the Port Rules tab:

1.

Port range — Specify the port range for the SQL Server instances this NLB cluster will support. If no other services on the server are using NLB, specify a range of 0 through 65,535. If other services on the server are using NLB, configure each service with its own port range and settings.

2.

Protocols — Click Both to enable both User Datagram Protocol (UDP) and Transmission Control Protocol (TCP).

3.

Filtering mode — Click Single Host if you use a dedicated NIC for the NLB traffic or click Multiple Hosts if you use the NIC for all traffic to the server.

4.

Affinity — Click None for filtering mode Single Host, or Single for Multiple Hosts.

6.

Click OK to close Network Load Balancing Properties.

7.

Click OK to close Connection Properties.

To add the cluster IP address to the NLB NIC

1.

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

2.

In Network and Dial-up Connections, right-click NLB NIC, and then click Properties.

3.

In Connection Properties, right-click Internet Protocol (TCP/IP), and then click Properties.

4.

In Internet Protocol (TCP/IP) Properties, click Advanced.

5.

On the IP Settings tab in Advanced TCP/IP Settings, in IP Addresses, click Add.

6.

In TCP/IP Addresses, type the NLB cluster IP address and subnet mask, and then click Add.

Note: The cluster IP address must come after the dedicated IP address in the list.

7.

Click OK to close Advanced TCP/IP Settings.

8.

Click OK to close Internet Protocol (TCP/IP) Properties.

9.

Click OK to close Connection Properties.

Note: Repeat these procedures on each node participating in the NLB cluster.

After you have configured all the nodes, verify with your network administrator that there is a DNS entry for the virtual cluster name. If you are using the cluster IP address instead of the cluster name when connecting to SQL Server, the network administrator should add the cluster IP address and name to the DNS reverse lookup table.

Enabling the Primary Server Node

After you have configured the NLB cluster on all nodes participating in the NLB cluster, enable the node servicing the primary server. Do not enable any nodes servicing secondary servers. When you enable a node, a convergence process occurs that determines the servers that will respond to client traffic directed to the NLB cluster name and IP address. This convergence process evaluates the nodes that are enabled and the NLB configuration settings for the cluster.

To enable the primary server node

1.

Open a command prompt.

2.

Type WLBS Start clustername:primary node name /PASSW <password>.

3.

Click Enter.

4.

Close the command prompt.

Changing the Primary Server with NLB

NLB supports automatic and manual failover modes to change the server that receives traffic directed to the NLB clusters virtual IP address. The automatic failover mode is designed to be used when NLB is automatically distributing client requests across a number of servers that are providing the same service to the client. When NLB is used as a part of a SQL Server high-availability solution, however, manual failover must be used.

Before changing the NLB cluster configuration to point to a secondary server, you must prepare the secondary server to receive client requests. The steps required to prepare the secondary server vary depending on the high-availability solution used with NLB.

•

If you are deploying NLB with log shipping, follow the role change steps in Chapter 2 of the Solution Guides before initiating a manual failover of NLB to a standby server.

•

If you are deploying NLB with transactional replication, follow the role change steps in Solution Guide Chapter 3 before initiating a manual failover of NLB to one of the transactional replication subscribers.

•

If you are deploying NLB without log shipping or transactional replication, restore and recover the production database to an alternative server before initiating a manual failover of NLB.

After the secondary server is ready to receive client requests, stop all traffic through NLB to the current primary server and then enable traffic to the secondary server.

To disable client traffic to the primary server

1.

Open a command prompt.

2.

Type WLBS DrainStop <clustername>:<primary node name> /PASSW <password> .

3.

Click Enter.

4.

After waiting a few moments for existing connections to finish draining, test to see whether the primary server is finished by typing WLBS query <clustername> /PASSW <password>.

5.

Click Enter.

6.

After the primary server has finished draining, close the command prompt.

Note: This procedure is required only if you are performing a planned failover. If the primary server has failed, skip this procedure.

To enable client traffic to the secondary server

1.

Open a command prompt.

2.

Type WLBS Start <clustername>:<primary node name> /PASSW <password> .

3.

Click Enter.

4.

Close the command prompt.

Note: To point clients to the original primary server, prepare the original primary server to receive client requests, and then use the two procedures above to disable client traffic to the current primary server and to point client traffic to the original primary server.


Top of pageTop of pagePrevious11 of 13Next
**
**