Job Scheduling with SQL: More Options than SQL Server Agent

Most developers and database administrators only consider SQL Server Agent when job scheduling with SQL comes to mind. While SQL Server Agent is the native tool available for Microsoft’s SQL Server, there are third-party job scheduling software solutions offering more advanced capabilities and improved task management for large teams and enterprises.

What is SQL? 

SQL stands for Structured Query Language and is a programming language designed for managing and manipulating relational databases. It provides a standardized way to interact with databases and perform operations like retrieving, updating, and deleting data. SQL is widely used across different database management systems and understanding how to use it is a fundamental skill for database professionals.

SQL Server

Microsoft’s SQL Server is a popular relational database management system (RDBMS). It provides robust and scalable solutions for storing, retrieving, and managing data. Through a comprehensive suite of tools and services, SQL Server offers efficient ways for teams to schedule and automate jobs.

SQL Server Agent 

SQL Server Agent is a built-in job scheduling and automation tool provided by Microsoft SQL Server. It allows database administrators (DBAs) to automate various tasks, including backup and maintenance jobs, data extraction and transformation processes, and other routine operations. SQL Server Agent service integrates seamlessly with the SQL Server database engine and provides a centralized platform for managing job schedules.

Scheduling Jobs in SQL Server 

Database administrators can schedule jobs using SQL Server Agent through the SQL Server Management Studio (SSMS) interface. With SSMS, SQL Server Agent jobs can easily be created, modified, and managed. The process typically involves the following steps:

1. Launch SSMS: Open SQL Server Management Studio and connect to the SQL Server instance for the job schedule.  
2. Navigate to SQL Server Agent: Expand the SQL Server instance node to find the “SQL Server Agent” node. Right click on the node and select “New Job” to create a new job.
3. Configure job properties: Provide a job name and specify the owner (usually a DBO or a specific user). Define job steps to represent individual tasks to be executed as part of the job.
4. Define job schedules: Set the job schedule by specifying the start time, end time or end date, and frequency of execution, such as daily frequency. SQL Server Agent offers various new schedule types, including one-time, recurring, and more advanced options.
5. Set notifications: Configure notifications to receive alerts when  jobs complete or encounter issues. Notifications can be received via email or other means.
6. Add optional advanced job scheduling features: SQL Server Agent allows for advanced job schedule functionality to perform complex operations and automate tasks. Features include executing SQL scripts, stored procedures, PowerShell scripts, and SSIS packages as part of a job. 
7. Save and enable the new job: Once all the configurations are complete, save the job and enable it to activate the schedule. The SQL Server Agent service will take care of executing the select job according to the defined schedule.
   

To schedule a job for a SQL query for daily frequency as noted in step 4 above, take the following steps: 

1. Select the “Steps” page on the left-hand side of the new job window. Click on the “New” button to define new job steps. 
2. In the new job step window, provide a job step name in the “Step name” field, 
3. In the “Database” field, select the SQL server database for executing the SQL query. 
4. In the “Type” section, select “Transact-SQL script (T-SQL)” to specify execution of an SQL query. 
5. In the “Command” field, enter the SQL query for scheduling. To schedule multiple SQL queries or commands in the same job, click on the “New” button to add more job steps. 
6. Select the “Schedules” page on the left-hand side of the new job window. Click on the “New” button to define a job schedule. 
7. In the new job schedule window, add a schedule name in the “Name” field. Note the assigned Job ID (job_id). 
8. Set daily frequency of the job schedule by selecting the “Daily” option under the “Frequency” section. Alternatively, users can use the freq interval (daily) and active_start_time parameters.  
9. Specify the start date in the “Start date” and the start time in the “Start time” field.
10. Set an end date for the job if applicable in the “End date” field. This field can be left blank if the job should run indefinitely. Alternatively, users can use the end_time parameter. 
11. Set the “Occurs once every field” to “1” to indicate the job runs on a daily frequency. 
12. Retrieve the schedule ID (schedule_id) for the newly created schedule. 
13. Save the schedule by clicking “OK”, and click on the “OK” button in the new job window to save the job. 

To associate the schedule with a job using the command line, execute the following T-SQL command:

USE msdb;

INSERT INTO dbo.sysjobschedules (job_id, schedule_id)
SELECT job_id, <Schedule_id>
FROM dbo.sysjobs
WHERE job_id = <Job_id>;

Replace <schedule_id> above with the schedule ID from step 12 above and the <job_id> with the Job ID from step 7. 

Next, execute the T-SQL command to active the job:

EXEC dbo.sp_update_job @job_id = <Job_id>, @enabled = 1;

Replace <job_id> with the Job ID from earlier. The job is now scheduled to run according to the specified SQL script and schedule settings. The script will be executed at the defined frequency and start time, utilizing any included parameters like Active_start_time, End_time, Sysjobs, Sysjobschedules, Sysschedules, Freq_interval, and Freq_subday_type.

ActiveBatch Job Schedule Software 

While SQL Server Agent provides native job scheduling capabilities for SQL, there are other tools available for teams requiring more advanced features for scheduling jobs and automating workflow tasks.

ActiveBatch is a comprehensive job scheduling software that offers enhanced features and integration options. Users can schedule and manage jobs within SQL Server and across other systems, including analysis services, Azure, and Linux environments. 

The centralized platform in ActiveBatch gives database administrators and execs one interface for designing, monitoring, and controlling job schedules. With ActiveBatch, teams can easily automate complex workflows, manage dependencies between jobs, and monitor job activity and job history.

With a user-friendly interface, ActiveBatch allows for intuitive job creation and configuration. ActiveBatch supports various scripting languages, including T-SQL (Transact-SQL), PowerShell, and supports various APIs, providing flexibility for executing job steps. Extensive troubleshooting and error-handling capabilities helps teams ensure a job runs smoothly and reliably.

ActiveBatch offers features like parameter passing, notifications, and customizable dashboards for monitoring job runs and performance metrics. The powerful scheduling engine allows admins to define schedules based on factors like CPU utilization, specific dates, or run time intervals.

Frequently Asked Questions