Been playing around with Azure Automation this past month and had a requirement to Up-scale & Down-scale Azure SQL Database tiers in an automated manner. I’m talking Azure SQL DB (DBaaS) and not SQL Server on Azure (IaaS). To get this automation, we were using the Azure Runbook Automation PowerShell workflow.
Basically, we needed to up-scale the database tier from Premium P1 to P2 in the morning and down-scale it back down to P1 in the night. To save costs you know 😉
There is a lot of information on the WWW showing different ways one can write a PS automation workflow. This post is to show you just 1 way that I was able to get it to work.
- Login to your Azure Subscription and go to the Resource Group that contains the SQL Database you need to work with.
- Create a new Automation Account
- Since I was trying to automate just our Cloud stuff in Azure (SQL DB), I ended up choosing the authentication method for the Automation account as “Azure Run As Account”
Note: you can also use an Azure AD Account depending on your organization’s architecture - Once the automation account has been setup, open up it’s blade in the Azure Portal and got to Runbooks. You will notice that there are already 3-4 tutorial runbooks present there by default.
- Add a Runbook -> Create a new Runbook. Give it some meaningful name for e.g. ResizeSQLDatabase
- For the runbook type, I chose PowerShell workflow. (You can use the Graphical workflow too).
- You should now see a new PS workflow window with the workflow name that you specified. Just like a regular PS workflow, you can define input parameters in the param () block.
param
(
[parameter(Mandatory=$true)]
[string]$ResourceGroupName
)
The below step is important. For Azure automation to work, it needs to use some form of credentials to perform your automation work. In my case up-scale & down-scale database tiers on a schedule. Copy & paste the below code section right after the param block in the workflow
# Get the connection "AzureRunAsConnection"
try
{
$connectionName = "AzureRunAsConnection"
$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName
"Logging in to Azure..."
Add-AzureRmAccount `
-ServicePrincipal `
-TenantId $servicePrincipalConnection.TenantId `
-ApplicationId $servicePrincipalConnection.ApplicationId `
-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
catch
{
if (!$servicePrincipalConnection)
{
$ErrorMessage = "Connection $connectionName not found."
throw $ErrorMessage
}
else
{
Write-Error -Message $_.Exception
throw $_.Exception
}
}
What the above code is doing is basically using the built-in automation account called “AzureRunAsConnection” and it’s credentials to run the PS workflow.
Now, coming to the actual SQL DB scaling, you can use the below cmdlet to do that
Set-AzureRMSqlDatabase -DatabaseName $DatabaseName -ResourceGroupName $ResourceGroupName -ServerName $ServerName -Edition $NewEdition -RequestedServiceObjectiveName $NewPricingTier
Each of the $variables in the above line are input parameters to my runbook. $NewPricingTier is the one that controls the SQL database tier & DTU’s
You can find more information about the parameters values here -> https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/?rnd=1
Once done, save the runbook and create job schedules for the new runbook with the appropriate input parameters and let Azure Automation do the work for you! 😀
Just a quick post to help anyone out there with their first Azure Automation runbook.
When SQL Server setup goes about installing the various SQL Server components, one of the things it has to do is to create the SQL Server services like SQL Engine, RS, AS, Browser service, SQL Writer service etc. Obviously if setup is not able to do this, it will fail. For someone to create/delete/modify a Windows service, they need to talk to the Service Control Manager (SCM). How do you isolate whether the problem is with SQL Setup or some other component? This is how I ended writing this code to test SCManager.
The SQL Dude! 