Manage schedules for regular maintenance jobs
In this tutorial we will learn about managing schedules for regular maintenance jobs.
However, you can create and schedule jobs that could be periodically executed against one or many databases to run Transact-SQL (T-SQL) queries and perform maintenance tasks. And, you can define target database or groups of databases where the job will be executed, and also define schedules for running a job. A job handles the task of logging in to the target database.
When to use automated jobs
There are several scenarios when you could use job automation:
Automate management tasks and schedule them to run every weekday, after hours.
- Firstly, deploy schema changes, credentials management, performance data collection or tenant (customer) telemetry collection.
- Secondly, update reference data (information common across all databases), load data from Azure Blob storage.
- Thirdly, rebuild indexes to improve query performance. Configure jobs to execute across a collection of databases on a recurring basis, such as during off-peak hours.
- Lastly, collect query results from a set of databases into a central table on an on-going basis. Performance queries can be continually executed and configured to trigger additional tasks to be executed.
Collect data for reporting
- Firstly, aggregate data from a collection of databases into a single destination table.
- Secondly, execute longer running data processing queries across a large set of databases, for example the collection of customer telemetry. Results are collected into a single destination table for further analysis.
Data movements
- Firstly, create jobs that replicate changes made in your databases to other databases or collect updates made in remote databases and apply changes in the database.
- Then, create jobs that load data from or to your databases using SQL Server Integration Services (SSIS).
SQL Agent Jobs
SQL Agent Jobs are a specified series of T-SQL scripts against your database. Use jobs to define an administrative task that can be run one or more times and monitored for success or failure. However, a job can run on one local server or on multiple remote servers. SQL Agent Jobs are an internal Database Engine component that is executed within the Managed Instance service. There are several key concepts in SQL Agent Jobs:
Job steps
SQL Agent Job steps are sequences of actions that SQL Agent should execute. Every step has the following step that should be executed if the step succeeds or fails, number of retries in a case of failure. Further, SQL Agent enables you to create different types of job steps, such as Transact-SQL job steps that execute a single Transact-SQL batch against the database. Or OS command/PowerShell steps that can execute custom OS script.
However, transactional replication is a Database Engine feature that enables you to publish the changes made on one or multiple tables in one database and publish/distribute them to a set of subscriber databases.
Job schedules
A schedule specifies when a job runs. More than one job can run on the same schedule, and more than one schedule can apply to the same job. However, a schedule can define the following conditions for the time when a job runs:
- Firstly, whenever Instance is restarted (or when SQL Server Agent starts). Job is activated after every failover.
- Secondly, one time, at a specific date and time, which is useful for delayed execution of some job.
- Thirdly, on a recurring schedule.
Job notifications
SQL Agent Jobs enable you to get notifications when the job finishes successfully or fails. You can receive notifications via email.
First, you would need to set up the email account that will be used to send the email notifications and assign the account to the email profile called AzureManagedInstance_dbmail_profile, as shown in the following sample:
You would also need to enable Database Mail on Managed Instance:
SQL
GO
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Database Mail XPs’, 1;
GO
RECONFIGURE
SQL Agent Job Limitations
Some of the SQL Agent features that are available in SQL Server are not supported in Managed Instance:
- Firstly, SQL Agent settings are read only. Procedure sp_set_agent_properties is not supported in Managed Instance.
- Secondly, enabling/disabling SQL Agent is currently not supported in Managed Instance. SQL Agent is always running.
- Thirdly, notifications are partially supported
- Pager is not supported.
- NetSend is not supported.
- Alerts are not supported.
- Next, proxies are not supported.
- Lastly, eventlog is not supported.
Elastic Database Jobs (preview)
Elastic Database Jobs provide the ability to run one or more T-SQL scripts in parallel, across a large number of databases, on a schedule or on-demand.
Run jobs against any combination of databases: one or more individual databases, all databases on a server, all databases in an elastic pool, or shardmap, with the added flexibility to include or exclude any specific database. However, jobs can run across multiple servers, multiple pools, and can even run against databases in different subscriptions. Servers and pools are dynamically enumerated at runtime, so jobs run against all databases that exist in the target group at the time of execution.
The following image shows a job agent executing jobs across the different types of target groups:
Elastic Job agent
An Elastic Job agent is the Azure resource for creating, running, and managing jobs. The Elastic Job agent is an Azure resource you create in the portal (PowerShell and REST are also supported). However, creating an Elastic Job agent requires an existing database in Azure SQL Database. The agent configures this existing database as the Job database.
Job database
The Job database is used for defining jobs and tracking the status and history of job executions. Moreover, the Job database is also used to store agent metadata, logs, results, job definitions. And, also contains many useful stored procedures and other database objects for creating, running, and managing jobs using T-SQL. For the current preview, an existing database in Azure SQL Database (S0 or higher) is required to create an Elastic Job agent.
However, if operations against the job database are slower than expected, monitor database performance and the resource utilization in the job database during periods of slowness using Azure portal or the sys.dm_db_resource_stats DMV. If utilization of a resource, such as CPU, Data IO, or Log Write approaches 100% and correlates with periods of slowness. Then, consider incrementally scaling the database to higher service objectives (either in the DTU model or in the vCore model) until job database performance is sufficiently improved.
Job database permissions
During job agent creation, a schema, tables, and a role called jobs_reader are created in the Job database. However, the role is created with the following permission and is designed to give administrators finer access control for job monitoring:
- Role name: jobs_reader
- ‘jobs’ schema permissions: SELECT
- ‘jobs_internal’ schema permissions: None
Target group
A target group defines the set of databases a job step will execute on. A target group can contain any number and combination of the following:
- Firstly, Logical SQL server – if a server is specified, all databases that exist in the server at the time of the job execution are part of the group. The master database credential must be provided so that the group can be enumerated and updated prior to job execution.
- Secondly, Elastic pool – if an elastic pool is specified, all databases that are in the elastic pool at the time of the job execution are part of the group. As for a server, the master database credential must be provided so that the group can be updated prior to the job execution.
- Thirdly, Single database – specify one or more individual databases to be part of the group.
- Lastly, Shardmap – databases of a shardmap.
Reference: Microsoft Documentation