Automation is one of the key techniques by which SQL Server DBAs attempt to tame their environment. Without the ability to automate at least some of their tasks, there would need to be many more members of database teams in all but the smallest SQL Server environment. It would be virtually impossible to manually maintain everything that needs to be done to keep the databases and applications running smoothly. DBAs would be required to spend a large part of their day repetitively typing commands to perform a wide variety of critical and trivial tasks.
The Microsoft brain trust responsible for the development of SQL Server understood this and realized that lack of reliable automation could pose a deterrent to widespread adoption of their product. Rather than count on homegrown or third-party tools to fill the gap, the SQL Server Agent was developed as an integral component of SQL Server. If you’re a SQL Server DBA, you should be taking advantage of this feature to make your life easier.
What is the SQL Server Agent?
The SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks in SQL Server. These tasks are called jobs in SQL Server and can be comprised of multiple steps. Each step performs a given task such as inserting a value in a table or backing up a database. SQL Server jobs can be started manually, in response to specific events, or based on a schedule.
SQL Server Agent uses four components to control how it executes administrative commands.
Jobs
Jobs are the basic unit of measure used by the SQL Server Agent. A job instructs SQL Server to perform a series of actions that can be monitored for success or failure. Each action is known as a job step and runs in a designated security context. A job can be run multiple times on either one or many remote servers. Jobs can be started in three ways:
- In response to an alert;
- According to a schedule;
- Manually by executing the sp_start_job stored procedure.
Schedules
Schedules define when a job runs. A single schedule can be associated with multiple jobs. You can also have multiple schedules that reference the same job. The conditions under which jobs are executed are:
- When the SQL Server Agent starts;
- At a specific date and time;
- On a recurring schedule;
- When CPU utilization reaches a level previously defined as idle.
Alerts
Alerts are automated responses to events such as a job starting or a resource threshold being met. Alerts respond to SQL Server events, SQL Server performance conditions, and Windows Management Instrumentation (WMI) on the host computer. An alert can run a job or notify operators.
Operators
Operators define the contact information for the parties responsible for SQL Server maintenance so they can be notified to take appropriate action based on the specific alert that initiated the notification.
Using these components enables the creation of a complex series of actions that can be encapsulated in SQL Server jobs. By defining job dependencies and nesting jobs, teams can build elaborate and intricate sets of jobs that perform many critical tasks.
Managing Many SQL Server Jobs
SQL Server jobs provide DBAs with an excellent opportunity to simplify their work life while maintaining well-behaving and performing databases. Creating jobs can boost productivity and enable the team to concentrate on more value-added activities. This strategy works fine until the number of jobs and their level of complexity becomes too great to be effectively managed manually.
An IDERA whitepaper addresses this issue and offers guidance on managing large numbers of SQL Server jobs. It’s a good read that concentrates on the benefits of:
- Centralizing the SQL Server jobs on dedicated servers;
- Utilizing the SSIS catalog;
- Using a third-party tool to manage and gain insight into the SQL Server job environment.
The information contained in the whitepaper will set you on the right path toward taming a complex SQL Server job environment. As far as the right third-party tool for addressing a complex environment, SQL Enterprise Job Manager is a perfect fit. It provides all the functionality required to get the maximum benefits out of SQL Server jobs.
Following are some of the features that make SQL Enterprise Job Manager a great tool for managing SQL Server jobs.
- Manage jobs across the entire enterprise from a centralized console.
- A familiar calendar view can be used to display job information across all instances with drill-down capability to obtain more details.
- Create, modify, and schedule jobs with just a few clicks to run on one or multiple SQL Servers.
- View job history and trends to identify scheduling issues, failed jobs, and those that run frequently or for a long time.
- Setup custom alerts and notifications so no one is surprised about SQL Server job issues.
SQL Enterprise Job Manager works with physical and virtual SQL Servers no matter where they are located. Monitor hybrid environments easily and maintain control over the SQL Server jobs that keep your systems running.