Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V

  • Article

Author: Cephas Lin
Contributors: Oleg Bulanyy

Applies to: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012

SQL Server Failover Clustering has been in SQL Server iterations since SQL Server 6.5, and in SQL Server 2012 it is called AlwaysOn Failover Cluster Instances (FCI) with numerous improvements and added features. In this blog post, when referring to either SQL Server Failover Clustering in SQL Server 2008 and SQL Server 2008 R2 or AlwaysOn FCI in SQL Server 2012, I will use the term FCI.

With many today looking for cost-effective ways to evaluate and learn about HADR solutions in the SQL Server offering, one question people ask is: How do I implement SQL Server Failover Clustering in Hyper-V? In this blog, I will show you how to quickly setup a two-server FCI in a single Hyper-V server with no extra hardware.

Remember that the walkthrough only gives you a jumpstart. A virtualized HADR solution using SQL Server can indeed help reduce cost while maintaining performance as well as reliability. However, successful implementation of a production HADR solution, virtualization or not, is magnitudes more involved than the steps here. You can find a more in-depth discussion at Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery. The following is only a partial list of considerations:

  • Multiple points of failure, both hardware and software
  • Storage performance
  • I/O performance
  • Network performance
  • Failover performance
  • Windows Server Failover Clustering (WSFC) quorum health
  • Disaster recovery procedures
  • Manageability and maintainability
  • Client connectivity

However, given that you understand the basic concepts of HADR and are ready to move on, knowing where to go from here is sometimes the hardest. For me, it's much easier to make sense of something when I can get my hands dirty in it. So this blog, you can say, can be titled How I Got Started with HADR Using SQL Server in My Own Basement.

Prerequisites

First things first:

Your FCI Template

Next, draft a template for your FCI. After you have this template, you can create an FCI with the parameters you desire using the steps later.

Version

[SQL_Version]

FCI Name

[FCI_NAME]

Instance Name

[INSTANCE_NAME]

Machine Names

[MACHINE_NAME1]

[MACHINE_NAME2]

File Server Name

[FILE_SERVER_NAME]

WSFC Cluster Name

[CLUSTER_NAME]

Cluster IP Address

[CLUSTER_IP]

Cluster Service IP Address

[SERVICE_IP]

SQL Service Account

[SQL_SERVICE]

SQL Agent Account

[SQL_AGENT]

Note: Make sure that the IP addresses you want to use do not conflict with IP addresses already used by other servers, and the SQL service and agent accounts have been created on the DC.

Initialize Shared Disks on [FILE_SERVER_NAME]

An FCI requires shared storage, which is typically implemented with a storage area network (SAN) connected by fibre channel. SAN is expensive, while the iSCSI software target is free! iSCSI software targets can be implemented directly in Hyper-V with no more than an extra VM. Again, I should note that this is a decision solely based on the cost of implementing a test environment, because production environment use fibre channels for the performance benefits and SANs for its data protection.

Note that the file server does not need to be built from scratch each time you rebuild an FCI. You simply need to discard the previous iSCSI target and create a new iSCSI target for the new FCI.

Now finally, perform the following on [File_SERVER_NAME] :

  1. If you have not already done so:

    1. Install Windows Server 2008 R2 SP1 and run Windows Update to the current patch level. See Windows Server 2008 R2.
    2. Join the machine to your domain.
    3. Install iSCSI Software Target 3.3.

  2. From the Start menu, open Administrative Tools > Microsoft iSCSI Software Target.

  3. Right-click iSCSI Targets and select Create iSCSI Target.

  4. In the iSCSI Target Identification page, give the iSCSI target a name and click Next.

  5. In the iSCSI Initiators Identifiers page, click Advanced, then do the following for both [MACHINE_NAME1] and [MACHINE_NAME2] in the Add/Edit Identifier dialog:

    1. In the Identifier Type list, select IP Address.
    2. In the Value box, type the IP address of the respective machine.
    3. Click Next, then click Finish.

  6. Right-click the iSCSI target you just created and select Create Virtual Disk for iSCSI Target.

  7. In the File page, type a path for the VHD file to create, then click Next.

  8. In the Size page, type 512 (MB), then click Next.

  9. Click Next again, then click Finish.

    You have now created the shared disk that will be used as the quorum disk for the WSFC cluster.

  10. Right-click the same iSCSI target you just created and select Create Virtual Disk for iSCSI Target. Then create a second virtual disk that is sufficiently large for your scenario. For example: 10000 (MB). This will be used as the data disk for the WSFC cluster.

Initialize SQL Server machines

Perform the following on the two machines that will host [FCI_NAME] :

  1. Create a new Hyper-V machine (or reformat your hard drive if you are implementing this walkthrough using hardware).
  2. Install Windows Server 2008 R2 SP1 and run Windows Update to the current patch level. See Windows Server 2008 R2.
  3. Join the machine to the domain.
  4. Add the account you will use to install SQL Server to the BUILTIN\Administrators group.
  5. Follow the steps below to prepare the iSCSI disks for failover clustering.
    1. From the Start menu, open Administrative Tools > iSCSI Initiator.
    2. Click Yes in the pop-up dialog to start the iSCSI Service.
    3. In the Targets tab, type [FILE_SERVER_NAME] and click Quick Connect.
    4. The Quick Connect window should open, showing that the target is now connected. Click Done.
    5. In the Volumes and Devices tab, click Auto Configure, then click OK.
    6. From the Start menu, open Administrative Tools > Server Manager.
    7. In the browser pane, navigate to Server Manager > Storage > Disk Managemnt.
    8. You should now see two iSCSI disks that are set to Offline. Right-click each the box that indicates Offline and select Online.
    9. Right-click each of the boxes again and select Initialize Disk.
    10. Right-click each of the box that indicates Unallocated and select New Simple Volume. Assign the following drive letters to the disks:
      • Quorum disk: Q
      • Data disk: Z

  1. Add the Failover Clustering feature using the following PowerShell commands:

    Import-Module Servermanager

    Add-WindowsFeature Failover-Clustering

  2. For SQL Server 2012, install KB 976097

  3. For SQL Server 2012, install KB 2494036

Configure [MACHINE_NAME1]

  1. Login using your installation account.

  2. Create, configure quorum for, and validate a new failover cluster with the following PowerShell command (run with administrative privileges).

    Import-Module FailoverClusters

    New-Cluster -Name Cluster1 -Node [MACHINE_NAME1] , [MACHINE_NAME2]

    Get-ClusterAvailableDisk | Add-ClusterDisk

    Set-ClusterQuorum –NodeAndDiskMajority "Cluster Disk 1"

    Test-Cluster

    NOTE: A few important things about the commands above:

    • The New-Cluster command automatically adds the iSCSI disks you have prepared to the new cluster. In case it does not, the Add-ClusterDisk command adds the iSCSI disks to the cluster. You can use Failover Cluster Manager to see the drive letter assigned to each cluster disk.
    • You must test the cluster before proceeding with the SQL Server installation. Running SQL Server Setup fails with an error if you run it without running Test-Cluster first or if Test-Cluster shows errors (not just warnings) in the test report.

  1. Change to the directory that contains the setup.exe file for [SQL_Version] , then install [FCI_NAME] using the following command:

    .\Setup.exe /QS /ACTION=InstallFailoverCluster /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQL, ADV_SSMS /INSTANCENAME= [INSTANCE_NAME] /SQMREPORTING=0 /FAILOVERCLUSTERDISKS="Cluster Disk 2" /FAILOVERCLUSTERIPADDRESSES="IPv4; [SERVICE_IP] ;Cluster Network 1;255.255.255.0" /FAILOVERCLUSTERNETWORKNAME= [FCI_NAME] /AGTSVCACCOUNT=" [SQL_AGENT] " /AGTSVCPASSWORD= [PASSWORD] /INSTALLSQLDATADIR="Z:\" /SQLSVCACCOUNT=" [SQL_SERVICE] " /SQLSVCPASSWORD= [PASSWORD] /SQLSYSADMINACCOUNTS="CORP\Install" "CORP\DBAdmin"

  2. Run the following commands to open the firewall for SQL Server connectivity.

    netsh advfirewall firewall add rule name='SQL Server Browser (UDP-In 1434)' dir=in action=allow protocol=UDP localport=1434 profile=domain
    netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL [10_50|11] . [INSTANCE_NAME] \MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP profile=domain

Configure [MACHINE_NAME2]

  1. Login using your installation account.

  2. Change to the directory that contains the setup.exe file for [SQL_Version] , then add this machine to [FCI_NAME] using the following command:

    .\Setup.exe /QS /ACTION=AddNode /IACCEPTSQLSERVERLICENSETERMS /INSTANCENAME= [INSTANCE_NAME] /AGTSVCACCOUNT=" [SQL_AGENT] " /AGTSVCPASSWORD= [PASSWORD] /SQLSVCACCOUNT=" [SQL_SERVICE] " /SQLSVCPASSWORD= [PASSWORD]

  3. Run the following commands to open the firewall for SQL Server connectivity.

    netsh advfirewall firewall add rule name='SQL Server Browser (UDP-In 1434)' dir=in action=allow protocol=UDP localport=1434 profile=domain
    netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL [10_50|11] . [INSTANCE_NAME] \MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP profile=domain

Done!

That it! As you can see, most of the steps are on configuring the Windows, storage, and WSFC cluster prerequisites for the actual FCI installation. Now you can log into the FCI instance using the [FCI_NAME] \ [INSTANCE_NAME] server\instance name from a client such as SQL Server Management Studio. You can now create some databases and tables, failover the FCI in the Failover Cluster Manager, make the disk quorum offline, shut down one of the servers, and observe the availability and behaviors of the database instance. You can also change the quorum model or the number of servers to see how different quorum models respond to server failures.