Azure Data Studio (ADS) is a lightweight IDE built on Visual Studio Code. I've written a few articles on how the tool works, and this one continues the series. In this article, I want to look at the way SQL Agent is managed inside the tool, both in the dashboard and the extension. A future article will look at the Notebook jobs separately as they are an enhancement only in ADS.

The other articles in this series on ADS works are listed in The Mastering Azure Data Studio Series.

• Getting Started with Azure Data Studio
• Getting Comfortable Writing Code in Azure Data Studio
• Using Notebooks in Azure Data Studio
• Using Azure Data Studio with TFVC
• Using Azure Data Studio with Git
• Editor Tips and Tricks for Azure Data Studio
• Using the Import Extension in Azure Data Studio
• The Profiler Extension in Azure Data Studio
• Server Dashboards in Azure Data Studio

You can download the tool and read those articles to learn about how to use this in writing code. I've gotten more comfortable with the tool, and while I still like SSMS, there are some features in ADS that I prefer.

The SQL Agent Extension

In the Extensions blade, you can search for the SQL Agent extension. You can see it below.

I've installed it, so I see Uninstall on the button, but if you haven't added this, click Install. You can read about the extension, but there isn't much information. There also isn't much on the MS Doc page.

The Server Dashboard and SQL Agent

In a previous article, we covered the Server dashboard and lightly showed the SQL Agent section. Now let's look at this section in more detail. As you can see below, there is a SQL Agent menu item on the left side. This is selected and we can see our SQL Agent jobs listed.  I see a few categories at the top and then a list of the jobs with a couple of options. I also see some metadata about the jobs. These are the columns shown:

• Name
• Last Run
• Next Run
• Enabled
• Status
• Category
• Runnable
• Scheduled
• Last Run Outcome
• Previous Runs

A few of these are shown in the image below.

To the right, one neat thing is a graph of the last few runs. This is similar to a lot of build and release tool dashboards that show the recent history. This lets us see at a glance not only the success or failure, but a duration of the jobs. The higher the bar, the longer things ran. This isn't an exact measurement to scale, but it is a quick glance of short or long running jobs. Red is failure, though I wish this were visually a different look since I know color blind people might find issues with this.

This is the same list of jobs that I see in SQL Agent in Management Studio (SSMS). The same server under SQL Agent shows this, but I can't easily see some of the meta data in the group. I'd need to look at each job.

This is one of the areas where a dashboard makes SQL Server administration easier in ADS than SSMS. There aren't many of these areas, but I like seeing this dashboard.

Let's look at the sub areas that we can access in ADS.

Managing Jobs

The main dashboard we see is the jobs area. We've seen the image above, and hopefully you noticed the two options you have: refresh and new job. Refresh does what you might expect, queries MSDB for a current list of jobs and metadata. Let's look at New Job.

When we click this, a blade opens on the right side. It's blank, and looks like this.

This is similar to the dialog in SSMS. I've put them both below, and they have almost all the same sections, though SSMS includes Target. That's for a multi-server job, which ADS doesn't manage. However, all the other items are there, though they are down the left menu in SSMS and along the top in ADS.

I won't go into the details of using SQL Server Agent, and there are plenty of articles on SQL Server Central about using the system. Instead, I'll show quickly how to add a new job.

Each job needs a name, and by default, the owner is the person who created the job. Categories are a way of grouping jobs, though this is only a metadata grouping. You can also add a detailed description, which can be handy for other team members. I'll add a few items for a new job that updates statistics using Ola's scripts.

Once this is done, I click the Steps item at the top and see a list of my steps. I don't have any to start.

I had to scroll down to find the new step button. You can see this below, and see the grayed out options for working with steps.

Once I click New Step, I get another dialog where I can fill in details. Once done, I clicked Parse (not shown, you need to scroll down) and I see the success message at the top.

I click OK at the bottom, and I have a step.

I could add more steps, reorder them, etc. but that's basic Agent stuff. Let's move on.

If I click Schedules, I don't see anything as this is a new job. At the bottom there are only two buttons: Pick Schedule and Remove Schedule. This is different than SSMS where I have the ability to create a new schedule. I'll choose Pick and I see this:

This the same list of schedules I'd see in SSMS if I choose Manage Schedules in SQL Agent (shown below). This means I can only choose a schedule and not add a new one or edit one. This limits ADS, but if you're working in a mature environment, you can pick a schedule.

After I have a schedule, I can click Alerts. This is the SQL Agent Alerts system that is in SSMS. I don't have anything, and again, I need to scroll down to find the New Alert button. Once I pick that, I get a blank dialog. The General options are the name, the enabled option, the type, and then what databases and severity to assign to this. Again, this is standard SQL Server DBA stuff.

The Response options allow me to execute another job (take other actions or do some general alerting) or notify an operator. I have no operators on this system, so this isn't enabled.

The Options tab has the same options as SSMS to decide what happens with this alert.

I clicked OK to save this, but I see this.

That's strange because I see the alert in SSMS. I guess there is an ADS extension bug? If anyone knows, let me know.

The Notifications tab has the standard notify options, but apart from the checkboxes, I couldn't get any drop downs to work. Not sure what's wrong here.

At this point I saved the job, and it appears in my list.

If I click the job, I get some minor details. I have the ability to run, stop, refresh or edit the job. I tried editing, and still did not see the alert or could get drop downs on notifications to work.

I clicked Run and after a few minutes clicked Refresh. I saw this, which was good.

It appears the job section works fairly well if you've done some setup in SSMS. If you haven't, you can't add schedules, so you need to get SSMS installed for that.

Let's move on. I'll skip Notebooks as that deserves its own article.

Alerts

The Alerts item, surprisingly, shows my Alert.

I'm guessing a bug in the job dialogs. In any case, I can see my alert. If I click it ... nothing. I can't edit or see more details. Let's try right clicking, and I get a choice of edit or delete. Let's select Edit and I'll see the same options as above in the job setction.

I'll click New Alert and I get a dialog that also looks like the one above. I added some stuff in here.

I then picked Response and again, I don't see operators, even though I added some in SSMS, and the checkbox doesn't work.

I clicked OK to save it and got an error:

So far the extension doesn't seem great, despite the fact this has been out for a long time. I guess people aren't using it?

Operators

When I click the Operators tab, I see the two I created.

If I click New Operator, I get a dialog. I'll add Louis.

These options look like what is in SSMS. In the Notifications, there doesn't appear to be anything, though I probably need to edit stuff in SSMS to get something to appear here. I'll try that with myself for another job. If I click OK, I see Louis added, and what's more, I get a red bar that shows this operator is disabled. Again, I'd prefer a visual indicator for color blind people.

I'll right click myself and I see the option to edit or delete.

I get the edit box and I'll change the name.

Once I do this and click OK, I see something strange. A new operator was added. Edit did a New. That's not good.

I assume this is some bug, but I haven't seen a place for feedback on this. I edited Grant as well, and after I check SSMS, there are copies of operators.

That's annoying, and limits the usefulness of ADS for these tasks with this extension.

Proxies

The last section in the extension to look at is the proxy one. If you aren't familiar with proxy accounts, they are a way to run a process under another credential. By default SQL Agent jobs run as the Agent service account. To use a different account, you need a proxy.

I don't have any proxies in SSMS, shown below. However, note that SSMS lists the categories where you can create proxy accounts.

In ADS, I don't see anything, no folders showing categories.

I can create a proxy in SSMS (and a credential to go with it). I'll grant this proxy access to the CmdExec system. When I refresh ADS, I see this:

If I right click this, I get the menu you'd expect: edit or delete. I click Edit and nothing happens. I click Delete and get asked to confirm a delete. I tested this and it deletes the proxy from SSMS as well.

If I click "New proxy" nothing happens either, so I suspect this subsystem was never really completed.

Summary

In this article, we looked at how to manage SQL Agent from within ADS. We have a number of options and some of the sections work well, some didn't work at all for me. I do like the view of the jobs, which lets me see the recent history of executions. The basics of using alerts and operators work, though proxies do not.

Overall, this isn't a replacement for SSMS, but it does give you some views into the Agent from ADS and if you need to do simple tasks, like kick off a job, this works well.