Clustered SQL Server 2005: Capturing an Image and Creating a Tier

Intended for use with Cassatt Active Response Premium Edition and Data Center Edition V5.1.

The Clustered SQL Server 2005 blueprint describes how to deploy an active/passive SQL Server 2005 cluster in the Cassatt Active Response environment. If you haven't read Understanding Blueprints for Deploying Applications, read that first for background and context.

This blueprint requires that you create a customized image and tier to run VMware ESX Server as a host for your clustered SQL Server VM guest tier. See the prerequisites for specifics.

Prerequisites

	Software requirements: VMware ESX Server 3.0.1 Windows Server 2003 SQL Server 2005 An MS Active Directory image and tier as described in Active Directory: Capturing an Image and Creating a Tier.
	Network Requirements—for more information, see Network Addresses: Calculating Requirements: Primary network: Make sure the network has adequate addresses available. If necessary, add a new network to Cassatt Active Response using the Controller. Private network for internal SQL Server cluster communications: Use the Controller to add a network for SQL Server cluster communications; no gateway is necessary. Size the network to include IP addresses for maximum nodes in both the ESX Server host tier and the clustered SQL Server tier. MSCS IP resources: assign three static IP addresses from the Cassatt Active Response network. Either select addresses from outside the Cassatt Active Response DHCP range, or use the Controller to reserve addresses from within the DHCP range.
	Storage requirements: SAN with 1 LUN available if using virtual shared storage; 4 LUNs if using raw devices.
	Host tier requirements: Follow the ESX Server 3.0 blueprint using these parameters: Set up FC SAN storage as described in the prerequisites. Follow all "FC SAN only" steps in the blueprint. After you install the ESX Server software, install the patches listed in VMware KB 2021. Create a VM; add a second virtual network adapter to the VM. During cccapture, set minimum NICs = 2 During tier creation: Set minimum, target, and maximum nodes to 2 Set HBA required = y and specify your HBA key Select the two networks described above; elect the Dedicated checkbox for the SQL Server cluster network Retain the image host for use in creating the clustered SQL Server image.
	Tier Settings: SLA: This blueprint uses a node-based SLA in order to ensure nodes are available for the cluster. To understand SLAs, see Understanding Tier Configuration and Personalization. Tier Dependencies: Select your ESX Server 3.0 host tier as a tier dependency to ensure that VMs are available for this tier. Also, select the MS Active Directory tier as a dependency. For more information, see the sidebar Tier dependencies: what you should know. Custom Attribute: Use the custom attribute from the VMs in the host tier. Because the custom attribute represents the storage access, use only one custom attribute to ensure that all VMs allocated to the tier have the same storage access. For more information, read about multiple storage locations and virtual application node allocation. HBA key: use the HBA key from the VMs in the host tier.

The blueprint steps

This blueprint differs somewhat from the standard high-level blueprint steps:

• For this blueprint, reserving the image host is a prerequisite because VMware must already be running before starting this blueprint.
• The SQL Server software is installed during personalization, so this blueprint does not include a software installation step after installing the operating system.

top

Step 1: Install and configure the operating system

Select Windows.

Step 2: Capture the image

In this step, you will capture the base image from the image host using the cccapture command—an interactive script. Although you can change values later, it is most efficient to supply valid values now.

Follow these steps to capture the image:

1. If you haven't already done so, shut down Windows and VMware on the image host.

If you do not shut down Windows and VMware prior to image capture, the image capture will fail.

2. Log into the active control node as root.
3. Enter the following command to start the cccapture interactive script:
   
   /opt/cassatt/bin/cccapture
4. Answer each prompt.
   
   The next table lists the recommended values for the blueprint; unless you have site specific reasons, you can accept the default values for other cccapture prompts.
   
   For names, descriptions, and other discretionary fields, use values that are meaningful to you. In the image name, do not use spaces or characters (for example, /) special to Unix-based file systems such as Linux and Solaris.
   
   

   At this prompt...	Enter	Notes
   Enter the type of operating system for this image	windows
   Do you want to specify the hardware requirements for a tier using this image? [n]	y
   Enter minimum number of network interface cards (NICs) for this image: [1]	2
   Enter the amount of time in seconds that the system should wait for the services provided by this image to start (after this time has passed, the system will attempt to reboot or replace the node): []	1800
   OS Monitoring Options Specify the operating system monitoring that will be used for this image (at least one monitoring option must be configured): Monitor via SNMP? [n]	y
   Monitor via ping? [n]	y

   
   
5. In the Controller, release the image host back to the free pool by selecting the node using the checkbox, then select Node Actions > Release from Image Capture.

top

Step 3: Create the tier

Return to the Controller to define the tier. Cassatt Active Response prepopulates tier values with the parameters you set during image capture. If necessary, you can change them during tier creation.

1. On the left navigation pane, click Tiers to display the Tier List.
   
2. On the Tier List page, click the New Tier button (on the right).
3. On the Properties page, set the properties for the tier. The next table lists the suggested values for this blueprint; unless you have site-specific reasons, you can accept the defaults in other fields.
   
   

   At this prompt...	Enter...	Notes
   Allow harvest	Off	Cassatt Active Response does not harvest nodes such that a tier has less than its minimum required number of nodes. Setting harvesting to Off, in this case, is good practice, indicating that you don't want nodes harvested from the tier.

   
   
4. On the SLA page, select the node-based SLA type and set as follows.
   
   

   At this prompt...	Enter...	Notes
   Operational Target	2	Set target = minimum = maximum. This blueprint specifies 2 for each value, which allows you to implement an active/passive cluster. If you intend to implement an active/active cluster, set the three values to your required number (>2).
   Minimum Nodes	2
   Maximum Nodes	2

   
   
5. On the Requirements page, set the hardware requirements for the tier. Unless you have site-specific reasons, you can accept the defaults in fields not listed.
   
   

   At this prompt...	Enter...	Notes
   HBA	HBAKey	Select the checkbox and use the same HBA key that's assigned to the VMs in the host tier.
   Custom Requirements	CustomAttribute	Assign the same custom attribute to this tier as you assigned to the VMs in the host tier. For more information, read about multiple storage locations and virtual application node allocation.
   Dependencies	VMHostTier	Specify the VM host tier.

6. On the Networks page, select the primary network. Select the private network for the SQL Server cluster and check the Dedicated checkbox.
7. On the IPs and Hostnames page, assign IP addresses or host names, or accept the default settings.
8. You can close the Creating Tier Progress page and continue with the next section.
   

top

Step 4: Personalize the image instances

Follow these steps to personalize the image instances. You will need Windows Administrator priviledges for most of these steps.

1. On the tier page, select the clustered SQL Server tier using the checkbox, then select Personalize from the actions dropdown menu.
   
   Cassatt Active Response boots the maximum nodes for the tier.
2. Configure the shared storage using one of these options:
   
   

   Raw device storage

   Follow the instructions in "Adding Hard Disks to Both Nodes" on page 32 of chapter 3 of Setup for Microsoft Cluster Service. In addition to the quorum device and a data storage device, add a device for MS DTC. Add all three devices to both VMs. If VMware displays an error message when you add a raw device, click OK to bypass the message and continue adding all three raw devices. When you are finished with the first VM, repeat the entire process for the same VM: on the second pass, you should not encounter any errors.

   Virtual storage

   Follow the instructions in "Adding Hard Disks to Both Nodes" on page 23 of chapter 2 of Setup for Microsoft Cluster Service. In addition to the quorum device and a data storage device, add a device for MS DTC. Add all three devices to both VMs.

   
   
3. Check the network connectivity: power on each VM and ping both the public and private interfaces from VM1 to VM2 and vice versa.
4. Verify that the shared storage is visible to each VM: go to Start > Control Panel > Administrative Tools > Computer Management > Storage > Disk Management.
5. Configure each VM:
1. Set the host name to a name that describes the role of the server. To do this, use NewSID, Microsoft's security ID program:
   1. Open a browser and navigate to to http://www.sysinternals.com.
   2. On the main menu click Security Utilities.
   3. On the Sysinternals Utilities:Security page, click NewSID.
   4. Scroll to the "Using the Source" section and click Download NewSID.
   5. Run the NewSID utility on your server and change the SID and the name of the server.
2. Set the VM DNS and WINS servers to the IP address of the Active Directory node. (Look in the Active Directory tier to find this IP.)
3. Join your Active Directory domain: go to Start > My Computer > Properties > Computer Name > Change and type the domain name.
4. Set up your private network as described in steps 1–16 of Recommended private "Heartbeat" configuration on a cluster server (remaining steps are done later in this procedure).
5. Set up your shared storage: go to Start > Control Panel > Administrative Tools > Computer Management > Storage > Disk Management.
   1. Initialize the disk.
   2. Create a new partition and assign a letter to the disk (e.g., Drive Q:), assign a descriptive name to the Volume Label (e.g., Quorum), format using NTFS, and don't set the disk to use Dynamic Links.
   3. Repeat for each disk.
6. Repeat these steps for the second VM.
6. Create a cluster:
   1. Shut down VM2 and create a cluster on VM1, as described in Checklist: Planning and creating a server cluster: Start > Administrative Tools > Cluster Administrator.

   If you receive a warning icon next to Checking cluster feasibility on the Analyzing Configuration page of the New Server Cluster Wizard, follow the workaround described in Shared disks are missing or are marked as "Failed" when you create a server cluster in Windows Server 2003. Installing the latest service pack, described in the same document, may not be sufficient to resolve the issue.

   Ignore any warnings about DHCP.

   The wizard creates a resource group for each disk. Name the groups according to their roles, e.g., MS DTC or SQL Server (uses the data storage disk), and create IP and Name resources for each group; see How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster. Use the IP addresses you reserved in the prerequisites.

   1. Complete the private network setup as described in steps 18–22 of Recommended private "Heartbeat" configuration on a cluster server.
   2. Add the second VM to the cluster as described in Add additional nodes to the cluster.
7. Install SQL Server: follow the procedures in How to: Create a New SQL Server 2005 Failover Cluster (Setup).
8. Test the failover capabilities between the two VMs.
9. When you are finished, return to the Controller, select the tier, and select "Personalization Complete" for the tier.

Step 5: Allocate nodes and activate the tier

Follow these steps to allocate nodes and activate the tier.

1. From the Tiers table on the Domain page, select the checkbox for the tier.
2. From the Tier Actions dropdown menu, select Allocate Nodes. Click Allocate Nodes on the confirmation page.
   
   Cassatt Active Response allocates nodes and displays the Tiers table.
3. Make sure none of the VMs allocated to the tier have a VMware Console attached.

The VMware Console can be attached to VMs that are not being activated in this tier, including those running on the same host server.

4. Again, select the checkbox for the tier.
5. From the Tier Actions dropdown menu, select Activate. Click Activate on the confirmation page.
   
   Cassatt Active Response starts the operating system and the applications on each node in the tier.

Do not attach a VMware Console to an ESX VM before it has finished booting.

Attaching a VMware Console to an ESX VM while it is booting under Cassatt Active Response Control can interrupt changes Cassatt Active Response makes to key definition items, disrupting the boot process.

To determine when it is safe to attach a remote console to a VM, do one of the following in the Controller:

1. On the Tier Activation status page, click Show Details.
2. In the events table, watch for an event called "Power Cycle Node" with status "Complete."
   
   OR
   
3. In the node list for the tier, wait until the node is "online."

When either of these things happens, it is safe to attach a remote console to the VM.

This completes the procedure to create and deploy a clustered SQL Server image.

VM Reference and Troubleshooting

Read additional information and tips about using VMs in your Cassatt Active Response environment.

top

Was this article useful? Tell us what you think.
Email infocentral@cassatt.com.