Add SQL Server Agent Management to SQL Operations Studio

By: Rajendra Gupta |  |   Related Tips:More > SQL Operations Studio

Problem

SQL Operations Studio is a new tool that runs on Windows, macOS, and Linux, and aims to simplify management for SQL Server on Windows, Linux, Azure SQL Database, and Azure SQL Data Warehouse. SQL Operations Studio provides support for extensions, which is a nice way to extend features and enhancements. Let's explore how to setup SQL Server Agent for SQL Operations Studio.

Solution

Extensions in SQL Server are a way to provide add-on functionality by Microsoft as well as third party users. As we discussed earlier in my tips about SQL Operations Studio , below are the SQL Operations Studio versions.

Release Date Release Version Description November 15, 2017 0.23.6 Initial release of SQL Operations Studio December 19,2017 0.24.1 December Public Preview January 17,2018 0.25.4 January Public Preview February 15,2018 0.26.7 February Public Preview March 28,2018 0.27.3 March Public Preview April 25, 2018 0.28.6 April Public Preview May 7, 2018 0.29.3 May Public Preview June 20,2018 0.30.6 June Public Preview July 19, 2018 0.31.4 July Public Preview August 30, 2018 0.32.8 August Public Preview

To view the SQL Operations Studio version, go to Help > About.

.

Inthis previous tip we explored these extensions:

• Server Reports
• WhoIsActive Extension
• RedGate SQL Search Extension

SQL Operations Studio does not support SQL Server Agent by default.  I feel that withoutSQL Server Agent this tool is not complete. In this tip, we'll explore how setup the SQL Server Agent extension in SQL Operations Studio.

SQL Server Agent Extension

SQL Operations Studio provides supports for SQL Server Agent using the SQL Server Agent extension.

With this extension, we can:

• View SQL Server Agent Jobs Configured on a SQL Server
• View Job History with job execution results
• Basic Job Control to start and stop jobs
• Create or Edit SQL Server Agent Jobs
• View, Configure Operators, Alerts
• Create Proxy accounts

To install SQL Server Agent, click on the extension in the marketplace.

This opens a web page in a new tab that shows the basic details about the SQL Server Agent extension. Click on Install and Reload.

.

Now we can see that the SQL Server Agent Extension is installed.

In order to launch the SQL Server Agent extension, connect to the database instance, right click on the instance and select the Manage option.

We can see SQL Agent as a tab in the server dashboard.

Click on the SQL Agent tab and it opens the below window.

In the left sidebar, we can see these options:

1. Jobs
2. Alerts
3. Operators
4. Proxies

Let's explore the SQL Server Agent extension options and see how useful SQL Operations Studio can be.

Jobs in SQL Server Agent Extension

When we launch SQL Server Agent Extension, by default it opens up in the Job tab, which shows all the configured jobs for that particular instance and the history.

In the jobs dashboard, we can see the below important columns:

• Name : Job Name
• Last Run : Last execution time of the job.
• Next Run : If the job is scheduled, it shows the next run time of the job.
• Enabled : If the job is enabled, then yes else no
• Category : If we have defined a job category in Job configuration, it shows the category from the below list. The default value is [Uncategorized [Local]]. Categories help filter jobs based on a particular category.

• Scheduled : If the Job is scheduled then Yes else No.
• Last Run Outcome : The last outcome of the job execution. It can show these values: failed, succeeded, retry, canceled, in progress. The status of Unknown shows that the job has not been run.
• Previous Runs : An interesting and nice enhancement to view job run trends. This shows the graphical history of the job execution with Green and Red color bars.
  • Green Bars: For successful job execution
  • Red bars: For failed job execution

For example, in the below screenshot, we can see that two instances of the job failed while others were successful.

You can check the job execution time by placing the cursor over the bar.

You can also see the height of the bars are different. The height of the bar depends upon the job execution time. We can see the difference in the execution time of a job.

In the jobs, we can also see a bar on the left as shown below. This bar shows the last job run outcome.

To refresh the jobs dashboard, click on the Refresh icon towards the upper right corner.

Now let me run a job and make it fail to see the behavior in the dashboard.

We can see the below changes in the SQL Server Agent job dashboard.

• Last run outcome bar becomes Red
• This also shows the failed job reason
• Previous runs also adds a failed bar

If we want to view the detailed job history, click on the particular job. For example, below we can see the job history for the DB Backup Job.

Scroll down to the screen and we can see the step history for the job. Currently, there seems to be some formatting issue as we cannot scroll to the left and right to view the complete message. It should be fixed in future releases of SQL Operations Studio.

However, we can double-click on the message or use Ctrl+C to copy the message content.  We can paste this message in Notepad or another editor to analyze it.

Executed as user: NT SERVICE\SQLAgent$SQL2017. Cannot open backup device 'C:\mssqltips\backup\master.bak'. Operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Now I have created the backup folder in the desired location. Now, we can run the job from SQL Operations Studio itself. In the detailed job history for the particular job, there is an option to run the job.

Once the job is started, we get a pop message that the job started successfully.

Click on Run to execute the job and we get the message - DB Backup Job: the job was successfully started.

We can see the job is successful now.

Edit and Delete a Job using SQL Operations Studio

Right clicking on a job gives options to edit and delete the job.

Click on Edit Job to make changes to the job. This opens up the Edit Job window.

We can make changes to the job and save it. Suppose we want to make below change:

• Disable the job
• Mention the reason in the description
• Change Job category to Database Maintenance

In the edit job screen, change the category from the dropdown menu, put the description in the description text box and remove the checkbox from Enabled. Click OK to make the changes.

We can verify the changes as shown below.

Filter the SQL Server Agent Jobs

Suppose we have a large number of jobs in the database instance. It would be difficult to find a particular job from the long list. The SQL Operations Studio SQL Agent extension is intelligent in doing that. We can filter out the result from any column we want.

As shown below, each column has the option to filter the results.

We can filter the results based on the job name, last run, next run, Enabled, Status, Category, last run outcome.

Suppose I want to see only enabled jobs, so click on the icon near the Enabled column. This opens up the mini window to either sort the results in descending or ascending order. We can also select Yes (to see enabled jobs) and No (to see disabled jobs). We can also select all if we want to see all the jobs irrespective of the status.

Click on Yes to see the enabled jobs and click OK.

We can see only enabled jobs now based on the filter. The SQL Agent Extension also makes it easy to identify whether results are filtered out or not. It places a special symbol as shown below to identify that we have applied filter on this column.

To remove the filter, click on the icon placed near the filtered column and select all.

We can see all the jobs now, but still you can see the filtered icon beside the Enabled column.

In order to remove the filter icon, go back to filter option and click on Clear.

We can now see the icon is gone for the filtered column.

Delete a SQL Server Agent Job

If we want to remove a job from a SQL Server instance, right click on the particular job and click Delete Job. For example, let's remove Test Job from our instance.

This gives up the pop-up message to confirm deletion of the job operation or to Cancel it.

Click on Delete Job and it removes the job immediately.

In this tip, we learned about the SQL Server Agent extension and how to view, run, edit, and filter jobs from SQL Server Operations Studio.  In my next tip, we will explore creating jobs, operators, alerts, and proxies using the SQL Server Agent extension in SQL Operations Studio.

Next Steps

• Stay tuned for further tips on SQL Server Agent in SQL Operations Studio.
• Read more about New SQL Operations Studio Installation and Overview.
• Read more about SQL Operations Studio - Query Editor and Source Control.

Last Update: 2018-10-01

About the author