Start/Stop Group of Azure RM VM's

, 2017-12-19

For testlab purposes, I often have a group of Azure VMs to manually spin up/spin down at once. I've had this script in use for a while and decided I'd comment it up and share.

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 {
$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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads