azure-sql-dtu-scale.ps1
|
<#PSScriptInfo .VERSION 1.0 .GUID f69bb8de-94e4-481f-bc27-01cc5e1bfcf9 .AUTHOR Indoqubix .COMPANYNAME Indoqubix .COPYRIGHT Indoqubix @ 2020 .TAGS Azure SQL,SQL Scale Up,SQL Scale Down .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .SYNOPSIS Vertically scale an Azure SQL Database up or down according to a schedule using Azure Automation. .DESCRIPTION This Azure Automation runbook enables vertically scaling of an Azure SQL Database according to a schedule. It checked if the database is already in the desired state then no work needs to be done other wise scale up/down the database as per the input edition & tier .PARAMETER resourceGroupName Name of the resource group to which the database server is assigned. .PARAMETER serverName Azure SQL Database server name. .PARAMETER databaseName Azure SQL Database name (case sensitive). .PARAMETER scaleEdition Azure SQL Database Edition that will be required. Available values: Basic, Standard, Premium. .PARAMETER scaleTier Azure SQL Database Tier that will required. Example values: Basic, S0, S1, S2, S3, P1, P2, P4, P6, P11, P15. .EXAMPLE -resourceGroupName myResourceGroup -serverName myserver -databaseName myDatabase -defaultEdition Standard -defaultTier S0 .NOTES AUTHOR: IndoQubix - http://www.indoqubix.com Email : support@indoqubix.com Last Update: 27 Aug 2020 #> param( [Parameter(Mandatory=$True,Position=0)] [ValidateLength(1,100)] [string]$resourceGroupName, [Parameter(Mandatory=$True,Position=1)] [ValidateLength(1,100)] [string]$serverName, [Parameter(Mandatory=$True,Position=2)] [ValidateLength(1,100)] [string]$databaseName, [Parameter(Mandatory=$True,Position=3)] [ValidateLength(1,100)] [string]$scaleEdition, [Parameter(Mandatory=$True,Position=4)] [ValidateLength(1,100)] [string]$scaleTier ) $connectionName = "AzureRunAsConnection" try { # Get the connection "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 } } filter timestamp {"[$(Get-Date -Format G)]: $_"} Write-Output "Script started." | timestamp # Get the database object $sqlDB = Get-AzureRmSqlDatabase ` -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -DatabaseName $databaseName Write-Output "DB name: $($sqlDB.DatabaseName)" | timestamp Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" | timestamp ######################################################## # Set Pricing Tier Database ######################################################## # Check current DG Edition and TIer if ($sqlDB.Edition -eq $scaleEdition -And $sqlDB.CurrentServiceObjectiveName -eq $scaleTier) { Write-Output "Already Database Server $($ServerName)\$($DatabaseName) is in required tier : $($scaleEdition):$($scaleTier)" | timestamp } else { Write-Output "Updating Database Server $($ServerName)\$($DatabaseName) to Edition : $($scaleEdition), tier: $($scaleTier)" | timestamp Write-Output "Updating Database , please wait..." | timestamp $sqlDB | Set-AzureRmSqlDatabase -Edition $scaleEdition -RequestedServiceObjectiveName $scaleTier | out-null } $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName Write-Output "Final DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" | timestamp Write-Output "Database updated successfully" | timestamp |