PowerShell: Microsoft SQL Administration

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}}

Leave a Reply

Your email address will not be published. Required fields are marked *