Category: Database

Script to Install Microsoft Clustering Service

$proxy="http://proxy:8080"
$exclusionList="localhost;*.kimconnect.com"

function checkProxy{
try{
$connectionTest=iwr download.microsoft.com
#$connectionSucceeds=Test-NetConnection -Computername download.microsoft.com -Port 443 -InformationLevel Quiet
if ($connectionTest){
return $True;
}
}
catch{
return $False
}
}

function fixProxy{ 
# Check if proxy is enabled on the system and fix it 
$proxyKey=(Get-ItemProperty -Path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings") 
if ($proxyKey.ProxyEnable){
# Set http proxy for browsers
Set-Itemproperty -path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name 'ProxyServer' -value $proxy

# Set winhttp proxy for PowerShell
netsh winhttp set proxy $proxy $exclusionList

[system.net.webrequest]::defaultwebproxy = New-Object system.net.webproxy($proxy)
[system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 
}

if (checkProxy){
"Proxy is now good to go..."
}
else{
"Proxy problems..."
break;
}
}

if(!(checkProxy)){"Internet issues detected. Fixing now..."; fixProxy;}

# Adding Microsoft Cluster
if (!(get-module -Name "FailoverClusters") ){
Install-WindowsFeature Failover-Clustering | out-null;
Install-WindowsFeature RSAT-Clustering-MGMT | out-null;
Install-WindowsFeature RSAT-Clustering-PowerShell | out-null;
Install-Module SqlServer -Force -Confirm:$False
Import-Module SqlServer
Import-Module FailoverClusters | out-null;
}

Script to Create New Microsoft Cluster

# Variables for New Cluster
$clusterName="SQLCLUSTER007"
$nodes="SQLSHERVER01","SQLSHERVER02"
$clusterIPs="x.x.x.x" # There should be a listener IP on each subnet that a node resides
$clusterQuorum="\\FILESERVER01\CLUSTER02"

Other SQL AlwaysOn High Availability Preparation Steps:
# Add Nodes into Permissions List of Quorum directory
# Create AD Group with the named node computer accounts
# Move nodes into same OU and add the newly created AD group with read-write access to that container
# Connect to each node to install SqlServer and/or SQLPS modules
# Ensure that clustering services are started on each node

# Build Cluster
New-Cluster -Name $clusterName -Node $nodes -StaticAddress $clusterIPs

# Set Quorum
if ($clusterQuorum -ne ""){
Set-ClusterQuorum -NodeAndFileShareMajority $clusterQuorum
}

# Connect to AD to sync DNS records
#repadmin /syncall on DC2

# Update DNS with the current Live IP of AlwaysOn Listener
#Get-ClusterResource $listenerAlias | Update-ClusterNetworkNameResource

# Check DNS Record to verify the correct Active IP
#(Resolve-DnsName $listener).IPAddress

# Force DNS Replication (SysAdmin)
#repadmin /syncall /Aed

Script to Create Cluster (Duplicate)

$proxy="http://proxy:8080"
$exclusionList="localhost;*.kimconnect.com"

function checkProxy{
    try{
        $connectionTest=iwr download.microsoft.com
        #$connectionSucceeds=Test-NetConnection -Computername download.microsoft.com -Port 443 -InformationLevel Quiet
        if ($connectionTest){
            return $True;
        }
    }
    catch{
        return $False
    }
}

function fixProxy{   
    # Check if proxy is enabled on the system and fix it   
    $proxyKey=(Get-ItemProperty -Path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings")        
    if ($proxyKey.ProxyEnable){
        # Set http proxy for browsers
        Set-Itemproperty -path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name 'ProxyServer' -value $proxy            

        # Set winhttp proxy for PowerShell
        netsh winhttp set proxy $proxy $exclusionList

        [system.net.webrequest]::defaultwebproxy = New-Object system.net.webproxy($proxy)
        [system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
        [system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 
        } 

    if (checkProxy){
        "Proxy is now good to go..."
    }
    else{
        "Proxy problems..."
        break;
    }
}

if(!(checkProxy)){"Internet issues detected. Fixing now..."; fixProxy;}

# Adding Microsoft Cluster
if (!(get-module -Name "FailoverClusters") ){
    Install-WindowsFeature Failover-Clustering | out-null;
    Install-WindowsFeature RSAT-Clustering-MGMT | out-null;
    Install-WindowsFeature RSAT-Clustering-PowerShell | out-null;
    Install-Module SqlServer -Force -Confirm:$False
    Import-Module SqlServer
    Import-Module FailoverClusters | out-null;
    }


# Variables for New Cluster
$clusterName="SQLCLUSTER007"
$nodes="SQLSERVER01","SQLSERVER02"
$clusterIPs="x.x.x.x"  # There should be a listener IP on each subnet that a node resides
$fileShareWitness=""
$domainExpression='dc=kimconnect,dc=com'

# Preparation Steps
# Add Nodes into Permissions List of Quorum directory
# Create AD Group with the named node computer accounts
# Move nodes into same OU and add the newly created AD group with read-write access to that container
# Connect to each node to install SqlServer and/or SQLPS modules
# Ensure that clustering services are started on each node

# Build Cluster
New-Cluster -Name $clusterName -Node $nodes -StaticAddress $clusterIPs

# Set Quorum
if ($fileShareWitness -ne ""){
    Set-ClusterQuorum -NodeAndFileShareMajority $fileShareWitness
}

# Connect to AD to sync DNS records
#repadmin /syncall on DC2

# Other commands...

# Update DNS with the current Live IP of AlwaysOn Listener
#Get-ClusterResource $listenerAlias | Update-ClusterNetworkNameResource

# Check DNS Record to verify the correct Active IP
#(Resolve-DnsName $listener).IPAddress

# Force DNS Replication (SysAdmin)
# repadmin /syncall /Aed
# repadmin /syncall %dc% /d /e /a /A /P
# repadmin /syncall dst-dc01 dc=kinecta,dc=org /d /e /a
# Repadmin /replicate $Destination $Source $domainExpression
# Adding Active Directory Module
if (!(get-module -name "ActiveDirectory") ){
    Add-WindowsFeature RSAT-AD-PowerShell | out-null;
    import-module -name "ActiveDirectory" -DisableNameChecking | out-null;
    }
$dcs=(Get-ADDomainController -Filter *).Name
$domainName=(get-addomain).DNSRoot
function Replicate-AllDomainController {
    $dcs | Foreach-Object {repadmin /syncall $_ (Get-ADDomain).DistinguishedName /e /A | Out-Null};
    Start-Sleep 10;
    # Check last replication success of each target server
    Get-ADReplicationPartnerMetadata -Target $domainName -Scope Domain | Select-Object Server, LastReplicationSuccess;
    }

Script to Administer SQL Clusters

<#  Sypnosis
    What it does:
    - Collects all FailOver Clusters in the current domain
    - Lists all nodes of a selected cluster
    - Asks user whether to change Primary node in a particular cluster
    - Changes Primary node basing on user input

    Current Limitations:
    - Cannot run outside of a server with FailOver Clusters being set
    - Dependent on WinRM being available on remote machines
    - Does not build cluster - that's a function in future development
#>

$proxy="http://proxy:8080"
$exclusionList="localhost;*.kimconnect.com"

# Dynamic Credential
$who = whoami
	if ($who.substring($who.length-2, 2) -eq "-admin"){$username=$who;}
    else {$username=$who+"-admin";}
$password = Read-Host -Prompt "Input the password for account $username" -AsSecureString
#$password=convertto-securestring "PASSWORD" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username,$password

# Show cluster resources and owners
#get-clustergroup

# Initialize variables
$clusters=""
$nodes=""
$list=""
$pickIndex=""
$pickedItem=""
$pickedCluster=""

# Ensure that script is ran in the context of an Administrator
$myWindowsID=[System.Security.Principal.WindowsIdentity]::GetCurrent()
$myWindowsPrincipal=new-object System.Security.Principal.WindowsPrincipal($myWindowsID)
 
# Get the security principal for the Administrator role
$adminRole=[System.Security.Principal.WindowsBuiltInRole]::Administrator
 
# Check to see if we are currently running "as Administrator"
if ($myWindowsPrincipal.IsInRole($adminRole))
   {
   # We are running "as Administrator" - so change the title and background color to indicate this
   $Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + "(Elevated)"
   $Host.UI.RawUI.BackgroundColor = "Black"
   clear-host
   }
else
   {
   # We are not running "as Administrator" - so relaunch as administrator
   
   # Create a new process object that starts PowerShell
   $newProcess = new-object System.Diagnostics.ProcessStartInfo "PowerShell";
   
   # Specify the current script path and name as a parameter
   $newProcess.Arguments = $myInvocation.MyCommand.Definition;
   
   # Indicate that the process should be elevated
   $newProcess.Verb = "runas";
   
   # Start the new process
   [System.Diagnostics.Process]::Start($newProcess);
   
   # Exit from the current, unelevated, process
   exit
   }

   function checkProxy{
    try{
        $connectionTest=iwr download.microsoft.com
        #$connectionSucceeds=Test-NetConnection -Computername download.microsoft.com -Port 443 -InformationLevel Quiet
        if ($connectionTest){
            return $True;
        }
    }
    catch{
        return $False
    }
}

function fixProxy{   
    # Check if proxy is enabled on the system and fix it   
    $proxyKey=(Get-ItemProperty -Path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings")        
    if ($proxyKey.ProxyEnable){
        # Set http proxy for browsers
        Set-Itemproperty -path "Registry::HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name 'ProxyServer' -value $proxy            

        # Set winhttp proxy for PowerShell
        netsh winhttp set proxy $proxy $exclusionList

        [system.net.webrequest]::defaultwebproxy = New-Object system.net.webproxy($proxy)
        [system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
        [system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 
        } 

    if (checkProxy){
        "Proxy is now good to go..."
    }
    else{
        "Proxy problems..."
        break;
    }
}

if(!(checkProxy)){"Internet issues detected. Fixing now..."; fixProxy;}

# Set PowerShell Gallery as Trusted to bypass prompts
$trustPSGallery=(Get-psrepository -Name 'PSGallery').InstallationPolicy
If($trustPSGallery -ne 'Trusted'){
    Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted
    }

# PowerShell Execution Policy
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Confirm:$false -Force

# Install PowerShellGet
#Install-Module PowerShellGet -Force

# Adding Prerequisite Active Directory Module
if (!(get-module -name "ActiveDirectory") ){
    Add-WindowsFeature RSAT-AD-PowerShell | out-null;
    import-module -name "ActiveDirectory" -DisableNameChecking | out-null;
    }
$domain=get-addomain
cls

# Adding Microsoft Cluster
if (!(get-module -Name "FailoverClusters") ){
    Install-WindowsFeature Failover-Clustering | out-null;
    Install-WindowsFeature RSAT-Clustering-MGMT | out-null;
    Install-WindowsFeature RSAT-Clustering-PowerShell | out-null;
    Install-Module SqlServer -Force -Confirm:$False
    Import-Module SqlServer
    Import-Module FailoverClusters | out-null;
    }
    
function listClusters{
    $domainName=($domain.DNSRoot).toUpper()
    "`n`nListing all Clusters in $domainName...`n"
    $global:clusters=get-cluster -domain $domain
    #$global:headLine="Available clusters in $domain`:"
}

function listNodes{
    "`n`nListing all Nodes in this Cluster...`n"
    $global:nodes=(Get-ClusterNode -Cluster $pickedItem).Name
    $global:pickedCluster=$pickedItem
    #$global:headLine="Available nodes in $pickedItem`:"
}

function show{
    Param($callFunction)
    invoke-command (Get-Item "function:$callFunction").ScriptBlock    
       
    if ($nodes){$count=$nodes.count; $global:list=$nodes}
    else {$count=$clusters.count; $global:list=$clusters; $global:nodes=""}
    
    $show="`n--------------------------------------------------------`nThere are $count items on this list:`n--------------------------------------------------------`n"
    #$show="`n--------------------------------------------------------`n$headLine`n--------------------------------------------------------`n"
        for ($row=0;$row -le $count-1;$row++){
            $server=$list[$row]
            $show += "$row"  + ": " + "$server" + "`n"
            }        
    return $show
}

function pickItem{
    do {
        try {
            $flag = $true
            [int]$pick=Read-Host -Prompt "`n--------------------------------------------------------`nPlease type the number corresponding to the desired item`n--------------------------------------------------------"
            } # end try
        catch {$flag = $false}
        } # end do 
    until ($pick -lt $list.count -and $flag)
    
    $global:pickIndex=$pick
    $global:pickedItem=$list[$pick]  
    $clusters=""
    $nodes=""
    "$pick`: $pickedItem is picked"    
}

function getPrimary{
    
    # Pick Cluster
    do {
        try{
            $flag1=$true
            show -callFunction listClusters
            pickItem
            }
        catch{$flag1=$fase}
        }
    until ($flag1)

    # Pick Node
    do {
        try{
            $flag2=$true
            show -callFunction listNodes
            pickItem
            }
        catch{$flag2=$fase}
        }
    until ($flag2)
}

# return true or false as determinant on whether program proceeds
function allNodesReachable{
    $winRMPort=5985
    $allNodesReachable=$True
    $nodes | foreach {
    if (!(Test-NetConnection -ComputerName $_ -Port $winRMPort).TcpTestSucceeded){$allNodesReachable = $False;break;}
    }
    return $allNodesReachable
}

function sqlClusterDiscovery{
    $global:targetNode=$nodes[$pickIndex]
    #$global:targetNode=$target

    # Connect to the target node to retrieve SQL Clustering Information
    $global:sqlClusterNameStatus=$False
    $global:sqlClusterName=""
    $global:sqlClusterOwner=""
    $global:sqlClusterListener=""
    $global:sqlClusterListenerAlias=""
    $global:sqlClusterTargetSynchronized=$False
    $global:victim='None'
    
    $sqlClusterDiscovery=Invoke-Command -Credential $cred -ComputerName $targetNode -ScriptBlock{
        $invoke = @{ComputerName=$env:COMPUTERNAME}
        $noError=$False
        $theTarget=$Args
        
        try{
            if ( !(get-module -name "FailoverClusters" ) ) {Import-Module FailoverClusters -DisableNameChecking | out-null;}
            $clusterName=(get-clusterresource).Name[3]
            #$clusterName=(ls SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups).Name #Alternative method
            $owner=(get-clusterresource | Select -ExpandProperty OwnerNode).Name[3]
            #owner=(ls SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups).PrimaryReplicaServerName #Alternative method
            $listener=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].Value
            $listenerAlias=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].ClusterObject.Name

            # Check for Synch status
            if ( !(get-module -name "SQLPS" ) ) {import-module -name "SQLPS" -DisableNameChecking | out-null;}
            $nodeObject=Get-Item SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$clusterName\AvailabilityReplicas\$theTarget
            $targetNodeSynchronized=$nodeObject.RollupSynchronizationState -like 'Synchronized'            
            $noError=$True
            }
        catch{
            $noError=$False
            }

        # Object is to be returned from remote to local PowerShell session
        if ($noError){
            $invoke.Add('sqlClusterStatus',$True)
            $invoke.Add('sqlClusterName',$clusterName)
            $invoke.Add('sqlClusterOwner',$owner)
            $invoke.Add('sqlClusterListener',$listener)
            $invoke.Add('sqlClusterListenerAlias',$listenerAlias)
            $invoke.Add('sqlClusterTargetSynchronized',$targetNodeSynchronized)
        }
        else{
            $invoke.Add('sqlClusterStatus',$False)
            $invoke.Add('sqlClusterName',$clusterName)
            $invoke.Add('sqlClusterOwner',$owner)
            $invoke.Add('sqlClusterListener',$listener)
            $invoke.Add('sqlClusterListenerAlias',$listenerAlias)
            $invoke.Add('sqlClusterTargetSynchronized',$targetNodeSynchronized)
        }
        New-Object -Type PSObject -Prop $invoke
        } -Args $targetNode #End sqlClusterDiscovery Invoke-Command
        
    $global:sqlClusterNameStatus = $sqlClusterDiscovery.sqlClusterStatus
    $global:sqlClusterName = $sqlClusterDiscovery.sqlClusterName
    $global:sqlClusterOwner = $sqlClusterDiscovery.sqlClusterOwner
    $global:sqlClusterListener = $sqlClusterDiscovery.sqlClusterListener
    $global:sqlClusterListenerAlias = $sqlClusterDiscovery.sqlClusterListenerAlias
    $global:sqlClusterTargetSynchronized = $sqlClusterDiscovery.sqlClusterTargetSynchronized
    
    # Pick a Potential Victim: a Synchronized Node that is not a Cluster Owner
    if ($sqlClusterTargetSynchronized -eq $False){
            $nodes | foreach {
                if ($_ -ne $sqlClusterOwner -and $_ -ne $targetNode){
                    $potentialVictim = Invoke-Command -Credential $cred -ComputerName $_ -ScriptBlock{
                        $thisNode=$env:COMPUTERNAME                        
                        $invoke = @{ComputerName=$env:COMPUTERNAME}
                        "Checking $thisNode..."

                        if ( !(get-module -name "SQLPS" ) ) {import-module -name "SQLPS" -DisableNameChecking | out-null;}
                        $clusterName=(ls SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups).Name
                        $nodeObject=Get-Item SQLSERVER:\Sql\$thisNode\default\AvailabilityGroups\$clusterName\AvailabilityReplicas\$thisNode
                        $thisNodeSynchronized=$nodeObject.RollupSynchronizationState -like 'Synchronized'

                        if ($thisNodeSynchronized){
                            "Victim shall be $thisNode";
                            $invoke.Add('Victim',$thisNode);
                            }
                        New-Object -Type PSObject -Prop $invoke
                        } # End potentialVictim Invoke-command   
                        
                        $global:victim=$potentialVictim.Victim
                        #if ($victim -ne 'None'){continue;}
                        #else{"`nThe chosen node $targetNode's SQL databases are NOT Synchronized, but no other node has been found to transfer this ability to $targetNode. Failover can only proceed with a potential loss of data.`n";}
                                             
                    } #Endif nodes loop
                } #End foreach
        } #End if not synchronized

        Write-host "`nCluster $pickedCluster Discovery Results:`n---------------------------------------------------------------------------------------`n"
        Write-host "targetNode: $targetNode"
        Write-host "sqlClusterTargetSynchronized: $sqlClusterTargetSynchronized"
        Write-host "sqlClusterName: $sqlClusterName"
        Write-host "sqlClusterNameStatus: $sqlClusterNameStatus"
        Write-host "sqlClusterOwner: $sqlClusterOwner"
        Write-host "sqlClusterListener: $sqlClusterListener"
        Write-host "sqlClusterListenerAlias: $sqlClusterListenerAlias"
        Write-host "victim (if any): $victim"
}

function seizeSyncMode{
    <#
    This function ensures that the target node can be set with Asynchronous Commit mode; otherwise, program cannot safely executes without potential data loss.
    - list all nodes
    - connect to each node to query whether it's primary or secondary and whether it's set with synchronous commit mode
    - if target node is a secondary with asynchronous commit mode, connect to another secondary and change that to asynchronous mode so that 
        the target node can be set with synchronous commit mode
    #>
    
    try{
    # Step 1: connect to cluster owner switch synch modes of secondary replicas
    Invoke-Command -Credential $cred -ComputerName $sqlClusterOwner -ScriptBlock{
        "`nConnected to $env:computername...`n"
        $theCluster=$Args[0]
        $theVictim=$Args[1]
        $theChosen=$Args[2]
        $thisComputer=$env:computername

        if ( !(get-module -name "SQLPS" ) ) {"SQLPS not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null }

        "`nNow setting $theVictim replica with Asynchronous Manual Mode...`n" 
        Set-SqlAvailabilityReplica -AvailabilityMode "ASynchronousCommit" -FailoverMode "Manual" -Path SQLSERVER:\Sql\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theVictim
        
        "`nNow setting $theChosen replica with Synchronous Commit mode...`n"
        Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Path SQLSERVER:\Sql\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theChosen
        
        <#
        # Check replica synchronization
        $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()
	            }
            }
        #>
                
        "`nExiting $env:computername...`n"
        } -Args $sqlClusterName,$victim,$targetNode    
        return $True;
    }
    catch{return $False;}
}   

function removeReplica($item){
    
    # Connect to cluster owner switch synch modes of secondary replicas
    Invoke-Command -Credential $cred -ComputerName $sqlClusterOwner -ScriptBlock{ 
        "`nConnected to $env:computername..."
        $theCluster=$Args[0]
        $theReplica=$Args[1]
        $thisComputer=$env:computername 
        
        if ( !(get-module -name "SQLPS" ) ) {"SQLPS not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null } 
              
        # Removing replica membership
        Remove-SqlAvailabilityReplica -Path SQLSERVER:\SQL\$thisComputer\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$theReplica
        write-host "$theReplica has now been removed from $theCluster."

    }  -Args $sqlClusterName,$item
}

function addReplica($item,$synchMode){
    # synchMode value shall either equal "1":Synchronous or "2":ASynchronous

    # Step 1: Connect to Cluster Owner to Add Replica
    Invoke-Command -Credential $cred -ComputerName $sqlClusterOwner -ScriptBlock{ 
        "`nConnected to $env:computername..."
        $theCluster=$Args[0]
        $theReplica=$Args[1]
        $thisComputer=$env:computername
        $synchType=$Args[2]
        
        if ( !(get-module -name "SQLPS" ) ) {"SQLPS module not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null }            
        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 }
        
        # Add secondary replica into cluster
        $domain=(get-addomain).DNSRoot
        $agPath = "SQLSERVER:\SQL\$thisComputer\default\AvailabilityGroups\$theCluster"  
        $endpointURL = "TCP://$theReplica.$domain`:5022" 
        $availabilityMode = if($synchType -eq 1){"SynchronousCommit"}else{"ASynchronousCommit"}
        if ($availabilityMode -eq "SynchronousCommit"){$failoverMode = "Automatic"}else{$failoverMode = "Manual"}
        $secondaryReadMode = "AllowAllConnections"
        New-SqlAvailabilityReplica -Name $theReplica -EndpointUrl $endpointURL -FailoverMode $failoverMode -AvailabilityMode $availabilityMode -ConnectionModeInSecondaryRole $secondaryReadMode -Path $agPath

        #$databases=Get-ChildItem SQLSERVER:\SQL\$env:computername\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
        #$databases | foreach {Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$env:computername\default\AvailabilityGroups\$theCluster" -Database $_}    
        
    }  -Args $sqlClusterName,$item,$synchMode
    
    # Step 2: Connect to new secondary replica to (a) join it to Availability Group (b) Join Databases to Availability Group
    Invoke-Command -Credential $cred -ComputerName $item -ScriptBlock{ 
        "`nConnected to $env:computername..."
        $theCluster=$Args[0]
        $theReplica=$Args[1]
        $thisComputer=$env:computername 
        
        if ( !(get-module -name "SQLPS" ) ) {"SQLPS module not in shell environment. Now importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null }            

        # Part (a): join node into availability group
        "Joining $theReplica to cluster $theCluster..."
        Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\$thisComputer\default -Name $theCluster

        # Part (b): join databases into availability group
        "Joining databases to cluster $theCluster..."
        $databases=Get-ChildItem SQLSERVER:\SQL\$theReplica\default\AvailabilityGroups\$theCluster\AvailabilityDatabases
        $databases | foreach {Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$theReplica\default\AvailabilityGroups\$theCluster\AvailabilityDatabases -Database $_}

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

        <#
        # Check Synchronization state
        $newSync = Get-Item SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$theCluster\AvailabilityReplicas\$env:computername
        $notSynchronized=$newSync.RollupSynchronizationState -notlike 'Synchronized'
        "`nDatabases are currently synchronized: $notSynchronized`n"
        if ($notSynchronized){
            Write-Host -NoNewline "Waiting for synchronization to complete..." 
            $dots=50
            while ($notSynchronized) {
	            $dots-=1;
                if ($dots -eq 0){Write-Host ".";$dots=92;}
                else {Write-Host -NoNewline "."}
	            Start-Sleep -s 2
	            $newSync.Refresh()
	            }
        #>

    }  -Args $sqlClusterName,$item

}

function setPrimary($index){
    $tries=3;

    # Check Point (rudimentary method)
    "`n`n`n`n{0} function is invoked!" -f $MyInvocation.MyCommand
    "`nPlease confirm that you want to set node $targetNode as Primary for cluster $pickedCluster"
    do {
        try {            
            [string]$confirm=Read-Host -Prompt "`nType in 'YES' to confirm! you have $tries attempt(s) left. Press Ctrl + C to abort!"
            $tries -= 1;
            } # end try
        catch {$confirm = $false}
        } # end do 
    until ($tries -eq 0 -OR $confirm -eq "YES")    
    
    if ($confirm){
    "`nConfirmation received!`n"
         
    "Please Wait while the program executes..." 
     
$primaryInvoke = Invoke-Command -Credential $cred -ComputerName $targetNode -ScriptBlock {
    $invoke = @{ComputerName=$env:COMPUTERNAME}
    $noError=$True
    $hostname=hostname
    "Now setting $hostname as Primary Failover Node..."  

    $theTarget=$Args[0]

    if ($hostname -eq $theTarget){        
        if ( !(get-module -name "SQLPS" ) ) {"SQLPS Module is currently not installed on $hostname. Importing..."; import-module -name "SQLPS" -DisableNameChecking | out-null;}
        $cluster=(get-clusterresource).Name[3]

        # Checking synchronization state with the primary replica before proceeding to Manual Failover and avoiding data loss
        Write-Host -NoNewline "Checking Synchronization status..."
        $newPrimary = Get-Item SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget
        $notSynchronized=$newPrimary.RollupSynchronizationState -notlike 'Synchronized'
        #$notHealthy=(Test-SqlAvailabilityReplica -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget).HealthState -notlike 'Healthy';

        if ($notSynchronized) {
            try{
                "Attempting to switch this replica, $theTarget, to 'Synchronous Commit' mode..." 
                # Set availability mode as Synchronous commit
                Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget
                Write-Host -NoNewline "Waiting for synchronization to complete..."
                while ($notSynchronized) {
	                Write-Host  -NoNewline "."
	                Start-Sleep -s 2
	                $NewPrimary.Refresh()
	                }
            }
            catch{
                Write-Host "This node cannot become synchronized with the Primary Node.`nFailover cannot proceed without data loss."
                $noError=$False
                return $_ # Break out of remote shell session
                }
        } # End if Not Synchronized
        if (!($notSynchronized)) {    # Execute only if Replica has been synchronized!
            try{

                # Move cluster group primary role to a specific node
                if ( !(get-module -name "FailoverClusters" ) ) {"FailoverClusters Module is currently not installed on $hostname. Importing..."; Import-Module FailoverClusters -DisableNameChecking | out-null;}
                Move-ClusterGroup “Cluster Group” -node $theTarget

                # Setting Target Node as Primary Node
                $databases= Get-ChildItem  "SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases"  
                $databases |   Suspend-SqlAvailabilityDatabase
                Switch-SqlAvailabilityGroup -Path SQLSERVER:\sql\$env:computername\default\availabilitygroups\$cluster #-AllowDataLoss        

                # These lines will only run on the SQL AlwaysOn HA Primary Node    
                # "Switching this Primary Node $theTarget to 'Synchronous Commit' mode..."
                Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityReplicas\$theTarget
                if ($notSynchronized) {
                    Write-Host -NoNewline "Waiting for node status to reach full synchronization..."
                    while ($notSynchronized) {
	                    Write-Host  -NoNewline "."
	                    Start-Sleep -s 2
	                    $NewPrimary.Refresh()
	                    }
                    "$theTarget node has been synchronized."
                    } # end if not Synchronized
                    else{
                        "$theTarget node is already synchronized."
                    }

                $databases |  Resume-SqlAvailabilityDatabase

                "Updating listener IP Address with DNS..."    
                $listener=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].Value
                $listenerAlias=(Get-ClusterResource | Get-ClusterParameter | Where-Object {$_.Name -eq "name"})[1].ClusterObject.Name
                Get-ClusterResource $listenerAlias | Update-ClusterNetworkNameResource
                # Resolve-DnsName $listener            
                } # end try
            catch{
                "Error has occured in the Setting Target Node as Primary Node."
                $noError=$False
                }
            } # End if synchronized            

    } # End if hostname = theTarget
    else{
    "There appears to be a mismatch between this node's hostname and the selected target.`nProgram shall not execute!"
    $noError=$False
    }

    # This variable determines whether program would continue to set other nodes to resume database movements
    # Object is to be returned from remote to local PowerShell session
    if ($noError){
    $invoke.Add('Status',$True)
    }
    else{
    $invoke.Add('Status',$False)       
    }
    New-Object -Type PSObject -Prop $invoke

} -Args $targetNode,$pickedCluster
    

"Now setting the other Nodes to resume Database movements..."
if ($primaryInvoke.Status){    $nodes | foreach {
        if ($_ -ne $targetNode){
        Invoke-Command -Credential $cred -ComputerName $_ -ScriptBlock{
            hostname
            if ( !(get-module -name "SQLPS" ) ) { import-module -name "SQLPS" -DisableNameChecking | out-null } 
            $cluster=(get-clusterresource).Name[3]
            $databases= Get-ChildItem  "SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups\$cluster\AvailabilityDatabases"
            $databases |  Resume-SqlAvailabilityDatabase
            # Join-SqlAvailabilityGroup -Path SQLSERVER:\Sql\$env:computername\default\AvailabilityGroups -Name $cluster
            }
        } -Args $targetNode,$pickedCluster
    }
}
    # "Done!`n`nProcess is completed to set $targetNode as the Primary Node."        
    } # endif confirm
#>
}

getPrimary
if (allNodesReachable){sqlClusterDiscovery}
if($victim -ne 'None' -and $victim -ne $null){if(seizeSyncMode){setPrimary $pickIndex}}