Creating a Clustered SQL Server Test Lab

RobertKaucher

I have added the first part of this to my blog: Creating a SQL Server 2008 Failover Cluster - Part I

The purpose of this document is to assist people who are studying for the MCITP Database Administrator 2005 or 2008 certifications in the creation of a test lab with a 2 node failover SQL Server cluster. You can run this lab setup under Hyper-V, VMware ESXi, or your favorite virtualization platform. This setup file assumes you are using Hyper-V and your shared disk provider is iSCSI Cake.

You may wish to read these articles as well.

You will need the following:
· Hardware that can easily run the required virtual infrastructure. When I set this lab up, I used a quad core system with 8 GB RAM and 4 hard drives.
· Server 2008 Enterprise or Datacenter (Download a trial here)
· SQL Server 2005 or 2008 (2005 Trial or 2008 Trial)
· iSCSI Cake, StarWind, or another iSCSI Target that support persistent reservations (MOST Linux systems, such as OpenFiler, do not w/o a commercial plugin)
· An Active Directory Domain

1. Install and update the host system.
2. Create 2 virtual hard drives that will be used as the disks for the shared storage.
3. Install the iSCSI target on the host system and create the two iSCSI disks. If you are using iSCSI Cake you should follow these directions.
4. Create the two virtual machines and install and update the OSes. (I personally create and update the first then copy it and run NewSID).
5. Create and configure a second, private network for the cluster heart beat. Ensure you can ping the guests via this network.
6. Join the two guests to the domain.
7. Connect to the iSCSI target using the iSCSI Initiator under Administrative Tools on the guest systems. Click on the Discovery tab and add the host OS. Go to the Targets tab and select logon.
8. On the first guest use Disk Manager to initialize and format the two disks.
9. Initialize the disks on the second guest.
10. Install Microsoft’s Failover Clustering on the two guests.
11. Create the Cluster. Do not forget to add both guests to the cluster in the wizard. Once created the disks will appear as in this image.
12. Run the SQL Server Installation Wizard. Do not forget to select the “create a cluster” option.
13. Run the installation wizard on the second node.


EDIT: bertieb's guide to setting up the SQL Server portion of the cluster . Covers 2000, 2005, 2008.
http://www.techexams.net/forums/sql-server-2000-2005-exams/44654-sql-server-clustering.html

EDIT 2: SQL Server 2008 is not compatible with with Windows Server 2008 R2. You should download the latest version of SQL Server 2008 R2 if you are using Windows Server 2008 R2 or attempt to slip stream SQL Server 2008 up to SP1.

RobertKaucher

The above is a rough draft... No images yet, I would like your thoughts, please.

dynamik

Nice job dude

Thanks for sharing!

RobertKaucher

My pleasure. Did you see any thing I forgot or that is inaccurate?

eMeS

This looks good to me...it's not quite what I did, but I had the benefit of having delivered a series of classes on SQL Server 2005 last year as well as access to the MOC and associated images.

Although the volume of people doing SQL Server work on here is low, your post might make a good sticky.

RobertKaucher

I was working in my lab today and realized I had not configured the quorum settings... I will need to add that to the "tutorial."

bertieb

Looks like a good effort there Robert

The following links are excellent resources for anyone building a Windows 2008/SQL2008 cluster. The SQL2008 install on a cluster is quite a bit different than previous versions, these help a great deal with working out the procedure.

Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1
Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 2
Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 3
Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 4

RobertKaucher

I edited my original post so that it has information regarding creating the cluster with Server 2008 R2. Once I got SQL Server 2008 slipstreamed to SP1 it was a pretty easy task to install and test the SQL Cluster.

blargoe

There were a bunch of hoops to be jumped through to get it SQL 2005 Clustering installed on Windows 2008 R2 as well, do you have that documented? I have a document I created for work around here somewhere that I could put out if you'd like.

RobertKaucher

Please feel free to post it! That would be a great idea. I didn't include anything for SQL Server 2005 because I am working on the 2008 exams at themoment.

blargoe

I'll see if I can find it. I've got a test cluster in vSphere and a production cluster that I'm slowly migrating toward. Both are W2008R2 with multiple SQL 2008 (SP1 slipstream) and SQL 2005 SP3 database instances.

RobertKaucher

Did you do a burn in with SQLIO or SQLIOSim/SQLIOStress on the VM?

I would like to know what the starts were if you did - if you happen to recall, of course. I have a warm spare server running on VMWare ESXi that was getting horrible readings from these two when I was doing the install. I figure even a slow server is better than no server in a disaaster situation, though.

blargoe

One of the links in bertieb's post that covered 2008 covered everything for 2008 R2, as far as I can tell.

RobertKaucher

Yes, you are correct. I believe the only issue is the slip streaming. Id on't think that was covered in his links...

blargoe

RobertKaucher wrote: »

Did you do a burn in with SQLIO or SQLIOSim/SQLIOStress on the VM?

I would like to know what the starts were if you did - if you happen to recall, of course. I have a warm spare server running on VMWare ESXi that was getting horrible readings from these two when I was doing the install. I figure even a slow server is better than no server in a disaaster situation, though.

I didn't do the burn in on the VM, as it was intended to be a lightly used test SQL cluster and none of our apps are highly transactional. Performance has been good in the test cases we've run, but I'm using RDM's mapped to our CLARiiON CX4 over Fiber rather than using a software iSCSI target so the performance should be almost as good as my production physical cluster.

RobertKaucher

Mine just uses VHDs on the ESXi server. No iSCSI.

NetAdmin2436

Thanks gents, I'm going to have to bookmark this for later...

user2010

blargoe : I should mention that you are particularly correct about performance, but you should know that all bread is not baked in one oven: StarWind is showed great results, during it's SAN software was used in test, performed by Intel. You can Check results here:
http://download.intel.com/support/network/sb/inteliscsiwp.pdf

As for the topic, please check follow:
StarWind iSCSI SAN Solution for MS SQL Server Data Sheet
How To Set Up SQL Server 2008 Cluster
How To Create SQL Failover Cluster

Regards

GagHalfrunt

I did this with Hyper-V, 3 VMs with Starwind on the third. Perfectly good for playing with SQL clustering but I wouldn't do it that way on a "real" server. Good for practising mind.

If I were I'd probably cluster at the VM level instead if it were me.

homerj742

Great job! Thanks Robert!