Move SQL 2017 Always On to New Network Segment

Tutorial on how to Move SQL 2017 Always On to New Network Segment

We are going to change all the IP Address for components used in Setup SQL 2017 Always On with PowerShell

Ensure that network routing is configured properly prior continue the lab below

Migrate the existing FSW to a new Windows 2016 Server

# Create a new share folder with Full Access permission granted to all cluster nodes
$FSW = "C:\SQLFSW"
New-Item -Path $FSW -ItemType "directory"
New-SmbShare -Name "SQLFSW" -Path "C:\SQLFSW" -FullAccess "AVENTISLAB\UAT-SQL01$","AVENTISLAB\UAT-SQL02$","AVENTISLAB\UAT-CLUS$"

# Assign Full Access NTFS Permission to all cluster nodes
$Computers= @('UAT-SQL01$','UAT-SQL02$','UAT-CLUS$')
ForEach ($Computer in $Computers) {

$Acl = (Get-Item $FSW).GetAccessControl('Access')
$Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($user, "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
$Acl.SetAccessRule($Ar)
Set-Acl -path $FSW -AclObject $Acl }

#Move FSW to new UAT-WIN16 
Get-Cluster | Set-ClusterQuorum -FileShareWitness \\UAT-WIN16\SQLFSW

Change the IP Address of UAT-WIN2016 from 192.168.1.22 to 10.10.10.22 and reboot

The File Share Witness cluster resource will show Failed for time being

Get-ClusterResource

Name                   State  OwnerGroup    ResourceType                 
----                   -----  ----------    ------------                 
Cluster IP Address     Online Cluster Group IP Address                   
Cluster Name           Online Cluster Group Network Name                 
File Share Witness (2) Failed Cluster Group File Share Witness           
SQLAG                  Online SQLAG         SQL Server Availability Group
SQLAG_IP               Online SQLAG         IP Address                   
SQLAG_SQLAO            Online SQLAG         Network Name    

Ensure that the UAT-WIN2016 is accessible via the new IP Address prior bringing up the FSW cluster resources

Get-ClusterResource -Name "File Share Witness (2)" | Start-ClusterResource

Name                   State  OwnerGroup    ResourceType      
----                   -----  ----------    ------------      
File Share Witness (2) Online Cluster Group File Share Witness

Move all the active cluster resource to UAT-SQL01

# Verify the existing owner of cluster resource
Get-ClusterGroup

Name              OwnerNode State  
----              --------- -----  
Available Storage UAT-SQL02 Offline
Cluster Group     UAT-SQL02 Online 
SQLAG             UAT-SQL01 Online 

# Move all cluster resources running on UAT-SQL02 to UAT-SQL01 
Move-ClusterGroup -Name "Cluster Group" -Node UAT-SQL01 -Verbose

Change the IP Address of UAT-SQL02 from 192.168.1.62 to 10.10.10.62 and reboot

Ensure that the UAT-SQL02 is accessible via the new IP Address prior verifying the status of the Cluster Nodes

Get-ClusterNode

Name                 ID    State                                                                                                                                                               
----                 --    -----                                                                                                                                                               
UAT-SQL01            1     Up                                                                                                                                                                  
UAT-SQL02            2     Up                         

Change the Cluster IP Address to from 192.168.1.x to 10.10.10.x

Get-ClusterResource -Name "Cluster IP Address" | Set-ClusterParameter -Name Address -Value "10.10.10.65"

WARNING: The properties were stored, but not all changes will take effect until Cluster IP Address is taken offline and then online again.

Get-ClusterResource -Name "SQLAG_IP" | Set-ClusterParameter -Name Address -Value "10.10.10.68"

WARNING: The properties were stored, but not all changes will take effect until SQLAG_IP is taken offline and then online again.

Move the Cluster Group & SQLAG cluster group from UAT-SQL01 to UAT-SQL02

# Move it to UAT-SQL02
Move-ClusterGroup -Name "Cluster Group" -Node UAT-SQL02 -Verbose
Move-ClusterGroup -Name "SQLAG" -Node UAT-SQL02 -Verbose

UAT-SQL02 will become the Primary Node for SQL Always On automatically now

Change the IP Address of UAT-SQL01 from 192.168.1.x to 10.10.10.x and reboot

Verify the Cluster Failover is working fine between both SQL Nodes on new Network Segment

# Move Cluster Group Resource back to UAT-SQL01
Move-ClusterGroup -Name "Cluster Group" -Node UAT-SQL01 -Verbose

Name          OwnerNode State 
----          --------- ----- 
Cluster Group UAT-SQL01 Online

# Move SQLAG Resource back to UAT-SQL01
Move-ClusterGroup -Name "SQLAG" -Node UAT-SQL01

Name  OwnerNode State 
----  --------- ----- 
SQLAG UAT-SQL01 Online

UAT-SQL01 will become the Primary Node for SQL Always On automatically now

Reference Links

1. FailoverClusters