$servers="SQL01","SQL02","SQL03";

# Dynamic Credential method 1
$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

function show{
$show="`n--------------------------------------------------`nThere are $count SQL Servers in this Collection: `n--------------------------------------------------`n"
    for ($row=0;$row -le $servers.count-1;$row++){
        $server=$servers[$row]
        $show += "$row"  + ": " + "$server" + "`n"
        }
return $show
}

function getPrimary{
do {
    try {
        $numOk = $true
        [int]$setPrimary=Read-Host -Prompt "Please type the index number corresponding to the desired SQL Servername to Set as Primary Node"
        } # end try
    catch {$numOK = $false}
    } # end do 
until ($setPrimary -lt $servers.count -and $numOK)
$global:primaryIndex=$setPrimary
}

function setPrimary($index){
    $target=$servers[$index]    
    Invoke-Command -Credential $cred -ComputerName $target -ScriptBlock {
    $hostname=hostname
      
    "Now setting $hostname as Primary Failover Node..."  
    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 |   Suspend-SqlAvailabilityDatabase
    Switch-SqlAvailabilityGroup -Path SQLSERVER:\sql\$env:computername\default\availabilitygroups\$cluster -AllowDataLoss
    $databases |  Resume-SqlAvailabilityDatabase

    "Updating listener IP Address with DNS..." 
    if ( !(get-module -name "FailoverClusters" ) ) {Import-Module FailoverClusters}
    $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
    }

    "Now setting the other Nodes to resume Database movements..."
    $servers | foreach {
        if ($_ -ne $target){
        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
            }
        }
    }
    "Process is completed to set $target as the Primary Node"
}

show
getPrimary
setPrimary $primaryIndex
Categories: