Scratch work for SQL Elastic Jobs in Azure w/ a PowerShell script

AzureElasticJobTest.ps1

import-module az.sql

<# TODO
Based on:
https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-powershell-create?view=azuresql

* Add "Get" commants to top of script as appropriate
* Be able to loop through set of servers
* Document steps and/or separate files
* Parameterize passwords for master & job user accounts
#>


# sign in to Azure account
Connect-AzAccount

#Folder containing sql scripts to import
$sourceFolder = "C:\SourceCode\ElasticJobsTest\"


#Get Access Token to connect to SQL Servers
$AccessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

$rg = "MyAccount-rg"
$agentServerName = "MyServer-elasticjobs"
$agentServer = Get-AzSqlServer -ResourceGroupName $rg -ServerName $agentServerName
$jobDatabaseName = "sqljobs"
$jobDatabase = Get-AzSqlDatabase -ResourceGroupName $rg -ServerName $agentServerName -DatabaseName $jobDatabaseName


$agentName = "ElasticJobsTest"
$jobagent = Get-AzSqlElasticJobAgent -Name $agentName -ServerName $agentServerName -ResourceGroupName $rg


$db1 = Get-AzSqlDatabase -ResourceGroupName $rg -ServerName "MyServer-SO" -DatabaseName "StackOverflow2010"
$db2 = Get-AzSqlDatabase -ResourceGroupName $rg -ServerName "MyServer" -DatabaseName "AdventureWorks"

$targetServer1 = Get-AzSqlServer -ResourceGroupName $rg -ServerName "MyServer-SO"
$targetServer2 = Get-AzSqlServer -ResourceGroupName $rg -ServerName "MyServer"

<# create appropriate resource group, server, database for Elastic Jobs

# create a resource group
Write-Output "Creating a resource group..."
$resourceGroupName = Read-Host "Please enter a resource group name"
$location = Read-Host "Please enter an Azure Region"
$rg = New-AzResourceGroup -Name $resourceGroupName -Location $location
$rg

# create a server
Write-Output "Creating a server..."
$agentServerName = Read-Host "Please enter an agent server name"
$agentServerName = $agentServerName + "-" + [guid]::NewGuid()
$adminLogin = Read-Host "Please enter the server admin name"
$adminPassword = Read-Host "Please enter the server admin password"
$adminPasswordSecure = ConvertTo-SecureString -String $AdminPassword -AsPlainText -Force
$adminCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $adminLogin, $adminPasswordSecure
$agentServer = New-AzSqlServer -ResourceGroupName $resourceGroupName -Location $location `
    -ServerName $agentServerName -ServerVersion "12.0" -SqlAdministratorCredentials ($adminCred)

# set server firewall rules to allow all Azure IPs
Write-Output "Creating a server firewall rule..."
$agentServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs
$agentServer

# create the job database
Write-Output "Creating a blank database to be used as the Job Database..."
$jobDatabaseName = "JobDatabase"
$jobDatabase = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $agentServerName -DatabaseName $jobDatabaseName -RequestedServiceObjectiveName "S0"
$jobDatabase

#> ##create pre-reqs

<#  Create target databases for elastic jobs to hit
# create a target server and sample databases - uses the same credentials
Write-Output "Creating target server..."
$targetServerName = Read-Host "Please enter a target server name"
$targetServerName = $targetServerName + "-" + [guid]::NewGuid()
$targetServer = New-AzSqlServer -ResourceGroupName $resourceGroupName -Location $location `
    -ServerName $targetServerName -ServerVersion "12.0" -SqlAdministratorCredentials ($adminCred)

# set target server firewall rules to allow all Azure IPs
$targetServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs
$targetServer | New-AzSqlServerFirewallRule -StartIpAddress 0.0.0.0 -EndIpAddress 255.255.255.255 -FirewallRuleName AllowAll
$targetServer

# create sample databases to execute jobs against
$db1 = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $targetServerName -DatabaseName "database1"
$db1
$db2 = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $targetServerName -DatabaseName "database2"
$db2
#>

<# Create Elastic Job Agent

Write-Output "Creating job agent..."
$agentName = Read-Host "Please enter a name for your new Elastic Job agent"
$jobAgent = $jobDatabase | New-AzSqlElasticJobAgent -Name $agentName
$jobAgent

#>

<# Create logins
# in the master database (target server #1)
# create the master user login, master user, and job user login
$params = @{
  'database' = 'master'
  'serverInstance' =  $targetServer1.FullyQualifiedDomainName
  #'username' = $adminLogin
  #'password' = $adminPassword
  'AccessToken' = $AccessToken
  'outputSqlErrors' = $true
  'query' = 'CREATE LOGIN masteruser WITH PASSWORD=''password!123'''
}
Invoke-SqlCmd @params
$params.query = "CREATE USER masteruser FROM LOGIN masteruser"
Invoke-SqlCmd @params
$params.query = 'CREATE LOGIN jobuser WITH PASSWORD=''password!123'''
Invoke-SqlCmd @params

# for each target database
# create the jobuser from jobuser login and check permission for script execution
$targetDatabases = ($targetserver1 | get-azsqldatabase | where DatabaseName -ne "master" | select -expandProperty DatabaseName)
$createJobUserScript =  "CREATE USER jobuser FROM LOGIN jobuser"
$grantAlterSchemaScript = "GRANT ALTER ON SCHEMA::dbo TO jobuser"
$grantCreateScript = "GRANT CREATE TABLE TO jobuser"

$targetDatabases | % {
  $params.database = $_
  $params.query = $createJobUserScript
  Invoke-SqlCmd @params
  $params.query = $grantAlterSchemaScript
  Invoke-SqlCmd @params
  $params.query = $grantCreateScript
  Invoke-SqlCmd @params
}

## Create logins/users for target server #2
$params = @{
  'database' = 'master'
  'serverInstance' =  $targetServer2.FullyQualifiedDomainName
  #'username' = $adminLogin
  #'password' = $adminPassword
  'AccessToken' = $AccessToken
  'outputSqlErrors' = $true
  'query' = 'CREATE LOGIN masteruser WITH PASSWORD=''password!123'''
}
Invoke-SqlCmd @params
$params.query = "CREATE USER masteruser FROM LOGIN masteruser"
Invoke-SqlCmd @params
$params.query = 'CREATE LOGIN jobuser WITH PASSWORD=''password!123'''
Invoke-SqlCmd @params

# for each target database
# create the jobuser from jobuser login and check permission for script execution
$targetDatabases = ($targetserver2 | get-azsqldatabase | where DatabaseName -ne "master" | select -expandProperty DatabaseName)
$createJobUserScript =  "CREATE USER jobuser FROM LOGIN jobuser"
$grantAlterSchemaScript = "GRANT ALTER ON SCHEMA::dbo TO jobuser"
$grantCreateScript = "GRANT CREATE TABLE TO jobuser"

$targetDatabases | % {
  $params.database = $_
  $params.query = $createJobUserScript
  Invoke-SqlCmd @params
  $params.query = $grantAlterSchemaScript
  Invoke-SqlCmd @params
  $params.query = $grantCreateScript
  Invoke-SqlCmd @params
}



# create job credential in Job database for master user
Write-Output "Creating job credentials..."
$loginPasswordSecure = (ConvertTo-SecureString -String 'password!123' -AsPlainText -Force)

$masterCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "masteruser", $loginPasswordSecure
$masterCred = $jobAgent | New-AzSqlElasticJobCredential -Name "masteruser" -Credential $masterCred

$jobCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "jobuser", $loginPasswordSecure
$jobCred = $jobAgent | New-AzSqlElasticJobCredential -Name "jobuser" -Credential $jobCred
#>


<# Create Jobs
Write-Output "Creating test target groups..."
# create ServerGroup target group
$serverGroup = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroup'
$serverGroup = $jobAgent | Get-AzSqlElasticJobTargetGroup -Name 'ServerGroup'
$serverGroup | Add-AzSqlElasticJobTarget -ServerName $targetServer1.ServerName -RefreshCredentialName $masterCred.CredentialName
$serverGroup | Add-AzSqlElasticJobTarget -ServerName $targetServer2.ServerName -RefreshCredentialName $masterCred.CredentialName

# create ServerGroup with an exclusion of db2
$serverGroupExcludingDb2 = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroupExcludingDb2'
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServer2.ServerName -Database $db2.DatabaseName -Exclude
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServer1.ServerName -Database $db1.DatabaseName


Write-Output "Creating a new job..."
$jobName = "Test Job - create tables"
$job = $jobAgent | New-AzSqlElasticJob -Name $jobName -RunOnce
$job = $jobAgent | Get-AzSqlElasticJob -Name $jobName
$job

Write-Output "Creating job steps..."
$sqlText1 = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('Step1Table')) CREATE TABLE [dbo].[Step1Table]([TestId] [int] NOT NULL);"
$sqlText2 = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('Step2Table')) CREATE TABLE [dbo].[Step2Table]([TestId] [int] NOT NULL);"

$job | Add-AzSqlElasticJobStep -Name "step1" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText1
$job | Add-AzSqlElasticJobStep -Name "step2" -TargetGroupName $serverGroupExcludingDb2.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText2

#>


<# Start Job

Write-Output "Start a new execution of the job..."
$jobExecution = $job | Start-AzSqlElasticJob
$jobExecution

## Get Job Status
$jobAgent | Get-AzSqlElasticJobExecution -Count 10
$jobExecution | Get-AzSqlElasticJobStepExecution
$jobExecution | Get-AzSqlElasticJobTargetExecution -Count 2
#>


<# Create Ola Stats Job

$serverGroup = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'AllServers'
$serverGroup = $jobAgent | Get-AzSqlElasticJobTargetGroup -Name 'AllServers'
$serverGroup | Add-AzSqlElasticJobTarget -ServerName $targetServer1.ServerName -RefreshCredentialName $masterCred.CredentialName
$serverGroup | Add-AzSqlElasticJobTarget -ServerName $targetServer2.ServerName -RefreshCredentialName $masterCred.CredentialName

$jobName = "Ola Hallengren Stats Maintenance"
$job = $jobAgent | New-AzSqlElasticJob -Name $jobName -RunOnce
$job = $jobAgent | Get-AzSqlElasticJob -Name $jobName


Write-Output "Creating job steps..."
#Create Maintenance Schema
$sqlText1 = "IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Maintenance')
BEGIN
  EXEC sp_executesql @command = N'CREATE SCHEMA [Maintenance];'
  END
  GRANT EXEC ON SCHEMA::Maintenance TO JobUser;
"
## Modify Ola's scripts to use "Maintenance" schema instead of "dbo" schema for these objects
#Create CommandLog table
$sqlText2 = Get-Content ($sourceFolder + "CommandLog.sql") -Raw
#Deploy CommandExecute proc
$sqlText3 = Get-Content ($sourceFolder + "CommandExecute.sql") -Raw
#Deploy IndexOptimize proc
$sqlText4 = Get-Content ($sourceFolder + "IndexOptimize.sql") -Raw


#Step to Update Statistics
$sqlText5 = "DECLARE @DbName SYSNAME = DB_NAME();

EXECUTE Maintenance.IndexOptimize
  @Databases = @DbName,
  @FragmentationLow = NULL,
  @FragmentationMedium = NULL,
  @FragmentationHigh = NULL,
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y',
  @LogToTable = 'Y';"




$job | Add-AzSqlElasticJobStep -Name "Create Maintenance Schema" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText1
$job | Add-AzSqlElasticJobStep -Name "Create CommandLog" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText2
$job | Add-AzSqlElasticJobStep -Name "Create CommandExec" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText3
$job | Add-AzSqlElasticJobStep -Name "Create IndexOptimize" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText4
$job | Add-AzSqlElasticJobStep -Name "IndexOptimize - Update Stats" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText5



#>

<# Create Ola Index Job
$jobName = "Ola Hallengren Index Maintenance"
$job = $jobAgent | New-AzSqlElasticJob -Name $jobName -RunOnce
$job = $jobAgent | Get-AzSqlElasticJob -Name $jobName


Write-Output "Creating job steps..."
#Create Maintenance Schema
$sqlText1 = "IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Maintenance')
BEGIN
  EXEC sp_executesql @command = N'CREATE SCHEMA [Maintenance];'
  END
  GRANT EXEC ON SCHEMA::Maintenance TO JobUser;
"
## Modify Ola's scripts to use "Maintenance" schema instead of "dbo" schema for these objects
#Create CommandLog table
$sqlText2 = Get-Content ($sourceFolder + "CommandLog.sql") -Raw
#Deploy CommandExecute proc
$sqlText3 = Get-Content ($sourceFolder + "CommandExecute.sql") -Raw
#Deploy IndexOptimize proc
$sqlText4 = Get-Content ($sourceFolder + "IndexOptimize.sql") -Raw

#Step to Run Index Defrag
$sqlText5 = "DECLARE @DbName SYSNAME = DB_NAME();

EXECUTE Maintenance.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationLevel1 = 5,
  @FragmentationLevel2 = 30"

$job | Add-AzSqlElasticJobStep -Name "Create Maintenance Schema" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText1
$job | Add-AzSqlElasticJobStep -Name "Create CommandLog" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText2
$job | Add-AzSqlElasticJobStep -Name "Create CommandExec" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText3
$job | Add-AzSqlElasticJobStep -Name "Create IndexOptimize" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText4
$job | Add-AzSqlElasticJobStep -Name "IndexOptimize - Index Maintenance" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText5

#>


<# Start Ola Job

Write-Output "Start a new execution of the job..."
$job = $jobagent | Get-AzsqlElasticJob -Name "Ola Hallengren Stats Maintenance"
$jobExecution = $job | Start-AzSqlElasticJob
$jobExecution

## Get Job Status
$jobAgent | Get-AzSqlElasticJobExecution -Count 10
$jobExecution | Get-AzSqlElasticJobStepExecution
$jobExecution | Get-AzSqlElasticJobTargetExecution -Count 2



Write-Output "Start a new execution of the job..."
$job = $jobagent | Get-AzsqlElasticJob -Name "Ola Hallengren Index Maintenance"
$jobExecution = $job | Start-AzSqlElasticJob
$jobExecution

## Get Job Status
$jobAgent | Get-AzSqlElasticJobExecution -Count 10
$jobExecution | Get-AzSqlElasticJobStepExecution
$jobExecution | Get-AzSqlElasticJobTargetExecution -Count 2
#>




<# Update Ola Job w/ New code
## Update Jobsteps
$job = $jobagent | Get-AzsqlElasticJob -Name "Ola Hallengren Index Maintenance"

## Update SQL Text for Ola's objects from SourceFolder
#Create CommandLog table
$sqlText2 = Get-Content ($sourceFolder + "CommandLog.sql") -Raw
#Deploy CommandExecute proc
$sqlText3 = Get-Content ($sourceFolder + "CommandExecute.sql") -Raw
#Deploy IndexOptimize proc
$sqlText4 = Get-Content ($sourceFolder + "IndexOptimize.sql") -Raw


$CommandLogJobstep = $job | Get-AzSqlElasticJobStep -StepName "Create CommandLog"
$CommandExecJobstep = $job | Get-AzSqlElasticJobStep -StepName "Create CommandExec"
$IndexOptimizeJobstep = $job | Get-AzSqlElasticJobStep -StepName "Create IndexOptimize"

$CommandLogJobStep | Set-AzSqlElasticJobStep -CommandText $sqlText2
$CommandExecJobstep | Set-AzSqlElasticJobStep -CommandText $sqlText3
$IndexOptimizeJobstep | Set-AzSqlElasticJobStep -CommandText $sqlText4

#>