Overview:

0. Add secondary replica to the cluster by running some commands on the availability group owner node
1. Run commands on the target secondary replica to join it to the availability group.
2. For each database in the availability group, create a secondary database on the server instance that is hosting the secondary replica. This process is outside the scope of this document.
3. Join secondary databases into the availability group.
4. Resume data movement from the secondary replica databases

$primaryReplica="SQL01"
$secondaryReplica="SQL02"
$theCluster="CLUSTER01"
if ( !(get-module -name "ActiveDirectory") ) {"ActiveDirectory module not in shell environment. Now importing..."; Add-WindowsFeature RSAT-AD-PowerShell; import-module -name "ActiveDirectory" -DisableNameChecking | out-null }
$domain=(get-addomain).DNSRoot
$syncMode=1 #1 for Auto, 2 for manual

# Remove a secondary replica (by issuing this command on primaryReplica)
# Warning: removing a replica will also remove its associated databases from the cluster
#Remove-SqlAvailabilityReplica -Path SQLSERVER:\SQL\$env:computername\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$secondaryReplica 

# Add secondary replica to cluster by issuing these commands on Primary Replica (using Invoke-Command)
$agPath = "SQLSERVER:\Sql\$primaryReplica\default\AvailabilityGroups\$theCluster"
$endpointURL = "TCP://$secondaryReplica.$domain`:5022"
$availabilityMode = if($syncMode -eq 1){"SynchronousCommit"}else{"ASynchronousCommit"}
if ($availabilityMode -eq "SynchronousCommit"){$failoverMode = "Automatic"}else{$failoverMode = "Manual"} 
$secondaryReadMode = "AllowAllConnections"
New-SqlAvailabilityReplica -Name $secondaryReplica -EndpointUrl $endpointURL -FailoverMode $failoverMode -AvailabilityMode $availabilityMode -ConnectionModeInSecondaryRole $secondaryReadMode -Path $agPath

# Add High Availability Databases into Cluster by running on Primary Replica (using Invoke-Command)
$databases=Get-ChildItem SQLSERVER:\SQL\$primaryReplica\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
$databases | foreach {Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$primaryReplica\default\AvailabilityGroups\$theCluster -Database $_}

<# This information is incorrect --- skip it
# Add secondary replica by issuing these commands on secondaryReplica
# Note that similar commands must be done on both primaryRelica and secondaryReplica
$agPath = "SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$theCluster"  
$endpointURL = "TCP://$env:computername.$domain`:5022"  
$availabilityMode = if($syncMode -eq 1){"SynchronousCommit"}else{"ASynchronousCommit"}
if ($availabilityMode -eq "SynchronousCommit"){$failoverMode = "Automatic"}else{$failoverMode = "Manual"} 
$secondaryReadMode = "AllowAllConnections" 
New-SqlAvailabilityReplica -Name $secondaryReplica -EndpointUrl $endpointURL -FailoverMode $failoverMode -AvailabilityMode $availabilityMode -ConnectionModeInSecondaryRole $secondaryReadMode -Path $agPath
#>

# Join Secondary Replica to Availability Group by issuing this command on the secondaryReplica
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\$env:computername\default -Name $theCluster

# Check replica synchronization (optional)
        $newSync = Get-Item "SQLSERVER:\Sql\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theChosen"
        $synchronized=$newSync.RollupSynchronizationState -like 'Synchronized'
        "`nReplica $theChosen is currently synchronized: $synchronized`n"        

        if (!($synchronized)){
            Write-Host -NoNewline "Waiting for synchronization to complete..." 
            $dots=50
            $timeout=300 #5 minutes
            while (!($synchronized)) {
	            $dots-=1;
                $timeout-=2;
                if($timeout -lt 0){"$timeout seconds have passed. Skip this waiting."; continue;}
                if ($dots -eq 0){Write-Host ".";$dots=92;}
                else {Write-Host -NoNewline "."}
	            Start-Sleep -s 2
	            $newSync.Refresh()
	            }
            }

# Resume Data Movement
$databases= Get-ChildItem SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
#$databases |  Suspend-SqlAvailabilityDatabase
$databases |  Resume-SqlAvailabilityDatabase

-------------------------------------------------------------------------------------------------
<#
This will appear if this replica has already been present in the cluster
Join-SqlAvailabilityGroup : Failed to join local availability replica to availability group 'EDMS_STG_AG'.  The
operation encountered SQL Server error 41106 and has been rolled back.  Check the SQL Server error log for more
details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.
At line:1 char:1
+ Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\$env:computername\default -Name $ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Join-SqlAvailabilityGroup], SqlException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.JoinSqlAvailabilityGroupC
   ommand
#>

# Add High Availability Databases into Cluster by running on Secondary Replica (using Invoke-Command)
$databases=Get-ChildItem SQLSERVER:\SQL\$secondaryReplica\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
$databases | foreach {Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$secondaryReplica\InstanceName\AvailabilityGroups\$theCluster" -Database $_}

<#
Troubleshooting:

Error:
Add-SqlAvailabilityDatabase : Database '' cannot be joined to availability group ''.  The database is
not an availability database on the availability group.

Explanation:
If Add-SqlAvailabilityDatabase command is issued against a database that has not been marked as an availability database (by residing on the appropriate path), then this error reminds the Admin to check the SQL path.

Error:
import-module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any
module directory.
At line:1 char:116
+ ... importing..."; import-module -name "ActiveDirectory" -DisableNameChecking | out- ...
+                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (ActiveDirectory:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

Explanation:
Active Directory module for Windows Powershell is needed on this host. Here's how to install it:
# Adding Prerequisite Active Directory Module
if ( !(get-module -name "ActiveDirectory") ) {"ActiveDirectory module not in shell environment. Now importing..."; Add-WindowsFeature RSAT-AD-PowerShell; import-module -name "ActiveDirectory" -DisableNameChecking | out-null }
#>
Categories: