| Elastic Jobs are currently in public preview. The feature is ready and we are preparing to make it officially generally available. No extra steps are required to get started. |
Determining the Cause of Failure
Assume for the purposes of demonstration that I have created a job with a typo (missing parenthesis before the CREATE) in it:
I run the job several times, but it fails. We can see this in the portal, or through Azure Cloud Shell:
We can confirm the failure by issuing the following query in Sql Server Management Studio when connected to the job database (this hosts my Elastic Jobs agent) directly:
SELECT *
FROM jobs.job_executions
WHERE job_name = 'demo123'
ORDER BY start_time DESC
Using any of these three methods confirms that the job failed. So, the first step is to drill down onto the failure message by executing the following query:
SELECT last_message
FROM jobs.job_executions
WHERE job_name = 'demo123' AND step_name <> 'NULL'
This yields the following error message:
| Failed to determine members of SqlServerTarget (server name 'targetserver', server location 'targetserver.database.windows.net'): Cannot open server 'targetserver' requested by the login. Client with IP address '13.89.57.50' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect. (Msg 40615, Level 14, State 1, Line 65536) |
Fixing the Firewall Rules
This error means that the Elastic Job Agent cannot connect to the target server(s) because the target has some firewall rules blocking the connection requests. Indeed - it is required that every target in the target group allows connections from Azure Services in order for Elastic Jobs to work. To fix this, I go to the target server in the Azure Portal and click on the “Firewalls and virtual networks” item under “Security”. Next, I toggle the "Allow Azure services" from OFF to ON, and save my changes.
Fixing Typos or Syntax Errors in Job Steps
Now, we re-execute the job, but the execution fails again. If I use SQL Server Management Studio to execute the same query as before, I can get the most recent failure message. This time, the error is:
| Command failed: Incorrect syntax near the keyword 'create'. (Msg 156, Level 15, State 1, Line 1) |
Recall that we expected this, since we knew there was a typo in the T-SQL used to define the step. I am going to update the T-SQL with the correct parenthesis. There are two ways to update a job step when you want to make this kind of a change - T-SQL or PowerShell. If you are using T-SQL, you will want to use the stored procedure jobs.sp_update_jobstep . In PowerShell you will use the Set-AzSqlElasticJobStep command from the specific job step you want to change. I am going to update my job step using PowerShell. To do this, first we need to bind the correct T-SQL text, then bind the specific job step we want to change, and then update it:
:\> $sqlText1 = "if not exists (select * from sys.Tables where object_id = object_id('T')) create table T (a int, b int)"
:\> $js = $job | Get-AzSqlElasticJobStep -Name step1
:\> $js | Set-AzSqlElasticJobStep -CommandText $sqlText1
Now that the job step has been updated, we wait for all running instances of the job to complete (if there are any) and re-run the job. And, of course, check on its execution.
:\> $job | Start-AzSqlElasticJob
Next, we can check the status by executing our query about job executions from Management Studio. Note the job_version is now 2 instead of 1 (because of the update). The job is running, and can complete successfully.
This post concludes my series covering Elastic Jobs on Azure SQL Database. I hope that you found it informative and helpful.
This blog is part of a series about Elastic Jobs on Azure SQL Database.
Code samples are also available on GitHub.
- Elastic Jobs in Azure SQL Database – What and Why
- Fundamental Concepts for Elastic Jobs in Azure SQL Database
- Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in PowerShell
- Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database using T-SQL
- Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database
- Troubleshooting Common issues with Elastic Jobs in Azure SQL Database