7 minutos de leitura
When using the Azure SQL Database, since we don’t have access to the instance and the SQL Server Agent, were gonna need to use a new service available in the Azure: Elastic Jobs, a job Scheduling services that execute custom jobs on one or many databases in Azure SQL Database.<br> The service is in the preview mode,so don’t expect to have full support on this, but if you need to execute maintenance tasks or T-SQLs on a schedule, it’s the most reliable option at the moment.</p><div id="attachment_4501" class="wp-caption aligncenter"><img aria-describedby="caption-attachment-4501" class="wp-image-4501 size-full" src="https://ilegra-site-cms-strapi.s3.us-east-1.amazonaws.com/Azure_SQL_Database_figura_1_544335124e.png" alt="" width="545" height="344" srcset="https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-1.png 545w, https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-1-300x189.png 300w, https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-1-500x316.png 500w" sizes="(max-width: 545px) 100vw, 545px"><p id="caption-attachment-4501" class="wp-caption-text">(https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview?view=azuresql)</p></div><h3>1 – Create a SQL database to host the agent</h3><p>For this to work, first we need to create a database to host our agent db separated from our target database.<br> I created a database named sqlagent in a new host to be the repository.</p><p><img class="aligncenter size-full wp-image-4502" src="https://ilegra-site-cms-strapi.s3.us-east-1.amazonaws.com/Azure_SQL_Database_figura_2_fb5e20bb9e.png" alt="" width="292" height="340" srcset="https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-2.png 292w, https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-2-258x300.png 258w" sizes="(max-width: 292px) 100vw, 292px"></p><h3>2 – Create an Elastic Job Agent</h3><p>With the database created let’s create our Elastic Job Agent.<br> Here well choose a name for the agent / select the subscription and select the database to be used:
Resource created:
With this done, now we can configure our first job to be scheduled.
But first, we have to execute a few steps in our agent and target databases:
Connect to the SQLAGENT database and run the following commands:
— Create a database master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD=’xxxxxxxxx‘;
— Create two database scoped credentials.
— The credential to connect to the Azure SQL logical server and execute the jobs
CREATE DATABASE SCOPED CREDENTIAL agent_credential WITH IDENTITY = ‘agent_credential‘,
SECRET = ‘xxxxxxx‘;
GO
— The credential to connect to the Azure SQL logical server, to refresh the database metadata, if there’s a change in the database configuration, for instance: a new database is added to the server.
CREATE DATABASE SCOPED CREDENTIAL master_credential WITH IDENTITY = ‘master_credential‘,
SECRET = ‘xxxxxxxx‘;
GO
Now we need to create the logins in the database were going to use to run our jobs. It’s very important to keep the same password used to create our scoped credential in the agent database.<br> Connect to the target database:</p><p>In the master database :</p><p><strong>create login</strong> agent_credential <strong>with password</strong>=’<strong>xxxxxxxxxx</strong>‘;<br><strong>create login</strong> master_credential <strong>with password</strong>=’<strong>xxxxxxxxxxx</strong>‘;<br><strong>create user</strong> master_credential for <strong>login </strong>master_credential;</p><p>In the labdatabasejobs database:</p><p><strong>create </strong>user agent_credential for<strong> login</strong> agent_credential;</p><p>And here I’ll grant dbowner so the user can have full access on the database:</p><p>EXEC sp_addrolemember N’db_owner’, N’agent_credential’<br> GO</p><p>If I had more databases in this server, I would need to create the agent_credential in all of them.</p><p><img class="aligncenter size-full wp-image-4505" src="https://ilegra-site-cms-strapi.s3.us-east-1.amazonaws.com/Azure_SQL_Database_figura_5_57981121bf.png" alt="" width="315" height="657" srcset="https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-5.png 315w, https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-5-144x300.png 144w, https://ilegra.com/wp-content/uploads/2022/10/Azure-SQL-Database-figura-5-240x500.png 240w" sizes="(max-width: 315px) 100vw, 315px"></p><h3>5 – Create a target group</h3><p>In the SQLAGENT database we need to create a target group and add the databases that well use to run our job.
We can add all the databases hosted in the server at once, then, even if new ones are created after the setup, they will be added automatically (with our master_credential updating this), or we can include only the database where the job needs to be executed.
— Connect to the job database specified when creating the job agent
— Add a target group containing server(s)
EXEC jobs.sp_add_target_group ‘DBGROUP1’;
– If we want to add all the databases:
— Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = ‘DBGROUP1’,
@target_type = ‘SqlServer’,
@refresh_credential_name = ‘master_credential’, –credential required to refresh the databases in a server
@server_name = ‘labdatabasejobs.database.windows.net’;
– If we want to add only one database:
EXEC jobs.sp_add_target_group_member
@target_group_name = ‘DBGROUP1’,
@membership_type = ‘Include’,
@target_type = ‘SqlDatabase’,
@server_name = ‘labdatabasejobs.database.windows.net’,
@database_name = ‘labdatabasejobs’;
You see that by choosing to add a single database we dont use the @refresh_credential_name parameter, since it doesnt matter how many databases are in the server.
– If we want to exclude one database from the target:
EXEC jobs.sp_add_target_group_member
@target_group_name = ‘DBGROUP1’,
@membership_type = ‘Exclude’,
@target_type = ‘SqlDatabase’,
@server_name = ‘labdatabasejobs.database.windows.net’,
@database_name = ‘labdatabasejobs’;
–Views to check the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name=’DBGROUP1;
SELECT * FROM jobs.target_group_members WHERE target_group_name=’DBGROUP1′;
For this lab I created the following table in the target database:
And now Im gonna create a job to insert data in this table in a five minute interval daily:</p><p><strong>–Connect to the job database</strong></p><p><strong>–Add job for insert</strong></p><p><strong>EXEC</strong> jobs.sp_add_job @job_name = ‘<strong>InsertTableTest</strong>‘, @description = ‘<strong>Job to insert current date</strong>‘;</p><p><strong>— Add job step</strong><br><strong>EXEC </strong>jobs.sp_add_jobstep @job_name = ‘<strong>InsertTableTest</strong>‘,<br> @command = <strong>N’insert into test values (getdate());</strong>‘,<br> @credential_name = ‘<strong>agent_credential</strong>‘,<br> @target_group_name = ‘<strong>DBGROUP1</strong>‘;</p><p>– With the job created, Ill add a schedule:
EXEC jobs.sp_update_job
@job_name = ‘InsertTableTest‘,
@enabled=1,
@schedule_interval_type = ‘Minutes‘,
@schedule_interval_count = 5;
Now let’s see our job created and the execution log:
In the portal we can also see information about the job:
That’s it.
Now you can run maintenance routine tasks / Run T-SQLs on a schedule in a single Azure SQL Database without the SQL Agent.
You can do the administration by T-SQL and verify in the portal the status of the jobs.
We hope that soon this service will be in general availability
