• The Azure Noob

Azure SQL DB - automated/Scheduled Resize




Anyone who has used the automation tools will know how frustrating the Gallery can be.


I've Imported various PowerShell scripts before now only to fine a bunch of corrupt characters where simple quotation marks or even replacing a simple pipe. I'm not sure why exactly it does this, but it's definitely one to watch out for when using the Gallery.


The Characters I'm referring too are "â€".


So after spending a lot of time trying to get Gallery running and chasing my own tail for different broken characters I decided to look what I could do instead.


What I found was someone just as equally frustrated with the Automation setup as me :- https://blobeater.blog/2018/10/11/auto-scaling-azure-sql-db/ However they decided to go down the WebJobs route, obviously you could do this but what I decided was more "Controllable" from a scheduling point of view was to still use the automation account.



1. Create Stored Procedures

So first thing to do is to create the SP’s on the Database :-


I wanted 2 resizing SP's one to Scale Up during the day, the other to scale down during the night.


CREATE PROCEDURE dbo.ScaleUp

AS

ALTER DATABASE [DBNAME] MODIFY (SERVICE_OBJECTIVE = 'PricingTier');

--- PricingTier – EG:- ‘SO’,‘S1’,‘S2’,‘GP_GEN5_2’,‘GP_GEN5_2’,‘GP_GEN5_4’


CREATE PROCEDURE dbo.ScaleDown

AS

ALTER DATABASE [DBNAME] MODIFY (SERVICE_OBJECTIVE = 'PricingTier');

--- PricingTier – EG:- ‘SO’,‘S1’,‘S2’,‘GP_GEN5_2’,‘GP_GEN5_2’,‘GP_GEN5_4’


If you want to run the SP and confirm they work, you can run this script

(be aware it takes a bit of time to update as it is resizing, so allow 2-5 mins for the process to complete)


SELECT Edition = DATABASEPROPERTYEX('DBNAME', 'Edition'),

ServiceObjective = DATABASEPROPERTYEX('DBNAME', 'ServiceObjective')



2. Create the Run Book

So now we have both SP's we want to setup a Automation Run Book to fire off each SP when we're ready.

In automation create a run book with the following PowerShell Workflow

(this script will work for any SP you want to run on a schedule)


workflow <CustomName>

{

[cmdletbinding()]

param

(

# Fully-qualified name of the Azure DB server

[parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

[string] $SqlServerName,

# Name of database to connect and execute against

[parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

[string] $DBName,

# Name of stored procedure to be executed

[parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

[string] $StoredProcName,

# Credentials for $SqlServerName stored as an Azure Automation credential asset

[parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

[PSCredential] $Credential

)

inlinescript

{

Write-Output “JOB STARTING”

# Setup variables

$ServerName = $Using:SqlServerName

$UserId = $Using:Credential.UserName

$Password = ($Using:Credential).GetNetworkCredential().Password

$DB = $Using:DBName

$SP = $Using:StoredProcName

# Create & Open connection to Database

$DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection

$DatabaseConnection.ConnectionString = “Data Source = $ServerName; Initial Catalog = $DB; User ID = $UserId; Password = $Password;”

$DatabaseConnection.Open();

Write-Output “CONNECTION OPENED”

# Create & Define command and query text

$DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand

$DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure

$DatabaseCommand.Connection = $DatabaseConnection

$DatabaseCommand.CommandText = $SP

Write-Output “EXECUTING QUERY”

# Execute the query

$DatabaseCommand.ExecuteNonQuery()

# Close connection to DB

$DatabaseConnection.Close()

Write-Output “CONNECTION CLOSED”

Write-Output “JOB COMPLETED”

}

}



3. Setup Credentials

Now we have the SP's and the Run Book created. We now need to add a SQL Credential


Within the Automation Account you need to setup/assign SQL Credentials, these will be used later by the runbook as it processes

Under Shared Resources select “Credentials” and Click Add a Credential

Give the Credential a useful name and populate with User Name and Password then Create


4. Scheduling

Now you have your SP in the DB and your RunBook to trigger the SP you need to setup a schedule and pass through parameters for the run.

Within the Automation Account under Shared Resources, select Schedule

Create Schedules for what you need to do.

(In my example, I’m running a schedule to Increase the Size of the Azure SQL DB in the morning and another to Decrease in the evening)



Once your schedule is configured you then need to assign it to the Runbook and populate with parameters.




5. Linking Schedule to your Runbook

Within the Runbook page select “Link to schedule”



A new page will open within here we need to link to the schedule you’ve just created and configure the parameters of the run you’d like to process.



Link the schedule you created in the previous step.


Then within “Parameters and run settings” populate


The Parameters are self-explanatory Pass through the “Credential Name” you gave the earlier.


Once complete the SP will run on the Schedule you’ve created.


With the way I have it configured, I setup a separate schedule for the Scale Up and a Scale Down, then just call the appropriate SP on the appropriate Schedule.



And with that you should be done.



Good look and get in contact if you need any assistance, or if you have any advice/opinions

188 views1 comment

Written by The Azure Noob. Lazily using WIX since ©2019. Who has time to build there own site.

This site was designed with the
.com
website builder. Create your website today.
Start Now