The official SQL Server AlwaysOn team blog.
Writer: Cephas Lin (Microsoft)
Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)
NOTE: SQL Server Failover Clustering has been renamed AlwaysOn Failover Cluster Instances (FCI) in SQL Server 2012. For simplicity, the term FCI in this paper applies to either SQL Server Failover Clustering in SQL Server 2008 R2 or AlwaysOn FCI in SQL Server 2012.
This blog is the continuation of a blog series that shows you how to perform the following end-to-end HADR migration scenario of SQL Server inside a test environment:
In Part 1, you created the base configuration. In this installment, you will apply the migration steps to the base configuration you created. Without further delay, let's start!
Role: Database Administrator
First, you create a temporary FCI in 10.1.1.X/24. Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy FCI3 in 10.1.1.X/24 using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.
WSFC Cluster Name
SQL Server Version
SQL Server 2012
FCI Network Name
File Server Name
Cluster Service IP Address
SQL Service Account
SQL Agent Account
Figure 5 shows the details of FCI3 after you create it, as viewed in Failover Cluster Manager. Similar to FCI1 and FCI2, FCI3 has a quorum model of Node and Disk Majority.
Figure 5 FCI3 as Shown in Failover Cluster Manager
FCI3 is used in this walkthrough as a temporary replacement FCI to preserve the two-FCI configuration when you are reconfiguring FCI1 or FCI2. You can perform migration without the temporary FCI and still preserve the high availability of your databases, but note that when FCI1 or FCI2 is being reconfigured or upgraded using this alternate approach, the high availability of your database instance rests upon the single remaining FCI.
In addition, FCI3 is used to set up the availability group in the SQL Server AlwaysOn solution, and the WSFC cluster used to host FCI3 (Cluster3) will become a permanent fixture in the final configuration.
In this section, you will remove the existing database mirroring session between FCI1\LIVE_INST and FCI2\LIVE_INST and configure a new database mirroring session between FCI1\LIVE_INST and FCI3\TEMP_INST. These actions effectively remove FCI2 from the HADR solution so that it can be reconfigured, while still maintaining two FCIs in your HADR database system. However, removing FCI2 also means that the system does not have any disaster recovery capability until FCI2 is reintroduced to your HADR system.
Perform the following steps while connected to FCI1\LIVE_INST and FCI3\TEMP_INST.
Primary Service Account
Mirror Service Account
At this point, FCI2 is no longer being used, and it can be reconfigured to be part of Cluster3.
Next, you reconfigure FCI2 by reinstalling Windows Server and SQL Server completely on the FCI2 machines. You cannot simply upgrade SQL Server 2012 on the existing configuration for FCI2 for the following reasons:
While you can manually delete both the FCI2 clustered service, the Cluster2 cluster, and the existing SQL Server installation, it can add unnecessary complication and troubleshooting to your migration process. Unless you have a compelling reason to preserve the existing Windows installation, it is simpler just to reinstall Windows.
Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy FCI2 using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.
Due to the reasons described previously, make sure you use the steps following the table to join machines to Cluster3.
*Instead of recreating Cluster2, you join the machines to the new cluster Cluster3. Follow the instructions below:
Get-Cluster Cluster3 | Add-ClusterNode REMOTE1,REMOTE2
Move-ClusterGroup "Available Storage" -Node REMOTE1
Get-ClusterAvailableDisk | Add-ClusterDisk
After the Add-ClusterNode command runs, ignore the errors for the three missing SQL Server DLLs. These DLLs are added when you install FCI2 on the nodes.
Note: The Move-ClusterGroup command moves the Available Storage resource group to REMOTE1. This resource group holds all newly added shared storage. Unless either REMOTE1 or REMOTE2 owns this resource group, you cannot bring the shared disk online after you add it to the cluster because it cannot be brought online on any other node in the cluster.
Get-ClusterGroup "SQL Server (TEMP_INST)" | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
Get-ClusterGroup "SQL Server (TEMP_INST)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
(Get-ClusterNode "REMOTE1").NodeWeight = 0
(Get-ClusterNode "REMOTE2").NodeWeight = 0
Your Cluster3 quorum should now look as follows:
IMPORTANT: You should reconsider your quorum model carefully to maintain optimal WSFC quorum health both when and after adding the nodes. For more information, see the WSFC Quorum Modes and Voting Configuration section in Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.
.\Setup.exe /QS /ACTION=InstallFailoverCluster /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQL, ADV_SSMS /INSTANCENAME=LIVE_INST2 /SQMREPORTING=0 /FAILOVERCLUSTERDISKS="Cluster Disk 3" /FAILOVERCLUSTERIPADDRESSES="IPv4;10.2.2.222;Cluster Network 2;255.255.255.0" /FAILOVERCLUSTERNETWORKNAME=FCI2 /AGTSVCACCOUNT="CORP\SQLAgent2" /AGTSVCPASSWORD=[PASSWORD] /INSTALLSQLDATADIR="[SHAREDDISK_DRIVELETTER]:\" /SQLSVCACCOUNT="CORP\SQLSvc2" /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\MSSQL11.LIVE_INST2\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP profile=domain
Get-ClusterGroup "SQL Server (LIVE_INST2)" | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
Get-ClusterGroup "SQL Server (LIVE_INST2)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
Get-ClusterGroup "Cluster Group" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2,REMOTE1,REMOTE2
The resource group "Cluster Group" contains cluster-level resources for Cluster3, including the quorum disk. The last command ensures that that all nodes have access to the quorum disk.
Once FCI2 has been reconfigured, it should look similar to Figure 6 when viewed in Failover Cluster Manager. Note that Cluster3 now has two cluster networks and two clustered services.
Figure 6 FCI2 after Reconfiguration as Shown in Failover Cluster Manager
In this section, you will perform a failover to make FCI3 the new primary partner and stop database mirroring. Then, you will establish an availability group with the temporary FCI as the primary replica and the remote FCI as the secondary replica. Ultimately, this availability group becomes a permanent fixture in the post-migration solution.
First, you enable the AlwaysOn Availability Groups feature on FCI3 and FCI2. You will perform this step now so that you do not have to restart FCI3 after it becomes the primary FCI. To do this, open a SQL Server PowerShell window from SSMS and run the following commands:
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI3\TEMP_INST -Force
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI2\LIVE_INST2 -Force
Next, you will perform the database mirroring failover to FCI3 and configure the availability group that you will use for the SQL Server AlwaysOn solution.
IMPORTANT: Your client application will experience downtime in this part of the migration process. The duration of the downtime depends on your efficiency, the database size, the speed of file transfer, and other factors.
IMPORTANT: This is the beginning of downtime. The console application on CLIENT should begin to show Error:... for write access failures.
NOTE: While the Full option automatically prepares the secondary replicas by taking the necessary backups, it is not designed for very large databases that may exist in a Tier-1 environment. For more information on the prerequisites for AlwaysOn Availability Groups, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).
Once the availability group configuration is finished, you can monitor the availability group health in SSMS by right-clicking AlwaysOn High Availability and selecting Show Dashboard. The view of the AlwaysOn Dashboard is shown in Figure 7.
Figure 7 AlwaysOn Dashboard after Availability Group Creation
The Failover Cluster Manager view of AG1 is shown in Figure 8. The Cluster3 cluster now contains three clustered services: two FCI services and one availability group service. The WSFC service manages aspects the resource group ownership and monitors the quorum health for the availability group. Note that other than quorum model and health management, you should avoid modifying the clustered service properties of the availability group in the Failover Cluster Manager. For example, do not perform availability group failovers using the Failover Cluster Manager. You can use the AlwaysOn Dashboard to perform this task. For more information, see DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover.
Figure 8 New Availability Group as Shown in Failover Cluster Manager
At this point, CORP\SQLClient still does not have access to the databases on FCI2\LIVE_INST2. If you must fail over to FCI2\LIVE_INST2 due to a failure during or after migration, you must perform the following steps immediately after the failover so that the client applications can recover database connectivity.
Role: Application Developer
Next, you reconfigure your client application to re-establish database connectivity. In a real-life migration scenario, you can proceed with this step as soon as downtime begins so that your client applications can re-establish database connectivity as soon as the availability group is online.
const string connString = @"Server=FCI1\LIVE_INST;Failover Partner=FCI2\LIVE_INST;Initial Catalog=DB1;Integrated Security=True";
const string connString = @"Server=tcp:AG1,10000;Database=DB1;IntegratedSecurity=SSPI";
IMPORTANT: The console application on CLIENT should now show Success:... for write access. This is the end of downtime.
You have now created the availability group, lost and recovered client connectivity, and recovered HADR capabilities. The only remaining tasks are reconfiguring FCI1, add it to the availability group, and remove the temporary FCI. I will cover that in the next installment. Stay tuned!