(SQL) Tip of the Day: Using Elastic Jobs for day to day tasks

Today’s Tip…

Elastic Database Jobs are a great way to schedule actions in SQL Azure that can be run via TSQL. I have created a script that will help you create a scheduled job to automatically scale your database up and down on a schedule. You should be able to use this as a template to create the jobs you need:

#Make sure you have Azure PowerShell Installed https://azure.microsoft.com/en-gb/documentation/articles/powershell-install-configure/

#Download nuget command line: https://nuget.org/nuget.exe

#Use Nuget to install the Azure Sql Database Jobs library

.\nuget install Microsoft.Azure.SqlDatabase.Jobs -prerelease

#You will now have a directory Microsoft.Azure.SqlDatabase.Jobs.x.x.xxxx.x where x.x.xxxx.x reflects the version number.

#move your working directory to Microsoft.Azure.SqlDatabase.Jobs.x.x.xxxx.x\tools

#Add the cmdlets

Unblock-File .\InstallElasticDatabaseJobsCmdlets.ps1

.\InstallElasticDatabaseJobsCmdlets.ps1

#Create your Elastic Jobs database and worker roles

Unblock-File .\InstallElasticDatabaseJobs.ps1

.\InstallElasticDatabaseJobs.ps1

#Login to Azure

Login-AzureRmAccount

#Setup your Elastic Jobs connection, use the credentials you created above

Use-AzureSqlJobConnection -CurrentAzureSubscription

#Create your credentials to the server/database you want to work on

$credentialName = "Your Credential Name Here"

$databaseCredential = Get-Credential

$credential = New-AzureSqlJobCredential -Credential $databaseCredential -CredentialName $credentialName

Write-Output $credential

#Create your first TSQL script (This one is to scale your database to S2)

$scriptName = "Scale Up The Database"

$scriptCommandText = "ALTER DATABASE [YourDatabaseHere] MODIFY (EDITION='standard', SERVICE_OBJECTIVE='S2')"

$script = New-AzureSqlJobContent -ContentName $scriptName -CommandText $scriptCommandText

Write-Output $script

#Create your second TSQL script (This one scales down your database to S0)

$scriptName2 = "Scale Down The Database"

$scriptCommandText = "ALTER DATABASE [YourDatabaseHere] MODIFY (EDITION='standard', SERVICE_OBJECTIVE='S0')"

$script = New-AzureSqlJobContent -ContentName $scriptName2 -CommandText $scriptCommandText

Write-Output $script

#Add your database as a job target (since we are scaling, point at the master db)

$databaseName = "master"

$databaseServerName = "YourSQLAzureServer"

New-AzureSqlJobTarget -DatabaseName $databaseName -ServerName $databaseServerName

#Create your database collection

$customCollectionName = "DatabaseCollectionNameHere"

New-AzureSqlJobTarget -CustomCollectionName $customCollectionName

#Add the database to your DB Collection

Add-AzureSqlJobChildTarget -CustomCollectionName $customCollectionName -DatabaseName $databaseName -ServerName $databaseServerName

#Create the Scale up job

$jobName1 = "Your Scale Up Job Name"

$target = Get-AzureSqlJobTarget -CustomCollectionName $customCollectionName

$job = New-AzureSqlJob -JobName $jobName1 -CredentialName $credentialName -ContentName $scriptName -TargetId $target.TargetId

Write-Output $job

#Create the Scale down job

$jobName2 = "Your Scale Down Job Name"

$target = Get-AzureSqlJobTarget -CustomCollectionName $customCollectionName

$job = New-AzureSqlJob -JobName $jobName2 -CredentialName $credentialName -ContentName $scriptName2 -TargetId $target.TargetId

Write-Output $job

#These two lines can be used to test execution, commented out unless you want to use them

#$jobExecution = Start-AzureSqlJobExecution -JobName $jobName1

#Write-Output $jobExecution

#

#$jobExecution = Start-AzureSqlJobExecution -JobName $jobName2

#Write-Output $jobExecution

#Create our first schedule (Runs daily at 10 AM in the local time it was created)

$scheduleName1 = "10 AM Central Time Daily"

$DayInterval = 1

$startTime = (Get-Date "6/9/2016 10:00 AM").ToUniversalTime()

$schedule = New-AzureSqlJobSchedule -DayInterval $DayInterval -ScheduleName $scheduleName1 -StartTime $startTime

Write-Output $schedule

#Add the job and schedule to trigger

$jobTrigger = New-AzureSqlJobTrigger -ScheduleName $scheduleName1 –JobName $jobName1

Write-Output $jobTrigger

#Create our second schedule (Runs daily at 11 AM in the local time it was created)

$scheduleName2 = "11 AM Central Time Daily"

$DayInterval = 1

$startTime = (Get-Date "6/9/2016 11:00 AM").ToUniversalTime()

$schedule = New-AzureSqlJobSchedule -DayInterval $DayInterval -ScheduleName $scheduleName2 -StartTime $startTime

Write-Output $schedule

#Add the job and schedule to trigger (Round 2)

$jobTrigger = New-AzureSqlJobTrigger -ScheduleName $scheduleName2 –JobName $jobName2

Write-Output $jobTrigger