Scaling Azure VM with Azure Automation, with help of Azure SQL

Posted: 8th September 2014 by Anders Bengtsson in Azure, Scripts

I am running a number of virtual machines in Microsoft Azure, some of them just a couple of hours and some I keep running 24/7. Often I don’t need them during the night, but some I still want to keep online to make sure database sync jobs runs. Previous I have been using a couple of different Powershell scripts that start and stop virtual machines, but now I have built a new solution, and want to share the idea with you. This new solution involves two components, except the virtual machines, it includes Azure Automation and Azure SQL. Azure Automation (current in preview) is an automation engine that you can buy as a service in Microsoft Azure. If you have been working with Service Management Automation (SMA) you will recognize the feature. You author runbooks in PowerShell workflow and execute them in Microsoft Azure, same way as with SMA, just that you don’t have to handle the SMA infrastructure. Azure SQL is another cool cloud service, a relational database-as-a-service.

Figur 1 Azure Automation

Figur 2 Azure SQL Database

In Azure Automation I have created two runbooks (VM-MorningStart and VM-EveningStop). One that runs every morning and one that runs every evening. These runbooks read configuration from the Azure SQL database about how the virtual machines should be configured during night and day. Azure Automation have a feature, schedule, which can trigger these runbooks once every day, shown in figure 3.

Figur 3 Schedule to invoke runbook

Both runbooks are built the same way. They check in the database for servers and then compare the current settings with the settings in the database. The database has one column for day time server settings and one for Night time server settings. If the current setting is not according to the database the virtual machine is shutdown, re-configured and restarted again. I use Azure AD to connect to the Azure subscription according to this blog post, and I use SQL authentication to logon to the SQL server. If a virtual machine is set to size 0 in the database it seems the machine should be turned of during the Night.

workflow VM-EveningStop
{
### SETUP AZURE CONNECTION
###
$Cred = Get-AutomationPSCredential -Name 'azureautomation@domainnameAD.onmicrosoft.com'
Add-AzureAccount -Credential $cred
### Connect to SQL
InlineScript {
$UserName = "azuresqluser"
$UserPassword = Get-AutomationVariable -Name 'Azure SQL user password'
$Servername = "sqlservername.database.windows.net"
$Database = "GoodNightServers"
$MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
$MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = $Database; User ID = $UserName; Password = $UserPassword;"
$MasterDatabaseConnection.Open();
$MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
$MasterDatabaseCommand.Connection = $MasterDatabaseConnection
$MasterDatabaseCommand.CommandText = "SELECT * FROM Servers"
$MasterDbResult = $MasterDatabaseCommand.ExecuteReader()
#
if ($MasterDbResult.HasRows)
{
while($MasterDbResult.Read())
{
Select-AzureSubscription -SubscriptionName "Windows Azure MSDN - Visual Studio Ultimate"
$AzureVM = Get-AzureVM | Where-Object {$_.InstanceName -eq $MasterDbResult[1]}
Get-AzureVM -ServiceName $AzureVM.ServiceName | Stop-AzureVM -Force
#
If ($MasterDbResult[2] -eq "0") {
}
Else {
Get-AzureVM -ServiceName $AzureVM.ServiceName | Set-AzureVMSize $MasterDbResult[2] | Update-AzureVM
Get-AzureVM -ServiceName $AzureVM.ServiceName | Start-AzureVM
}
}
}
}
}

The database is designed according to this figure (click to make it larger)

AzureSQL

Summary: Azure automation run one runbook in the morning to start up and scale up virtual machines, and one runbook in the evening to scale down and shut down virtual machines. All Configuration is stored in a Azure SQL database.

Note that this is provided “AS-IS” with no warranties at all. This is not a production ready management pack or solution for your production environment, just a idea and an example.

  1. steven Smith says:

    To learn automate scaling/resizing of Azure VMs .

    http://cloudmonix.com/blog/how-to-automate-scaling-of-azure-vms/

  2. […] The following is the sample code that I have to convert existing system ton an A4. I found that the ability to change the size of a VM could only occur through an inline script which explains some of the complexity shown below. See Ander’s blog for a better way to handle things like this on his article available at: http://contoso.se/blog/?p=4042 […]

  3. Noah says:

    Thanks for sharing. I was looking at a this approach and ended up working out a way to use the new tags feature in Azure to store the schedules, which makes it a little easier to manage them from the portal. For those interested, the runbook is available with more detail here:

    https://automys.com/library/asset/scheduled-virtual-machine-shutdown-startup-microsoft-azure

  4. Hi, you can use this solution to both start and stop Your Machines at any given time. You can use SMA, Orchestrator or Azure Automation to do that. Not sure if I understod Your Question correct.

  5. Hello, I have almost experience in performing runbooks of Azure and I need to know if they can give me a runbook, already, for example, to turn off my azure at a certain hour and another machine to turn it on, please. I’ve watched anything on the internet but not clarify me. Without more and waiting that I can help, receive a cordial greeting.
    Miguel

  6. Markus Neifer says:

    Nice idea. Thanks for sharing.