{"id":2781,"date":"2021-05-31T09:15:34","date_gmt":"2021-05-31T06:15:34","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2781"},"modified":"2025-11-03T17:16:28","modified_gmt":"2025-11-03T14:16:28","slug":"pos-mporoyme-na-programmatisoyme-ergasies-se-azure-sql-databases-me-ti-chrisi-elastic-database-jobs","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos_mporoume_na_programmatisoume_ergasies_se_azure_sql_databases_me_ti_xrisi_azure_database_elastic_jobs\/","title":{"rendered":"How we can schedule jobs on Azure SQL Databases using Elastic Database Jobs"},"content":{"rendered":"

As we know in Azure SQL Databases<\/strong> there is no SQL Server<\/strong> Agent<\/strong>, which means we can't run scheduled tasks through it. Nevertheless on\u00a0Azure SQL Databases\u00a0<\/strong>we can schedule jobs in many other ways such as using\u00a0Azure Data Factory<\/strong>,\u00a0Elastic Database Jobs<\/strong>\u00a0and\u00a0Logic Apps<\/strong>. <\/p>\n\n\n\n

In previous article<\/a> we had seen how they work Logic Apps<\/strong>. In Logic Apps, however, we have to connect one by one and create a workflow for each case.<\/p>\n\n\n\n

In this article we will see how we use them Elastic Database Jobs<\/strong>. In contrast to the Logic Apps<\/strong> Job execution can be done from a single base to a group of bases. Also the creation, management and monitoring of Jobs is done with code Transact SQL<\/strong>.<\/p>\n\n\n\n

Specifically, we will see how to create a Job that will run once a week Rebuild the Indexes in all bases of a target group that we have defined.<\/p>\n\n\n\n

The example<\/h2>\n\n\n\n

To begin with, we connect to Azure Portal<\/a>. Like prerequisite <\/em>is to have created an Azure SQL Database*<\/strong>which will manage all the Jobs and will be the one who will "click" on the Elastic Job agent<\/strong> (if we don't know how to make a base we see it here<\/a>).<\/p>\n\n\n\n

*The job db should be made in a basic DTU model and is always online and does not require high resources. Its cost in this case is quite low ~ 4 euros per month.<\/em><\/p>\n\n\n\n

Then we look for the service Elastic Job agents<\/strong>.<\/p>\n\n\n\n

\"\"
1<\/figcaption><\/figure>\n\n\n\n

Here we should define the name of the Agent and as Job database <\/em>her Azure SQL Database<\/em> that we have built that will manage the Jobs.<\/p>\n\n\n\n

\"\"
2<\/figcaption><\/figure>\n\n\n\n

The next step requires a little attention to understand the logic.<\/p>\n\n\n\n

We should create two credentials in the Job db, which, however, will not be for the database itself but for external use towards the rest (scoped credential<\/strong><\/em>).<\/p>\n\n\n\n

We will create these two credentials as logins <\/strong>in master db on the hosting logical Server <\/strong>Azure SQL Databases. Also, after creating the login for the master db, we should also create the user in it.<\/p>\n\n\n\n

Finally, we will have to connect to each of the bases that the Server contains and that's it create the user<\/strong> from the user login we created in the master db.<\/p>\n\n\n\n

\"\"
3<\/figcaption><\/figure>\n\n\n\n

So we open one SQL Server Management Studio <\/strong>the Azure Data Studio <\/strong>and we create them.<\/p>\n\n\n\n

To begin with, we connect to Job db <\/strong>to make them scoped credentials<\/strong>:<\/p>\n\n\n\n

USE [job-db]\nGO\n--Connect to the job database specified when creating the job agent\n\n-- Create a db master key if one does not already exist, using your own password.  \nCREATE MASTER KEY ENCRYPTION BY PASSWORD='Patatakia1!';  \n  \n-- Create a database scoped credential.  \nCREATE DATABASE SCOPED CREDENTIAL myjobcred WITH IDENTITY = 'myjobcred',\n    SECRET = 'userpass1!';\nGO\n\n-- Create a database scoped credential for the master database of server1.\nCREATE DATABASE SCOPED CREDENTIAL mymastercred WITH IDENTITY = 'mymastercred',\n    SECRET = 'masterpass1!';\nGO\n<\/pre>\n\n\n\n
Then we connect to master db<\/strong> of logical Server <\/strong>which hosts the Azure SQL Databases to create the logins and the user in the master db:<\/p>\n\n\n\n
-- At the target server containing the Databases\nUSE [master]\nGO\n\nCREATE LOGIN mymastercred with password = 'masterpass1!';\nGO\nCREATE USER mymastercred FROM LOGIN mymastercred;\nGO\nCREATE LOGIN myjobcred with password = 'userpass1!';\nGO<\/pre>\n\n\n\n
Finally we connect separately to each base you contain on the target Server<\/strong> and we will create the user from the login.<\/p>\n\n\n\n
However, since I want to rebuild indexes, I will also give the right required to the user to complete the process. I mean db_ddladmin <\/em>and view database state<\/em>:<\/p>\n\n\n\n
USE [dpgr_db]\nGO\nCREATE user myjobcred  FOR LOGIN myjobcred; \nGO\nALTER ROLE db_ddladmin ADD MEMBER myjobcred;\nGO\nGRANT VIEW DATABASE STATE TO myjobcred;\nGO\n\nUSE [mig_db]\nGO\nCREATE user myjobcred  FOR LOGIN myjobcred; \nGO\nALTER ROLE db_ddladmin ADD MEMBER myjobcred;\nGO\nGRANT VIEW DATABASE STATE TO myjobcred;\nGO\n\nUSE [job-db]\nGO\nCREATE user myjobcred  FOR LOGIN myjobcred; \nGO\nALTER ROLE db_ddladmin ADD MEMBER myjobcred;\nGO\nGRANT VIEW DATABASE STATE TO myjobcred;\nGO<\/pre>\n\n\n\n
After we're done with the users we go back to the job-db and it's time to build it target group<\/strong> . The target group is the group of databases where the Job will be executed.<\/p>\n\n\n\n
In our case, we want it to run on as many bases as exist on the specific logical server.<\/p>\n\n\n\n
USE [job-db]\nGO\n\nEXEC jobs.sp_add_target_group 'ServerGroup1';\nGO\n\n-- Add a server target member\nEXEC jobs.sp_add_target_group_member\n'ServerGroup1',\n@target_type = 'SqlServer',\n@refresh_credential_name='mymastercred', --credential required to refresh the databases in a server\n@server_name='dpgr-srv.database.windows.net';\nGO\n\nSELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1';\nSELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';<\/pre>\n\n\n\n
We see with the results of the selects that they were created successfully.<\/p>\n\n\n\n
\"\"
4<\/figcaption><\/figure>\n\n\n\n
In the next step we create the Job in Job db<\/strong>.<\/p>\n\n\n\n
We should remember that the time he accepts it is in UTC time zone<\/strong> so for Greek time we should do -2 or -3 hours on the date we set (in our case I do it with the function DATEADD<\/em>).<\/p>\n\n\n\n
Also in the parameter schedule_interval_type <\/strong><\/em>we defineWeeks<\/em>' with schedule_interval_count <\/strong><\/em>1 to run once a week. As schedule_start_time <\/strong><\/em>is the date it will run for the first time.<\/p>\n\n\n\n
--Connect to the job database specified when creating the job agent\n\n--Add job for create table\n\nDECLARE\n@date DATETIME2;\nset @date = DATEADD(HOUR,-2,'20201226 04:00:00'); -- I am using DATEADD because it works on UTC time zone, but Greece has gmt+2 \/+3.\n\nEXEC jobs.sp_add_job @job_name='Rebuild Indexes'\n,@description='Rebuild Indexes'\n,@Enabled = 1\n,@schedule_interval_type  = 'Weeks'\n,@schedule_interval_count  = 1\n,@schedule_start_time  = @date;\n\n\/* for schedule_interval_type the options\n'Once',\n'Minutes',\n'Hours',\n'Days',\n'Weeks',\n'Months'\n*\/\n<\/pre>\n\n\n\n
After we have created and scheduled the Job, we should add the job step<\/strong> in the job that will have the code we want to run (in the parameter command<\/strong><\/em>). In our case, the rebuild of the indexes.<\/p>\n\n\n\n
We do not forget that we should define the target_group_name <\/strong><\/em>which we had also created the user credential in credential_name<\/strong><\/em>.<\/p>\n\n\n\n
-- Add job step for create table\nEXEC jobs.sp_add_jobstep @job_name='Rebuild Indexes',\n@credential_name='myjobcred',\n@target_group_name='ServerGroup1',\n@step_name='run rebuild',\n@command=N'\nDECLARE @Indexes TABLE\n   (\n      ObjectSchema SYSNAME,\n      ObjectName SYSNAME,\n      IndexName SYSNAME,\n      FragmentationPCT FLOAT,\n      Command AS (REPLACE(REPLACE(REPLACE(N''ALTER INDEX {IndexName} ON {ObjectSchema}.{ObjectName} REBUILD;'',N''{IndexName}'',IndexName),N''{ObjectSchema}'',ObjectSchema),N''{ObjectName}'',ObjectName))\n   )\n;\nINSERT INTO @Indexes\n(\n    ObjectSchema,\n    ObjectName,\n    IndexName,\n    FragmentationPCT\n)\nSELECT QUOTENAME(SCHEMA_NAME(SO.schema_id)) AS ObjectSchema,\n   QUOTENAME(SO.name) AS ObjectName,\n   QUOTENAME(I.name) AS IndexName,\n   IPS.avg_fragmentation_in_percent\nFROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,N''Limited'') AS IPS\nJOIN sys.objects AS SO ON SO.object_id = IPS.object_id\nJOIN sys.indexes AS I ON I.index_id = IPS.index_id\n   AND I.object_id = IPS.object_id\nWHERE IPS.avg_fragmentation_in_percent > 15\nORDER BY IPS.avg_fragmentation_in_percent DESC\n;\n \nDECLARE @TsqlCommand NVARCHAR(MAX);\n \nDECLARE IndexRebuilder CURSOR LOCAL FAST_FORWARD\n   FOR\n   SELECT I.Command\n   FROM @Indexes AS I\n;\n \nOPEN IndexRebuilder\n \n   FETCH NEXT FROM IndexRebuilder\n   INTO @TsqlCommand\n   ;\n \n   WHILE @@FETCH_STATUS = 0\n   BEGIN\n      \n      EXEC sp_executesql @Command = @TsqlCommand;\n \n      FETCH NEXT FROM IndexRebuilder\n      INTO @TsqlCommand\n      ;\n   END \nCLOSE IndexRebuilder\nDEALLOCATE IndexRebuilder\n;'<\/pre>\n\n\n\n
We are ready to test. Now either we wait for it to run when we have scheduled it or we start it manually with the command:<\/p>\n\n\n\n
--to run manually\nexec jobs.sp_start_job 'Rebuild Indexes'<\/pre>\n\n\n\n
In the Agent service in the Azure Portal, we can see the execution history of the Jobs.<\/p>\n\n\n\n
\"\"
5<\/figcaption><\/figure>\n\n\n\n
As also on the tab Jobs<\/em>, the Jobs that exist.<\/p>\n\n\n\n
\"\"
6<\/figcaption><\/figure>\n\n\n\n
The same information can be seen with the T-SQL query in the Job db:<\/p>\n\n\n\n
-- View all jobs executions\nSELECT job_execution_id,job_name,lifecycle,start_time,create_time,last_message,target_type,target_server_name,target_database_name,target_elastic_pool_name,is_active FROM jobs.job_executions\nORDER BY start_time DESC\nGO\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If we want to stop a Job after finding first of the job_executions <\/em>the job_execution_id<\/em>, we can with the following query:<\/p>\n\n\n\n
-- View all active executions to determine job execution id\nSELECT * FROM jobs.job_executions\nWHERE is_active = 1 AND job_name = 'Rebuild Indexes'\nORDER BY start_time DESC\nGO\n\n-- Cancel job execution with the specified job execution id\nEXEC jobs.sp_stop_job 'D85DF748-F940-4FD3-AFB4-D7E892ABBDF4'<\/pre>\n\n\n\n
We can also delete the execution history of a Job or delete the entire Job.<\/p>\n\n\n\n
--clean job history\nEXEC jobs.sp_purge_jobhistory @job_name = 'Rebuild Indexes'\n\n--delete the job\nEXEC jobs.sp_delete_job @job_name='Rebuild Indexes'<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n