Microsoft SQL Server 2000 High Availability

This section describes the implementation considerations when you configure a failover cluster.

Prerequisites

Prior to installing SQL Server 2000, make sure there are no errors in Event Viewer that could prevent a successful cluster installation. Verify that only the services necessary for the operating system are running. Any other services should be stopped because they could interfere with the installation process. These services include Simple Network Management Protocol (SNMP), the World Wide Web Publishing service, and vendor-specific programs. The easiest way to start and stop multiple services is to create two batch files: one that contains multiple net stop commands and one that contains the corresponding net start commands.

Installation Order

This section provides the installation order for various versions of Windows and SQL Server 2000.

Windows 2000 Advanced Server and Windows 2000 Datacenter Server

Install Windows 2000 Advanced Server and Windows 2000 Datacenter Server in this order:

1. Install Windows 2000 Advanced Server (the vendor installs Windows 2000 Datacenter Server).

2. Install any necessary Windows 2000 service packs or hot fixes.

3. Install Microsoft Internet Explorer 5 Update (if necessary).

4. Create the necessary domain user accounts.

5. Complete any server cluster preinstallation tasks required, as described in Chapter 5.

6. Create the server cluster.

7. Create the clustered MS DTC.

8. Complete any server cluster postinstallation tasks required, as described in Chapter 5.

9. Stop unnecessary services, such as Internet Information Services (IIS) or Simple Mail Transfer Protocol (SMTP). This will vary on a case-by- case basis, and although it is not required, it is recommended.

10. Rename the cluster group with the disk to be added during the failover cluster installation.

11. Install SQL Server 2000.

12. Install the latest SQL Server 2000 service pack, as well as any hot fixes.

Windows Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition

This is the order for installing Windows Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition:

1. Install Windows Server 2003 Enterprise Edition (the vendor installs Windows Server 2003 Datacenter Edition).

2. Install any necessary Windows Server 2003 service packs or hot fixes.

3. Create the necessary domain user accounts.

4. Complete any server cluster preinstallation tasks required, as described in Chapter 5.

5. Create the server cluster.

6. Create the clustered MS DTC.

7. Complete any server cluster postinstallation tasks required, as described in Chapter 5.

8. Stop unnecessary services such as IIS. Although this is not required, it is recommended.

9. Rename the cluster group with the disk to be added during the failover cluster installation.

10. Install SQL Server 2000. SQL Server 2000 Release A is recommended.

11. Install SQL Server 2000 Service Pack 3 or later. SQL Server 2000 Service Pack 2 and earlier versions are not supported under Windows Server 2003.

12. Install any necessary SQL Server 2000 hot fixes.

    Warning

    If you attempt to set up a SQL Server virtual server on a server cluster where you select the default of having all nodes as part of the virtual server definition for SQL Server and the computer name of the primary domain controller is more than 14 or 15 characters , you might encounter an access violation. This commonly occurs when the cluster nodes are set up as domain controllers. For any updates to this, see Knowledge Base article 289828, PRB: SQL Server Setup in a Cluster Environment Encounters an Access Violation If You Have a Long Computer Name, at http://support.microsoft.com .

Installing a SQL Server Virtual Server

Installing a failover cluster is very similar to the process of installing a stand- alone SQL Server 2000 instance.

Postinstallation Tasks

This section highlights some best practices when implementing a SQL Server 2000 failover cluster.

Configuring Antivirus Software

If there is no reason to put antivirus software on the cluster nodes dedicated to SQL Server, very secure, and without file shares on it, do not install the software. If your corporate security policy dictates that antivirus software must be configured on all servers, you must set the filtering of the antivirus program to exclude the scanning of all SQL Server drives that contain data and log files. You do not want the scanner on another node in the event of a failover to detect the drive and prevent SQL Server from starting because the virus scanner is now scanning your database and log files. Also remember, as noted in Chapter 5, to exclude the \MSCS directory on the quorum if you are using a disk-based quorum.

Adding Drives for SQL Server Use

During SQL Server setup, you can choose only one drive letter out of your available cluster drives. To be able to use additional drives, you must add them as dependencies to the SQL Server resource.

1. Start Cluster Administrator.

2. Fail all disk resources that will be added to the SQL Server virtual server to the same node that currently owns the SQL Server resources.

3. Drag and drop the additional disk resources to the folder containing the SQL Server resources to move them. A message will be displayed, similar to the one shown in Figure 6-5.

   Figure 6-5: Cluster Administrator confirmation message.

4. In the Move Resources dialog box, shown in Figure 6-6, click Yes.

   Figure 6-6: Move Resources dialog box.

5. Take the SQL Server resource offline by right-clicking it and selecting Bring Offline. Or, you can use SQL Server Service Manager and stop SQL Server. Both methods are supported.

6. Right-click the SQL Server resource and then click Properties.

7. In the Properties dialog box, click the Dependencies tab, and then click Modify.

8. In the Modify Dependencies dialog box, shown in Figure 6-7, the available resources for the cluster appear in the Available Resources list. Select the drives to add, click the arrow to move the resource to the Dependencies list, and then click OK.

   Figure 6-7: Modify Dependencies dialog box.

9. To verify that the resource is now a dependency, in the Properties dialog box click the Dependencies tab, shown in Figure 6-8.

   Figure 6-8: Verify the new dependency in the SQL Server Properties dialog box.

10. Bring the SQL Server resource online when complete. Also restart SQL Server Agent and SQL Server Fulltext if necessary.

Verifying the Drive Configuration

To ensure all of your drives are added to SQL Server properly, you can do two things:

1. Execute the following Transact-SQL query:

   select * from ::fn_servershareddrives()

   The output, shown in Figure 6-9, should reflect all of the drives SQL Server can use.

   Figure 6-9: Output of the fn_servershareddrives function.

2. Open SQL Server Enterprise Manager, and try to create a new database. All drives should be available for use, as shown in Figure 6-10.

   Figure 6-10: Drive letter available in SQL Server Enterprise Manager.

Disabling Unnecessary Services

If you have not done so already, disable any Windows services that SQL Server does not use and are not needed for core operating system functionality.

Assigning a Static Port Number

For each IP address, you should use a static port number to ensure that the port is always the same in a failover. To change the port number associated with each IP address of your instance, follow these steps:

1. Start Server Network Utility (SQL Server Server Network Utility) from the SQL Server menu group.

2. From the server drop-down list, in the Instances group, select the instance to modify. Under Enabled Protocols, select TCP/IP. Click Properties, as shown in Figure 6-11.

   Figure 6-11: General tab of the SQL Server Network Utility dialog box.

3. In the next dialog box, enter a number for the port, as shown in Figure 6-12. Click OK.

   Figure 6-12: Changing the port number.

4. Click OK.

5. Repeat Steps 2 to 4 to change any other IP ports associated with the instance.

6. Click OK when finished. The message in Figure 6-13 is displayed. Click OK.

   Figure 6-13: Information confirmation message.

7. Stop and restart SQL Server virtual server for the port changes to take effect.

Enabling Advanced Security

If you need to use IPSec, Kerberos, or Secure Sockets Layer (SSL) with your failover cluster as part of your overall security, it is definitely possible. It takes proper planning, so read the following information carefully .

IPSec

IPSec, although it technically can work in a clustered environment, is not really designed for that, so you might want to evaluate the effectiveness of IPSec in your cluster scenario. The underlying issue is that in a failover, the Internet Key Exchange Security Associations are not moved from one node to another. Also, by default, the Security Association Idle Timer times out in 5 minutes. This means that after a failover, applications or users accessing the cluster cannot reconnect until at least 5 minutes after all cluster resources are up after the failover. Your application, should you choose to use IPSec, has to tolerate these problems.

Kerberos

Kerberos is supported on a server cluster and subsequently failover clustering under both Windows 2000 (with Windows 2000 Service Pack 3 or later) and Windows Server 2003. To see the steps to configure Kerberos on a Windows 2000 post “Service Pack 3 server, read Knowledge Base article 235529, Kerberos Support on Windows 2000 “Based Server Clusters.

SSL Certificates

SSL certificates are fully supported on all versions of Windows 2000 and Windows Server 2003 clustering. To use SSL encryption on a SQL Server 2000 cluster, a certificate must be issued to the Virtual SQL Server Name.

Configuring SQL Server Resources

Once your failover cluster is installed, you might need to modify some of the parameters associated with the SQL Server resources. These include setting a preferred owner if you have more than two nodes, as well as your failover and failback policies.

Setting Preferred Owners

When you use more than two nodes in a failover cluster, it is important to consider which node should own the SQL Server processes in the event of a failover. The potential owners are configured with SQL Server Setup. With up to four nodes available under 32-bit and eight under 64-bit, there should be an order that makes logical sense for the production environment. You should set the failover preferences for the group containing all the resources for the instance of SQL Server (not only on the virtual server) to ensure that all resources properly fail over to the same node. For example, in an N + 1 configuration, each group would have the idle node second in the list of preferred owners. This means that if any of the nodes failed, the resources on that node would move to the idle node. To set preferred owners, follow these steps:

1. Start Cluster Administrator. Right-click the group containing the SQL Server 2000 virtual server, and then click Properties.

2. On the General tab, the Preferred Owners list box displays all cluster nodes that can potentially own the processes in that group, and the current order in which they would fail over. To change the order, click Modify.

3. In the Modify Preferred Owners dialog box, shown in Figure 6-14, make any changes to the preferred failover order. All nodes currently configured as potential owners appear in the right pane in the order of failover preference. For example, there are four nodes in a cluster: Dennis, James, Tommy, and Chuck. All four nodes of the cluster can be potential owners, and the order of failover if Dennis goes down is set to be James, then Tommy, and finally Chuck if both James and Tommy are unavailable.

   Important

   Do not use this procedure to add nodes to the SQL Server virtual server definition. You must use SQL Server Setup as described later in the section Adding or Removing a Cluster Node from the Virtual Server Definition and Adding, Changing, or Updating a TCP/IP Address. Figure 6-14: Modify Preferred Owners dialog box.

Failing Back to the Preferred Owner, Thresholds, and Other Parameters

You need to determine how you want your resources to behave in the aftermath of a failover ”do you want them to automatically fail back to the preferred owner once it comes online? How many times do you want to try to start the resources on the current node before allowing it to fail over to another server?

Resource Group Failback

All resources fail over to another node at the group level. In the event of a failover, the cluster group containing the SQL Server resources can be configured to fail back to the primary node when and if it becomes available again. By default, the Prevent Failback option is not selected because usually there is no problem with continuing on the secondary node when you have properly planned all of your resources. This setting provides an opportunity to analyze and repair the problem on the failed node. If you need to move the resources, you can do it manually later. You should not change this setting, but if you need to, here is how:

1. Start Cluster Administrator. Right-click the group containing the SQL Server 2000 virtual server, and then click Properties.

2. In the Properties dialog box, click the Failback tab, shown in Figure 6-15.

   Figure 6-15: Resource group Properties Failback tab.

3. To prevent an automatic failback, select Prevent Failback. To allow automatic failback, select Allow Failback, and then one of the following options:

   • Immediately This means that the moment Windows Clustering detects that the preferred cluster node is online, it fails back any resources. This is not advisable because it could disrupt clients and applications, especially at peak times in the business day.

   • Failback Between N And N1 Hours This option allows a controlled failback to a preferred node (if it is online) during a certain period. The hours are set using numbers from 0 through 23.

Configuring Resource Parameters

Failovers can also be controlled in terms of a threshold, meaning that after a certain point, a resource is not able to fail over to another node. There are two levels of thresholds: resource and cluster. Depending on how the resource is configured, it can affect the group failing over to another node. To configure resource parameters, follow these steps:

1. Start Cluster Administrator. Select the proper group containing the SQL Server 2000 virtual server, then right-click the resource to alter, and click Properties.

2. In the Properties dialog box, click the Advanced tab, shown in Figure 6-16.

   Figure 6-16: Advanced tab of the Properties dialog box for a cluster resource.

3. Select Do Not Restart if the Cluster Service should not attempt to restart or allow the resource to fail. By default, Restart is selected. If Restart is selected, configure the restart policy:

   • Affect The Group To prevent the failure of the selected resource from causing the SQL Server group to fail over after the specified number of retries (Threshold) has occurred, you should clear the Affect The Group check box.

   • Threshold This is the number of times the Cluster Service will try to restart the resource, and Period is the amount of time (in seconds) between retries. For example, if Threshold is set to 0, and the Affect The Group check box is selected, on detection of a failure the entire group with the resource is failed over to another node. Do not modify Threshold unless you are directed to by Microsoft PSS.

     Tip	If you are not using a resource, such as the clustered full-text resource for each SQL Server virtual server, clear the Affect The Group check box. Do not change the Affect The Group status of data or log disks, the SQL Server IP address or network name, or SQL Server Agent and SQL Server itself.

4. Do not ever modify the Looks Alive Poll Interval and Is Alive Poll Interval settings. These settings are configured to be optimal for the specific application, which in this case, is SQL Server.

5. Do not modify Pending Timeout. The value, represented in seconds, is the amount of time the resource in either the Offline Pending or Online Pending states has to resolve its status before the Cluster Service puts the resource in either Offline or Failed status.

6. Click OK.

Cluster Group Thresholds

Not unlike a resource s thresholds, you can configure thresholds at a group level to tell the server cluster how many times to try to restart the group on one node before attempting to fail the group over to another node in the server cluster.

1. Start Cluster Administrator. Right-click the group containing the SQL Server 2000 virtual server, and then click Properties.

2. In the Properties dialog box, click the Failover tab, shown in Figure 6-17.

   Figure 6-17: Failover tab for a cluster group.

   To configure the failover policy, in the Threshold box, enter the number of times the group is allowed to fail over within a set span of hours. In the Period box, enter the set span of hours. For example, if Threshold is set to 10 and Period is set to 6, the Cluster Service fails the group over a maximum of 10 times in a 6-hour period. At the 11th failover in that 6- hour period, the server cluster leaves the group offline. This affects only resources that were failed over; therefore, if the SQL Server resource failed 11 times, it would be left offline, but the IP could be left online.