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"
$dbcredential=get-credential
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.
$Servername="mytestinst1.database.windows.net"
$DBname="mytestdb1"
$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
$jobexecutionid="cdfac7e0-79d6-46ce-afff-9a22902eeab6"
$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
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-jobs-powershell
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"
$dbcredential=get-credential
$credential = New-AzureSqlJobCredential -Credential $dbCredential -CredentialName $credentialName
write-output $credential
Note: I am not touching anything related to sharding(Scale out) as it is Different topic altogether.
$Servername="mytestinst1.database.windows.net"
$DBname="mytestdb1"
$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
$jobexecutionid="cdfac7e0-79d6-46ce-afff-9a22902eeab6"
$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
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-jobs-powershell
Comments