SQL Azure Elastic Jobs using powershell

Hi Mates,

In this post we are going to see how we can schedule the jobs with the help of powershell of what we created in my previous post.

Supported Elastic Database jobs group types

The job executes Transact-SQL (T-SQL) scripts or application of DACPACs across a group of databases. When a job is submitted to be executed across a group of databases, the job “expands” the into child jobs where each performs the requested execution against a single database in the group.

There are two types of groups that you can create:

Shard Map group: When a job is submitted to target a shard map, the job queries the shard map to determine its current set of shards, and then creates child jobs for each shard in the shard map.

Custom Collection group: A custom defined set of databases. When a job targets a custom collection, it creates child jobs for each database currently in the custom collection.

To set the Elastic Database jobs connection

A connection needs to be set to the jobs control database prior to using the jobs APIs. Running this cmdlet triggers a credential window to pop up requesting the user name and password created when installing Elastic Database jobs.Hence kindly pass username & password of your Azure instance

#setup the connection to your elastic jobs db
Use-AzureSqlJobConnection -CurrentAzureSubscription

Encrypted credentials within the Elastic Database jobs

Database credentials can be inserted into the jobs control database with its password encrypted. It is necessary to store credentials to enable jobs to be executed at a later time, (using job schedules).

We can treat this as similar to our proxies with respect to on-premises

#setup a credential to run jobs

$credentialname="job cred"

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

write-output $credential

Pass the server & Database name for elastic jobs to work on.
Note: I am not touching anything related to sharding(Scale out) as it is Different topic altogether.

$DBtarget=New-AzureSqlJobTarget -DatabaseName $DBname -ServerName $Servername

$scriptName = "Index Fragmentation"
$scriptCommandText ="Alter index all on process_list rebuild"
$script = New-AzureSqlJobContent -ContentName $scriptName -CommandText $scriptCommandText
$credentialname="job cred"
$jobname="index maintenance"
$job = New-AzureSqlJob -ContentName $scriptName -CredentialName $credentialName -JobName $jobName -TargetId 0c0d1d2e-9246-41b3-843a-0ff50f5f4961

Execute the job
$jobExecution = Start-AzureSqlJobExecution -JobName $jobName

To retrieve the state of a single job execution

$jobExecution = Get-AzureSqlJobExecution -JobExecutionId $jobExecutionId
Write-Output $jobExecution

I would strongly recommend you to look for the below link as it explores all the options like scheduling cancelling & removing the job