DIY Active/Active SQL Cluster Demo in Hyper-V with ISCSI – Step by Step Instructions

Here are my instructions for setting up an active/active SQL cluster in your Hyper-V environment with iSCSI. Please contact me with any questions or suggestions for clarification.

Prerequisites:
Basic knowledge of Windows administration (Joining domains, adding users)
Windows Server 2008 R2 (SP1 recommended)
SQL Server 2008 R2 (SP1 recommended)
To recreate this demo you’ll need a Windows Server 2008 R2 Hyper-V Environment with 5GB ram (maybe less will work)
Have an Active Directory domain and controller setup and available, with a valid login in the Domain Admins group.
You’ll want to decide your Virtual Machine names, and designate several valid unused IP addresses for use in this demo. (See SQL Clustering Worksheet)
Start in your Hyper-V Server, Hyper-V Manager
Create Virtual Networks
You’ll need two networks, an External one for local lan traffic (you probably already have this), and a new Private one for the iSCSI traffic.Set up  virtual network for iSCSI
In Hyper-V manager, click Virtual Network Manager
Click New Virtual Network
Name:iSCSI Network (See worksheet)
Type:Internal Only (only VMs can access)

Create Virtual Machines
You’ll need 3 Virtual Machines: SAN Host and two SQL Nodes
SAN (can be on DC or separate machine)
SQL Node 1
SQL Node 2

Create Parent Disk
I created a Virtual Machine called BASE1 with a single Dynamically Expanding VHD.
Installed Windows Server 2008 Enterprise R2 SP1 64 bit (32 may work) and all Windows Updates, and ran Sysprep in c:\windows\system32\ (Choose OOBE, Generalize, Shutdown options)
Deleted BASE1 Virtual Machine (Virtual Hard Disk remains undeleted)

Create New Virtual Machines
For each Virtual Machine (SAN,SQL Node 1, SQL Node 2): See Network Diagram for reference

  1. In Hyper-V Manager choose Create New Virtual Machine.
  2. Choose location for the Virtual Machine files (I use V:\VMs\<VM Name>)
  3. Grant 1024MB ram.
  4. Choose Virtual Network (should have already created) that is configured as External (see above.)
  5. Choose to create Virtual Hard Disk later and Finish.
  6. Enter Settings for the new VM.
  7. Select IDE0, Add a new Hard Disk, Click New.
  8. Choose a Differencing Disk.
  9. Enter filename under the folder for your Virtual Machine (I use V:\VMS\DaveSAN\DaveSAN.vhd)
  10. Choose the parent disk (I use V:\VMs\BASE\BASE1.vhd) and Finish.

Repeat above steps for SQL Node 1 and SQL Node 2

Start VMs and configure networking and domain

For each Virtual Machine (SAN,SQL Node 1, SQL Node 2):

  1. Start the Virtual Machine, allow it to configure and reboot.
  2. Follow the defaults for the setup, but choose Skip in the activate windows screen. (Can work within 180 day trial, or activate with key later)
  3. Set local Administrator password (P@ssw0rd is a good choice)
  4. In Network Connections, choose properties for your Local Area Network connection..
  5. Uncheck IPv6 (for simplicity), enter properties for IPV4, and enter IP (Each VM need different unused IP! See worksheet under ‘My Local Lan IP’), Subnet Mask, Gateway and DNS (See your worksheet for these values)
  6. Rename local area network connection (I use Local LAN)
  7. VERIFY: In Status for this network connection you should see sent and received packets.
  8. Join your computer to your domain, give your machine name (See worksheet), and enter valid Domain Admin login to complete the process.
  9. Once you reboot, login as domain administrator from this point forward.
  10. Once you’ve logged in to the domain successfully, shut down the VM.
  11. In Hyper-V Manager, in the Setting for the VM, choose Add hardware, Network Adapter, click Add.
  12. Choose the Private Network you created for ISCSI traffic (See worksheet), click OK.
  13. Start the VM and login.
  14. In Network Connections, choose properties for your new Local Area Network connection, not the Local Lan connection..
  15. Uncheck all by IPv4 (again for simplicity), enter properties for IPV4, and enter IP (Each VM need different unused IP! See worksheet under ‘My iSCSI Lan IP’), Subnet Mask, No Gateway or DNS. Click OK
  16. Rename local area network connection (I use iSCSI LAN)
  17. Turn off Windows Firewall for all networks (for simplicity)
  18. (Optional: Firewall Security Policy to enable iSCSI, SQL, (MSDTC Policy )

Repeat above steps for SQL Node 1 and SQL Node 2
VERIFY: From the SAN VM, confirm you can ping both SQL Node IPs on the ISCSI LAN network (See worksheet).

Create Snapshots (optional)
Now that networking is configured, you may wish to take a snapshot of each machine so you can return to this point as a fallback if you misconfigure iSCSI.
For each Virtual Machine (SAN,SQL Node 1, SQL Node 2):
From Hyper-V Manager, Shutdown the VM, Create Snapshot, and Start the VM

Configure iSCSI Target on SAN

Login to the SAN VM
Create a folder to hold vhd files for disks we will create. (I use C:\Disks)
Download Microsoft iSCSI Target 3.3 software from http://www.microsoft.com/download/en/details.aspx?id=19867
Run the file to extract files to any local folder (I use c:\iSCI)
Install the iscsitarget_public.msi file under the x64 folder, choose default options.

Create iSCSI disks
We’ll create 3 disks, a 1GB disk for Quorum, and two 10GB disks for Data, one for the first SQL Instance and one for the second SQL Instance
(Keeping it simple. Could add additional disks for logs, tempdb, etc.)
Launch Microsoft iSCSI Software Target application

For each of 3 disks:

  1. Right Click Devices, chose Create Virtual Disk, click Next
  2. Enter path for the disk, I use C:\Disks\LUN0.vhd)
  3. Enter 1024 for disk size in MB
  4. Enter LUN0 for Description
  5. Choose Next (assign initiators later) and Finish

Repeat above steps with LUN1 and LUN2, each 10240MB (10GB)
[see image iSCSITarget – 3 LUNs.png]

Create iSCSI Target
In Microsoft iSCSI Software Target application, Rick-Click iSCSI Targets and choose Create New Target. Click Next.
Enter name and description (I use DaveSAN1, First Target), Click Next, then Click Advanced.
Click Add, choose Identifier Type as IP Address, and enter the iSCSI LAN IP addresses of SQL Node 1 (See worksheet, I use 10.1.1.11), Click Next and Finish
Repeat previous step for SQL Node 2 (I use 10.1.1.12)
Under iSCSI Targets, Right Click your new iSCSI Target, choose Add Existing Virtual Disk to Target, and choose all disks
[see image iSCSITarget – Target with Disks.png]
Right-Click your new target, Properties, Advanced, turn off Enforce idle connection timeout
[see image iSCSITarget – Target Properties – Enforce idle connection timeout.png]

Configure iSCSI Initiators
Initiator for SQL Node 1
Login to SQL Node 1
Under Administrative Tools, Choose iSCSI Initiator, and Click Yes to start the service
In Target, enter iSCSI LAN IP of the SAN (I use 10.1.1.10) and click Quick Connect, then click Done.
Enter Disk Management (Start,Run, diskmgmt.msc) Click OK to Initialize all 3 disks.
[Note the order the disks are added is arbitrary, may not be in the order you created them. Could add disks at target and initialize one at a time at initiator to control this. Any other way?]
Locate the 1GB drive, Right-Click, Configure as a Simple Volume,  Drive letter Q:,Volume Name Quorum, NTFS, Quick Format
Locate the first 10GB drive, Right-Click, Configure as a Simple Volume,  Drive letter S:,Volume Name Data1, NTFS, Quick Format
Locate the 1GB drive, Right-Click, Configure as a Simple Volume,  Drive letter T:,Volume Name Data2, NTFS, Quick Format
[Note the drive letters are arbitrary, and in SQL 2008 R2 unnecessary as we could use mount points. Just need to be consistent among nodes.]
[see image SQL Node 1 – Disk Management – 3 Disks.png]

Initiator for SQL Node 2
We want to verify we can connect to the disks on SQL Node 2.
Before leaving SQL Node 1, take all 3 disks offline (Right-Click greay area on left of each disk, choose Offline)
Login to SQL Node 2
Under Administrative Tools, Choose iSCSI Initiator, and Click Yes to start the service
In Target, enter iSCSI LAN IP of the SAN (I use 10.1.1.10) and click Quick Connect, then click Done.
Enter Disk Management (Start,Run, diskmgmt.msc) Click OK to Initialize all 3 disks.
[Note the disks are added with arbitrary drive letters.]
Change the drive letters, if necessary, so that the drive letters match SQL Node 1. (I use  Quorum is Q:, Data1 is S:, and Data2 is T:)

Configure Windows Cluster
Continuing in SQL Node 2:
In Server Manager, navigate to Features, Add Feature, Failover Clustering, click Next, click Install
In Administrative Tools, click Failover Cluster Manager
Click Validate a Configuration
In the Name field, enter the Local LAN IP of SQL Node 2 (I use 10.0.1.152), click Add (it will enter the FQDN of the server)
Choose All Tests, Next, (test should pass after a few minutes with warnings but no errors), can view (and  save report as MHT on desktop), finish
While you are waiting, or afterwards:
Switch over to SQL Node 1
In Server Manager, navigate to Features, Add Feature, Failover Clustering, click Next, click Install
Switch Back to SQL Node 2 and continue..

VERIFY: No errors in Cluster Validation Report
From SQL Node 2, Click Create a Cluster
In the Name field, enter the Local LAN IP of SQL Node 2: (I Use 10.0.1.152), click Add
Enter Cluster Name: (I use DaveCluster1)
Enter IP Address for your new Cluster (I use 10.0.1.160)
Next, Finish, DaveCluster1should be created
Navigate to DaveCluster1, Storage, right-click, Add a Disk. choose smaller 1GB disk
Right-click DaveCluster1, More Actions, Configure Cluster Quorum Settings, click Next, choose Node and Disk Majority, choose drive Q, click Next, Next, Finish
Right-click Storage, Add a Disk. choose 10GB Disk drive corresponding to S:, using Disk Manager to verify whether it’s Disk 1, Disk 2 or Disk 3

VERIFY: S: Drive (not T: ) listed under Storage, Available Storage
Right-click Nodes, Add Node, enter  Local LAN IP of SQL Node 1: (I Use 10.0.1.151), click Add
Follow defaults and run all tests. (Will take longer as it tests the disk failover.)
After tests, Next and Finish, and your 2nd cluster node is installed. (Name is in lower case, not sure why or how to change.)

VERIFY: See if you can fail the resource for drive S: back and forth successfully.

Install SQL Instance1

  1. In Active Directory Users and Computers:
  2. Create a user for SQL Service, note the username and password. (I use DaveSQLService)
  3. For both SQL Node 1 and SQL Node 2:
  4. Install Framework 3.5 from Server Manager, Add Role, Application Role, and choose defaults.
  5. From SQL Node 1:
  6. Run setup on SQL Server 2008 R2 (Standard or Enterprise) In Windows Explorer, double-click DVD drive F: (may need to mount ISO from Hyper-V settings)
  7. Click Installation, click New SQL Server Failover Cluster Installation
  8. After Setup Support Files install, click Install
  9. Click Next, leave default on Evaluation, click Next
  10. Click accept, click Next
  11. Setup Support Rules should pass, with 5 warnings (MSDTC and Network Binding).
  12. Choose Database Engine, Management Tools Complete (Analysis Services is clusterable but not done for simplicity), click Next
  13. Enter SQL Server Network Name: (I use DaveSQLCluster1)
  14. Choose Named Instance, enter: Instance1
  15. Click Next 4 times, accept defaults, ensure correct Disk (from Cluster, Add Disk step above) is selected.
  16. at Cluster Network Configuration step, uncheck DHCP, enter IP Address for SQL Cluster 1 (I use 10.0.1.161)
  17. Click Next until Server Configuration, click Same account for all services, click Browse, enter SQL Service Account (I use DaveSQLService), click Check Names, choose OK, enter password (I use P@ssw0rd)
  18. Click Next, Click Add Current User. (Can click Data Directories tab to see the folders on the S: drive used for SQL)
  19. (Filestream supported for Clustering, not done for simplicity)
  20. Accepting defaults, setup should complete successfully

From SQL Node2
[Configure Clustering]
Run setup on SQL Server 2008 R2 (Standard or Enterprise) In Windows Explorer, double-click DVD drive F: (may need to mount ISO from Hyper-V settings)
Click Installation, Add Node to a SQL Server Failover Cluster
Choose defaults, enter password, complete installation
VERIFY:  From Failover Cluster Manager, Under Services and Applications, Right-Click your SQL Service, More Actions, Move this Service..Move to other Node, verify success.

Install SQL Instance2
From SQL Node 2:

  1. Run setup on SQL Server 2008 R2 (Standard or Enterprise) In Windows Explorer, double-click DVD drive F: (may need to mount ISO from Hyper-V settings)
  2. Click Installation, click New SQL Server Failover Cluster Installation
  3. After Setup Support Files install, click Install
  4. Click Next, leave default on Evaluation, click Next
  5. Click accept, click Next
  6. Setup Support Rules should pass, with 5 warnings (MSDTC and Network Binding).
  7. Choose Database Engine, Management Tools Complete (Analysis Services is clusterable but not done for simplicity), click Next
  8. Enter SQL Server Network Name: (I use DaveSQLCluster2)
  9. Choose Named Instance, enter: Instance2
  10. Click Next 4 times, accept defaults, ensure correct Disk (from Cluster, Add Disk step above) is selected. (Should be only one unassigned)
  11. at Cluster Network Configuration step, uncheck DHCP, enter IP Address for SQL Cluster 2 (I use 10.0.1.162)
  12. Click Next until Server Configuration, click Same account for all services, click Browse, enter SQL Service Account (I use DaveSQLService), click Check Names, choose OK, enter password (I use P@ssw0rd)
  13. Click Next, Click Add Current User. (Can click Data Directories tab to see the folders on the T: drive used for SQL)
  14. (Filestream supported for Clustering, not done for simplicity)

Accepting defaults, setup should complete successfully

From SQL Node1
[Configure Clustering]
Run setup on SQL Server 2008 R2 (Standard or Enterprise) In Windows Explorer, double-click DVD drive F: (may need to mount ISO from Hyper-V settings)
Click Installation, Add Node to a SQL Server Failover Cluster
Choose defaults, enter password, complete installation
VERIFY:  From Failover Cluster Manager, Under Services and Applications, Right-Click your SQL Service, More Actions, Move this Service..Move to other Node, verify success.
VERIFY: Start SQL Management Studio, View Menu, Registered Servers,, Register instances davesqlcluster1\instance1 and davesqlcluster2\instance2
RUN Query against all instances:
select SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as NodeName
,SERVERPROPERTY(‘ServerName’) as ServerName
,SERVERPROPERTY(‘MachineName’) as ClusterName
,SERVERPROPERTY(‘IsClustered’) as IsClustered
–, HOST_NAME()
, name
from sys.databases
DONE! (for now..)
_____________________________

Leave a Reply

Your email address will not be published. Required fields are marked *