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:
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 .
First things first:
If you want to implement an FCI in an existing domain over your physical network, then you can run three VMs (one of which being the file server) with enough memory for both host and guests, about 2GB a piece. However, you should take a look at the caveat in Configure Windows Failover Clustering for SQL Server (Availability Group or FCI) with Limited... . If you want to implement an FCI in its own virtual network, you can squeeze another domain controller (DC) with DHCP service into the mix, running on 1GB of memory.
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.
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] :
You have now created the shared disk that will be used as the quorum disk for the WSFC cluster.
Perform the following on the two machines that will host [FCI_NAME] :
Import-Module Servermanager
Add-WindowsFeature Failover-Clustering
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:
.\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"
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
.\Setup.exe /QS /ACTION=AddNode /IACCEPTSQLSERVERLICENSETERMS /INSTANCENAME= [INSTANCE_NAME] /AGTSVCACCOUNT=" [SQL_AGENT] " /AGTSVCPASSWORD= [PASSWORD] /SQLSVCACCOUNT=" [SQL_SERVICE] " /SQLSVCPASSWORD= [PASSWORD]
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.