How to create SQL Job in Azure SQL Database

As you know with creating the SQL Job you’ll be able to schedule and run statements and T-SQL commands whenever you want, housekeeping and rebuilding the index are good examples as you don’t want to run these kind of commands during the business hours but do you know as we have not the agent in Azure SQL Database how we create and manage the job?

In this topic I’ll try to create the simple job in Azure SQL Database step by step!

1- Create Elastic Job Agents.

From Azure Portal go to “Elastic Job agents” then create the first agent.

It’s very simple step, you need to choose the name for agent then choose the database which you already created. It takes only a few minutes.

If you go back to “Elastic job agent” page you’ll see the agent which you created. You might need to refresh the page to see new item.

If you click on the job, you’ll be able to see some details like jobs, groups and latest 100 executions, I’ll show you this section later.

2- Create Master Key

You need to create Master Key in the database, see the referral link hereif you already have master key in your server, you can skip this step.

 
CREATE MASTER KEY

3- Create Credential

Open SSMS, connect to the database with admin user and with the following statement create the credential.

 
CREATE DATABASE SCOPED CREDENTIAL jobcredential2022
WITH IDENTITY = 'jobcredential2022',
SECRET = 'TestSS2022@'

You can check credential that you created with the following T-SQL command.

 
SELECT * FROM sys.database_scoped_credentials

4- Create new Login in Master database

You need to create new login in Master database, here is the T-SQL command.

 
CREATE LOGIN jobcredential2022
    WITH PASSWORD = 'TestSS2022@';  
GO
ALTER SERVER ROLE ##MS_DatabaseConnector## add member [jobcredential2022]

5- Create the user in User database.

With the following command I’ll create the user for the login which I created in step 4.

 
CREATE USER jobcredential2022
    FOR LOGIN jobcredential2022
    WITH DEFAULT_SCHEMA = dbo
GO

6- Create Group and add Target group

Before creating the job you need to have a group, I’ll create 'au-group-job' as a group in my database with the following command.

 
EXEC jobs.sp_add_target_group 'au-group-job'

Then run the following command, You need to replace the credential that you created in previous steps and the server name.

 
EXEC jobs.sp_add_target_group_member 'au-group-job',
@target_type = 'SqlServer',
@refresh_credential_name='jobcredential',
@server_name='au-east-server.database.windows.net'

With two following command you can check what you created in this step, they’re useful if you do troubleshooting and if you’re unsure about the target group and member!

 
SELECT * FROM jobs.target_groups
SELECT * FROM jobs.target_group_members

8- Permission to the user

Give the permission for the user which you created in step 5

 
EXEC sp_addrolemember N'db_owner', N'jobcredential2022'

9- Create the Job with T-SQL

You need to create the job with T-SQL command, in the following statement I’ll create new job and I want to execute sp_updatestats. We already created the other items like credential and group name.

 
EXEC jobs.sp_add_job @job_name='MyHousekeeping', @description='Print statement'
EXEC jobs.sp_add_jobstep @job_name='MyHousekeeping',
@command=N'EXEC sp_updatestats;  ',
@credential_name='jobcredential2022',
@target_group_name='au-group-job'

You can see the job that you created with the following command or if you go to the portal, you can see same result from Elastic Jon agent page and Jobs section.

 
SELECT * FROM jobs.jobs

10- Run the job!

You can run the job with the following command.

 
EXEC jobs.sp_start_job 'MyHousekeeping'

11- Check the result

With the following T-SQL Command you could check the result for the job which you have executed in previous step.

 
EXEC jobs.sp_start_job 'MyHousekeeping'

If you go to Elastic Job Agent in Portal like the following screenshot you could see the Status and latest 100 job executions.

In the other options on the lest side you also could see Target groups, Credentials and other objects which you created in SSMS in previous steps.