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 2 , you created a temporary FCI3 for primary site workload, reconfigured FCI2, and created an availability group that encompasses FCI3 and FCI2. This availability group contains both DB1 and DB2. In this installment, you will finish the migration steps by reconfiguring FCI1 and returning to the two-FCI configuration that you had pre-migration. So, let's start!
In this section, you reconfigure FCI1 and join it to the availability group.
Role: Database Administrator
First, you reconfigure FCI1 by reinstalling Windows Server and SQL Server completely on the FCI1 machines. For the reasons described in Step 3: Reconfigure FCI2 with SQL Server 2012 in Part 2 , you cannot simply upgrade SQL Server 2012 on the existing configuration for FCI1.
Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V , deploy FCI1 at the primary site using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.
Make sure you use the steps below the table to join machines to Cluster3.
Version |
SQL Server 2012 |
FCI Name |
FCI1 |
Instance Name |
LIVE_INST1 |
Machine Names |
PRIMARY1 PRIMARY2 |
File Server Name |
FILE1 |
WSFC Cluster Name |
Cluster3* |
Cluster Service IP Address |
10.1.1.111 |
SQL Service Account |
CORP\SQLSvc1 |
SQL Agent Account |
CORP\SQLAgent1 |
*Instead of recreating Cluster1 , you join the machines to the new cluster Cluster3 . Follow the instructions below:
Import-Module FailoverClusters
Get-Cluster Cluster3 | Add-ClusterNode PRIMARY1,PRIMARY2
Move-ClusterGroup "Available Storage" -Node PRIMARY1
Get-ClusterAvailableDisk | Add-ClusterDisk
Test-Cluster
After the Add-ClusterNode command runs, ignore the errors for the three missing SQL Server DLLs. These DLLs are added when you install FCI1 on the nodes.
Note: The Move-ClusterGroup command moves the Available Storage resource group to PRIMARY1 . This resource group holds all newly added shared storage. Unless either PRIMARY1 or PRIMARY2 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.
After the nodes are added to the cluster, your WSFC quorum should 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 ... .
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-ClusterGroup "SQL Server (LIVE_INST2)" | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
Get-ClusterGroup "SQL Server (LIVE_INST2)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
.\Setup.exe /QS /ACTION=InstallFailoverCluster /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQL, ADV_SSMS /INSTANCENAME=LIVE_INST1 /SQMREPORTING=0 /FAILOVERCLUSTERDISKS="Cluster Disk 4" /FAILOVERCLUSTERIPADDRESSES="IPv4;10.1.1.111;Cluster Network 1;255.255.255.0" /FAILOVERCLUSTERNETWORKNAME=FCI1 /AGTSVCACCOUNT="CORP\SQLAgent1" /AGTSVCPASSWORD= [PASSWORD] /INSTALLSQLDATADIR=" [SHAREDDISK_DRIVELETTER] :\" /SQLSVCACCOUNT="CORP\SQLSvc1" /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_INST1\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP profile=domain
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-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 "SQL Server (LIVE_INST1)" | Set-ClusterOwnerNode -Owners PRIMARY1,PRIMARY2
Get-ClusterGroup "SQL Server (LIVE_INST1)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners PRIMARY1,PRIMARY2
Get-ClusterGroup "Cluster Group" | Get-ClusterResource | Set-ClusterOwnerNode -Owners PRIMARY1,PRIMARY2,REMOTE1,REMOTE2,TEMP1,TEMP2
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 9 when viewed in Failover Cluster Manager. Note that Cluster3 now has two cluster networks and two clustered services.
Figure 9 FCI1 after Reconfiguration as Shown in Failover Cluster Manager
Role: Database Administrator
First, you enable the AlwaysOn Availability Groups feature on FCI1. To do this, open a SQL Server PowerShell window from SSMS and run the following commands:
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI1\LIVE_INST1 -Force
Role: Database Administrator
Next, you will add the primary site to the availability group.
This setting makes the remote site the preferred secondary replica for backup operations.
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 . It may take some time for FCI1\LIVE_INST1 to become synchronized. Once it is synchronized, the AlwaysOn Dashboard looks similar to Figure 10.
Figure 10 AlwaysOn Dashboard after Addition of FCI1\LIVE_INST1
The last step is to return the availability group to a two-FCI configuration. You will failover the availability group to the FCI1\LIVE_INST1, and then remove FCI3\TEMP_INST from the availability group.
Role: Database Administrator
Perform the following steps while connected to FCI3\TEMP_INST, which is currently hosting the primary replica in the availability group.
The AlwaysOn Dashboard may show error states until failover is complete and the dashboard is refreshed. By default, the dashboard refreshes every 30 seconds. You can refresh it manually by pressing F5.
Important: At this point, CORP\SQLClient still does not have access to the databases on FCI1\LIVE_INST1 . You must perform the following steps immediately after the failover so that the client applications can recover database connectivity.
Perform the following steps while connected to FCI1\LIVE_INST1, which is currently hosting the primary replica in the availability group.
Once the replica has been removed, you will remove the clustered service and the nodes from Cluster3.
IMPORTANT: Before evicting TEMP1 and TEMP2 from the cluster, make sure all nodes have access to the quorum disk. If TEMP1 and TEMP2 are still the only nodes that can access it, the quorum disk is taken offline immediately after you evict the two nodes because no other node is able to take ownership of the quorum disk. You can test this condition by attempting to move the quorum disk between the remaining nodes. For example, to move the quorum disk to PRIMARY1, the following PowerShell command:
Move-ClusterSharedVolume "Cluster Disk 1" -Node PRIMARY1
Your Cluster3 quorum should now look as follows:
After you finish removing the FCI3 nodes from Cluster3, the cluster should look similar to Figure 11. The storage view is shown here to demonstrate that the quorum disk is online and hosted by one of the remaining nodes.
Figure 11 Failover Cluster Manager View after Removal of FCI3
Whew! Three blogs later, you have migrated a SQL Server 2008 R2 HADR solution to a SQL Server AlwaysOn solution. Take a look at the table below for a comparison of the two solutions.
SQL Server 2008 R2 Solution (Pre-Migration) |
SQL Server AlwaysOn Solution (Post-Migration) |
|
Number of WSFC Clusters |
2 |
1 |
Number of FCIs |
2 |
2 |
Number of Cluster Networks |
1 in each cluster for the respective subnet |
2 in the cluster, spanning both subnets |
Number of Cluster Disks |
2 in each cluster (1 for quorum and 1 for FCI) |
3 (1 for quorum and 1 for each FCI) |
Number of Nodes |
2 in each cluster |
4 |
Number of Clustered Services |
1 in each cluster |
3 (1 for each FCI and 1 for availability group) |
Cluster Resource Ownership |
Owned by all nodes in each cluster |
FCI resources owned by respective FCI nodes. Availability group resources owned by all nodes. |
HA Failover Approach |
FCI failover in Failover Cluster Manager |
FCI failover in Failover Cluster Manager |
DR Failover Approach |
Database mirroring failover in SQL Server Management Studio |
Availability group failover in SQL Server Management Studio |
Migrating an HADR solution is not a trivial matter, and clearly, migrating to SQL Server AlwaysOn is not an exemption. Hopefully, I have put you on the right track by helping you avoid many of the potential issues. I want to remind you at this point that the whitepaper: Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from ... , shows you an alternative migration approach without using intermediate hardware. If there is the interest, I can blog about that in a separate series. Whatever path you take to migrate your solution to SQL Server AlwaysOn, be sure to test your particular migration scenario extensively, including rehearsing failover scenarios and disaster recovery scenarios, before performing the migration on your production system.
Also, I have not shown you the new workloads that are possible with your new SQL Server AlwaysOn solution, such as using the read-only secondary replica for read workloads. For more information, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server) .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.