Elastic Database Jobs: Sample for Running T-SQL Code in Multiple Databases

Introduction:

After moving from on-premise SQL Server to Azure SQL Database, the SQL Server Agent service is no longer available to you. Administrators are therefore missing the ability to run recurring jobs, in order to execute T-SQL scripts and Stored Procedures on a schedule.

Elastic Database Jobs are one way of implementing a scheduled execution on the Azure SQL Database platform. The feature is available through the Azure Portal, but only in a very limited way. To take advantage of the full feature set, you will have to use the corresponding PowerShell cmdlets.

This article takes the general steps from the article "Create and manage a SQL Database elastic database jobs using PowerShell (preview)" and guides you step-by-step to your first Elastic Database Job.

The sample assumes that you already have two Azure SQL Databases on the same SQL server, either in an Elastic Pool or as two unpooled databases. You will create a sample table in each database, and then use the Elastic Database Job to insert rows into both databases.

Prerequisites:

Please follow the PowerShell section in the article "Installing Elastic Database jobs overview" in order to download, import, and install the components for Elastic Database Jobs.

You can find a general overview about Elastic Database Jobs in the article "Create and manage a SQL Database elastic database jobs using PowerShell (preview)". It describes the commands from this blog article in more depth; it also shows you additional commands e.g. regarding the job scheduling or changing the T-SQL scripts after the initial configuration.

Sample Table for testing the job execution:

In each of your databases for this test, run the following T-SQL command to create a sample table:

CREATE TABLE ConnectionDetails (
ConnectionDate datetime default getdate(),
net_transport sql_variant NULL,
protocol_type sql_variant NULL,
auth_scheme sql_variant NULL,
local_net_address sql_variant NULL,
local_tcp_port sql_variant NULL,
client_net_address sql_variant NULL
);

Sample Steps for creating and managing Elastic Database Jobs:

NOTE: In the steps below, you will have to customize the following variables and placeholders to match your own environment:

-SubscriptionId abcdefgh-1234-abcd-1234-abcdefghijkl
$databaseServerName = "yourserver"
$databaseName = "yourdb-1"
$databaseName = "yourdb-2"

(1) Open the Windows PowerShell desktop app.

(2) Switch to the working directory (you have created this directory through the Download and Import steps in article "Installing Elastic Database jobs overview" mentioned above) :

PS C:\> cd C:\nuget\Microsoft.Azure.SqlDatabase.Jobs.0.8.3362.1\tools
PS C:\nuget\Microsoft.Azure.SqlDatabase.Jobs.0.8.3362.1\tools>

(3) Login to Azure:

Login-AzureRmAccount

(4) Get information about the subscription:

Get-AzureRmSubscription

(5) - optional - If you have several subscriptions, select the appropriate one (insert your own subscription ID here) :

Select-AzureRmSubscription -SubscriptionId abcdefgh-1234-abcd-1234-abcdefghijkl

(6) Open a connection to the Elastic Database jobs (this will ask you for the credentials to login to the jobs server and database - you have created these through the Install steps in article "Installing Elastic Database jobs overview" mentioned above) :

Use-AzureSqlJobConnection -CurrentAzureSubscription

(7) Create an encrypted credential for job execution across databases (this will ask you for the credentials to login to your user databases - it assumes that the same credentials are valid for all databases) :

$credentialName = "JobCredential"
$databaseCredential = Get-Credential
$credential = New-AzureSqlJobCredential -Credential $databaseCredential -CredentialName $credentialName
Write-Output $credential

(8) Create a T-SQL script for the cross-database job (this script selects connection details and inserts them into the table you have created above) :

$scriptName = "StoreConnectionDetails"
$scriptCommandText = "
INSERT INTO ConnectionDetails (net_transport, protocol_type, auth_scheme, local_net_address, local_tcp_port, client_net_address)
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address ;
"
$script = New-AzureSqlJobContent -ContentName $scriptName -CommandText $scriptCommandText
Write-Output $script

(9) Create a custom database collection target:

$customCollectionName = "PoolDatabases"
New-AzureSqlJobTarget -CustomCollectionName $customCollectionName

(10) Add database job targets for your two sample databases:

$databaseServerName = "yourserver"
$databaseName = "yourdb-1"
New-AzureSqlJobTarget -DatabaseName $databaseName -ServerName $databaseServerName

$databaseServerName = "yourserver"
$databaseName = "yourdb-2"
New-AzureSqlJobTarget -DatabaseName $databaseName -ServerName $databaseServerName

(11) Add the two databases to a custom database collection target:

$databaseServerName = "yourserver"
$databaseName = "yourdb-1"
$customCollectionName = "PoolDatabases"
Add-AzureSqlJobChildTarget -CustomCollectionName $customCollectionName -DatabaseName $databaseName -ServerName $databaseServerName

$databaseServerName = "yourserver"
$databaseName = "yourdb-2"
$customCollectionName = "PoolDatabases"
Add-AzureSqlJobChildTarget -CustomCollectionName $customCollectionName -DatabaseName $databaseName -ServerName $databaseServerName

(12) Create a job to execute the script that we created above:

$jobName = "StoreConnectionDetails"
$scriptName = "StoreConnectionDetails"
$credentialName = "JobCredential"
$customCollectionName = "PoolDatabases"
$job = New-AzureSqlJob -JobName $jobName -CredentialName $credentialName -ContentName $scriptName -TargetId (Get-AzureSqlJobTarget -CustomCollectionName "PoolDatabases").TargetId
Write-Output $job

(13) Execute the job (this will return a JobExecutionId that you will use in the next step) :

$jobName = "StoreConnectionDetails"
$jobExecution = Start-AzureSqlJobExecution -JobName $jobName
Write-Output $jobExecution

(14) Retrieve the state of a single job execution (insert the JobExecutionId from the preceding step) :

$jobExecutionId = "27949a8a-5434-4c89-ad3b-6a2704a0808a"
$jobExecutions = Get-AzureSqlJobExecution -JobExecutionId $jobExecutionId -IncludeChildren
Write-Output $jobExecutions

(15) You may run the preceding step repeatedly until the "Lifecycle" columns reports "Succeeded". If you then check the sample tables in the databases, you should see that one row had been inserted in each of them:

select * from ConnectionDetails

 

I hope that these steps will help you getting started with Elastic Database Jobs. Please leave a comment below in case you encounter any issues with this article!