The SQL Server Availability Groups template in the Azure VM Gallery, for example, creates five VMs in a resource group. Who wants to use the Azure portal in browser to click, wait, x5? And if you try to start/stop too many VMs at a time, they will error out.
PowerShell provides! Since the Start-AzureRMVM and Stop-AzureRMVM cmdlets wait for each VM to finish changing state, it'll take 2-3 minutes per VM, but it's launched in one manual step.
Note, there's also built-in Azure functionality to shut down VMs on a schedule, no coding required. In this case, the use case for this script is to start up my five VMs for a test Availability Group manually, and bring them down when I'm done with my lab. I may also set up the auto-shutdown as insurance that I won't accidentally leave the VMs running.The below script is quite verbose and well-commented, including the Setup block to get the required module and log you into Azure. You could certainly shorten and simplify the script, but my goal here is readability, and repeatability. (If you have a suggestion on how this can be improved, I'm all for it.)
Specify a resource group name in $ResourceGroupName, and a list of VM names in $VMs. Important note here: when you have a cluster of VMs, you may want to have them start up and shut down in a specific order. The below example shuts down the domain controller VMs last, and starts them up first.
#Launch VS Code as Administrator
<#
Invoke-Command -script {Install-WindowsFeature -Name "Failover-Clustering" } `
-ComputerName SQLDEV11, SQLDEV12, SQLDEV14, SQLDEV15
Invoke-Command -script {Install-WindowsFeature -Name "RSAT-Clustering-Mgmt" } `
-ComputerName SQLDEV11, SQLDEV12, SQLDEV14, SQLDEV15
Invoke-Command -script {Install-WindowsFeature -Name "RSAT-Clustering-PowerShell" } `
-ComputerName SQLDEV11, SQLDEV12, SQLDEV14, SQLDEV15
#>
#Install-Module SQLSERVER -Force -AllowCLobber
#Import-Module SQLSERVER
#Must run on the primary node
#TODO: configure initial variable values.
Write-Output "Begin $(Get-Date)"
#Setup: TODO Configure these
$PrimaryReplicaName = "SQLSERVER-0"
$PrimaryReplicaInstanceName = "SQL2K17" #Named instance or DEFAULT for the default instance
$SecondaryReplicaName1 = "SQLSERVER-1"
$SecondaryReplicaInstanceName1 = "SQL2K17" #Named instance or DEFAULT for the default instance
$AvailabilityGroupName = "WWI2017-AG"
#Inventory and test
Get-ChildItem "SQLSERVER:\Sql\$($PrimaryReplicaName)\$($PrimaryReplicaInstanceName)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\" | Test-SqlAvailabilityReplica | Format-Table
$AGPrimaryObjPath = "SQLSERVER:\Sql\$($PrimaryReplicaName)\$($PrimaryReplicaInstanceName)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\$($PrimaryReplicaName+$(IF($PrimaryReplicaInstanceName -ne "DEFAULT"){$("%5C")+$PrimaryReplicaInstanceName} ))"
$AGPrimaryObj = Get-Item $AGPrimaryObjPath
$AGSecondaryObjPath = "SQLSERVER:\Sql\$($PrimaryReplicaName)\$($PrimaryReplicaInstanceName)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\$($SecondaryReplicaName1+$(IF($SecondaryReplicaInstanceName1 -ne "DEFAULT"){$("%5C")+$SecondaryReplicaInstanceName1} ))"
$AGSecondaryObj = Get-Item $AGSecondaryObjPath
#Set replicas to synchronous before planned failover
Set-SqlAvailabilityReplica `
-Path $AGPrimaryObjPath `
-AvailabilityMode SynchronousCommit `
-FailoverMode "Manual" `
-ErrorAction Stop
Set-SqlAvailabilityReplica `
-Path $AGSecondaryObjPath `
-AvailabilityMode SynchronousCommit `
-FailoverMode "Manual" `
-ErrorAction Stop
#Check for when replicas are synchronized.
Do {
$AGSecondaryObj.Refresh()
$CurrentSync = ($AGSecondaryObj | Select RollupSynchronizationState | Format-Wide | Out-String).Trim()
IF ($CurrentSync -ne "Synchronized") {
Write-Output "Waiting for Synchronized state before failover, still $($CurrentSync)"
Start-Sleep -s 2
}
} Until ($CurrentSync -eq 'Synchronized')
#Perform failover
Write-Output "Beginning Failover $(Get-Date)"
Switch-SqlAvailabilityGroup `
-Path "SQLSERVER:\Sql\$($SecondaryReplicaName1)\$($SecondaryReplicaInstanceName1)\AvailabilityGroups\$($AvailabilityGroupName)\" `
-ErrorAction Stop `
#Only include the next line if it is a forced failover
#-AllowDataLoss -Force
Write-Output "Failover Complete $(Get-Date)"
Start-Sleep -s 10 #Allow failover to resolve
#Return secondary replica to Asynchronous sync
#Note that the values here of Primary and Secondary1 are flipped, because the variables predate the failover.
Invoke-Command -script { `
param($SecondaryReplicaName1, $SecondaryReplicaInstanceName1, $AvailabilityGroupName, $PrimaryReplicaName, $PrimaryReplicaInstanceName)
Set-SqlAvailabilityReplica `
-Path "SQLSERVER:\Sql\$(($SecondaryReplicaName1))\$(($SecondaryReplicaInstanceName1))\AvailabilityGroups\$(($AvailabilityGroupName))\AvailabilityReplicas\$(($SecondaryReplicaName1)+$(IF(($SecondaryReplicaInstanceName1) -ne "DEFAULT"){$("%5C")+(($SecondaryReplicaInstanceName1))} ))" `
-AvailabilityMode asynchronousCommit `
-ErrorAction Stop
Set-SqlAvailabilityReplica `
-Path "SQLSERVER:\Sql\$(($SecondaryReplicaName1))\$(($SecondaryReplicaInstanceName1))\AvailabilityGroups\$(($AvailabilityGroupName))\AvailabilityReplicas\$(($PrimaryReplicaName)+$(IF(($PrimaryReplicaInstanceName) -ne "DEFAULT"){$("%5C")+(($PrimaryReplicaInstanceName))} ))" `
-AvailabilityMode asynchronousCommit `
-ErrorAction Stop
Get-ChildItem "SQLSERVER:\Sql\$($SecondaryReplicaName1)\$($SecondaryReplicaInstanceName1)\AvailabilityGroups\$($AvailabilityGroupName)\AvailabilityReplicas\" | Test-SqlAvailabilityReplica | Format-Table
} -ComputerName $SecondaryReplicaName1 -Args $SecondaryReplicaName1, $SecondaryReplicaInstanceName1, $AvailabilityGroupName, $PrimaryReplicaName, $PrimaryReplicaInstanceName
Write-Output "End $(Get-Date)"
This is version of the script updated for Resource Manager VMs. For classic VMs, I have a similar script in a blog post here.
