>> You're probably familiar with SQL Server Agent jobs,
but have you seen how you can create, maintain,
and manage them using Notebook Jobs in Azure Data Studio?
Learn more in this episode of Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman, and welcome to
this episode of Data Exposed MVP edition.
Today, I'm joined by an MVP Taiob.
Thanks so much for joining us today.
Can you tell us a little bit about what you do?
>> Thank you, Anna.
I'm a Data Platform MVP,
I have been working with
mostly Microsoft Data Stack for almost 15 years.
I started my journey on-premises and in
last four or five years moving into Cloud,
currently in a hybrid state.
Also, I do a lot of stuff with the community.
I'm engaged to user groups, SQL Saturdays,
and also due to pandemic speaking in
a lot of groups all around the world,
mostly remotely and on the topics of Data Platform.
>> Awesome, cool. Well, thank you for joining us
today, and thanks for all you do for our community.
I know it's a super-big part of
everything that happens in the Azure SQL and SQL Server worlds.
Thank you for that.
Today, we are going to be talking about something pretty new
to me called Notebook Jobs in Azure Data Studio.
I wanted to start by asking you a few questions.
People have been using,
maybe SSMS or other ways of managing
SQL Server Agent jobs for a long time.
Why or what benefits do folks get with
this new capability Notebook jobs?
>> Sure. I personally have been primarily be using
SQL Agent for scheduling jobs for years.
Recently, with Azure SQL database,
we'd been looking at other options.
But the primary advantage when I saw that I can
schedule a Notebook is to save the output.
To give you an example, you have a crisis,
you get up at morning two o'clock,
you're charging your issue,
you make some changes and you're gone.
Next day, sometime you don't remember.
Just early morning, two o'clock, you just got a call.
If all of the team members have these Notebooks shared with them,
pre-configured, they can run this pre and post and save that.
You can schedule that as a job.
Either it runs at a frequent interval,
or you can run it on demand.
If you have your configurations here to save it once a day,
once a week, then you can go
back and see those changes that what you change.
You can schedule a Notebook.
It will save your output.
It will save all the outputs for as long as you want to keep it.
That's the main advantage I found.
Folks, probably many people know,
many people might not know,
Tiger Team actually is putting all their scripts in
notebooks and sharing with the community for a similar purpose.
>> Got you. Notebooks are super useful.
I definitely agree with that because of the saving of the outputs.
But this other capability is the ability to
schedule them and store the results in maybe different places.
Does this functionality come built into Azure Data Studio?
>> It does not come built-in into Azure Data Studio.
It doesn't come built-in into SQL Server or Azure SQL database.
I'll show you extension in
Azure Data Studio that you first have to load.
Still in preview, extension by Microsoft.
Once you have that extension enabled,
then you get this capability.
>> Got you. Not super difficult to get.
You just have go get an extension Azure Data Studio.
Awesome, cool. You mentioned something about Azure SQL database.
Does this work in Azure SQL database
or Azure SQL Managed Instance?
>> Natively, I cannot create a job for Azure SQL database,
but I can do it for Managed Instance because
Azure SQL database doesn't have a SQL Server Agent.
Today, if you want to run a Notebook,
you can do with the SQL Notebook.
There's a partial command and I also
have example in the demo script that I shared.
You can do it there, but you really cannot scheduled
the way you can do it against on-premises SQL Server.
>> Got it. If you wanted to do
the scheduling capability with Azure SQL database,
since you don't have SQL Server Agent,
that's really why you can't use this.
There are other things like elastic jobs,
run Boggs, automation that you could use.
How about to see this in action?
I've never seen it before.
Do you have anything you can show us?
>> Yes, I can. Definitely. Let me share this.
This is a Notebook I wrote in Azure Data Studio in SQL Kernel,
if you're familiar with Notebook,
you can write your notes.
I have some prerequisites for this to work.
Before I do any of this,
I'm going to show the extension.
There's two way you can get this extension.
One is, if I type here,
agent, this is the extension that you have to enable,
then you get the functionality that I'll be talking about.
There's also something called Admin Pack.
If you have Admin Pack,
Admin Pack actually comes with
four different extensions and SQL Server Agent comes with it.
You do not need that to load separately if you already have it.
>> Nice. I actually didn't know there
was an Admin Packs, so that's cool.
You get all those things in one extension.
>> Yeah, you get all these four.
I'm going to close this.
Before we want to run this,
we need to decide a database,
can be existing one or you can create a new one.
This will create two tables.
One is nb_template and nb_materialized.
One will store the definition of the Notebook.
The SQL Server Agent can pull
that definition when it runs the job.
Other one is going to save your results from each run.
I'm just going to create a empty database
called Notebook Jobs here.
Once I'm done with that,
now I'm going to create
the Notebook Job and I'm not going to read all these details.
I also put a link here.
You can see all the features
into the job indicates what does what,
just in case if you're interested.
The jobs by default take the name of the Notebook.
I'll just run a script to delete it in case
if you already have a job with the same name,
you'll get an error message.
Here I'm going to go to the run a Notebook Job,
set it up, and then we'll look at the output.
This is the connections for your server.
This is my default instance in the machine that I'm using.
If you right-click and you go to Manage,
you'll see something called SQL Agent.
Once you click this,
let me get some real estate and turn this off,
now this is exactly you are seeing what's
in your SQL Server Agent,
the jobs that you have already configured.
I do not have any jobs,
I just have one that comes by default.
Now, if I click on the "Notebooks", here,
I can see if I have any jobs that created with the notebook.
The one you saw actually,
I was testing it before this,
so I cleared it up again.
Now, we can say create a "New Notebook Job", and here,
you point what notebook you want to run,
and we'll see this notebook what it's doing.
I'm saving my database configurations,
and I can save this maybe once a week,
so in case if something goes wrong,
I can go back and see what was my previous setting,
or if you're making a major deployment,
you probably want to save this before,
so you can compare it after that.
For my storage database,
as I said, you can choose anyone,
but in my case, I'm just going to choose "Notebookjobs".
I'll run it against
master database as I'm collecting all the configurations.
As you see, the name was taken from the notebook name.
Now, one thing,
I can pick a schedule here,
whatever schedules I have available,
but I cannot create a new schedule,
so if you need a new schedule,
you should create it in your SQL Server Agent
using the SQL PowerShell or from the GUI,
and then you can pick it up here.
I'm not going to do one,
so you can put a description here, and "Okay".
Now we have it.
If we have created a schedule or picked a schedule,
it'll run on that schedule.
We can also run it manually from here.
>> Cool. Pretty easy to set up, it looks like.
>> Yeah. Once I'm done,
now this is the beauty of this,
the output is saved in another notebook,
and I can open this here.
Now you see my notebook.
That's why I didn't show it to you before.
This was my notebook. Simple. It's just
collecting a system configurations,
and now I have the output.
I can save this before or after.
Run this, save it,
and I can e-mail it to someone if I
need to share it with my team
or to some other business partners or whatnot.
I can do alerts, operators, proxies.
The only thing I cannot do from here is the schedule.
As I said, you have to create your own beforehand.
Now, if I refresh it, I'll see two jobs.
These are each runtime.
A longer green bar means it
ran longer compared to the shorter ones.
If you do not put a path,
the output, by default,
gets saved where my notebook was.
As you can see here with the timestamp,
you'll get _output; that's another notebook.
There is another place it gets saved.
If you go here, you can just grab that output,
and you can send it to someone.
We'll quickly look at the metadata,
what those two tables that I talked about.
As you can see, the first one has the metadata in a JSON format.
The notebook itself is a JSON file,
nothing else, so it has all the details.
This is our runtime,
what time we last ran it.
Was it successful? Did it fail?
As we talked about before,
invoke SQL command,
you can run this with this partial command,
and you can automatically open
this in Azure Data Studio with open editor file.
I put three examples here: one is for default instance,
one is for named instance,
and how you can run it against
Azure SQL Database by proper authentication.
I also put some references here if you want to read more details.
There are still some issues that I sent to the product team,
and once I get those results,
I'll probably update this in my GitHub.
>> Awesome, cool. This has been really useful.
I've learned a lot. I hadn't
seen these notebooks in action really before.
I had seen the PowerShell command,
but I haven't seen that GUI that
you showed that makes it really easy to set these up,
schedule them, analyze them,
and what I love is that it's storing those notebooks as new files.
That was actually going to be one of my questions.
Well, I don't really want to just keep
overwriting this notebook,
that's not necessarily valuable,
but now you have a log,
you can compare like you said,
before a big change happens or after a big change happens,
make sure things are still running
however, it is that you want
from the job, so that's really useful.
Is there any limitations or
batches people should be aware of as they get started with this?
>> Yeah. One limitation people have right now is with
the named instance as the product team is aware of it.
You cannot schedule this to
a named instance from the GUI that's why I
put an example with the invoke SQL runbook
that you can run it manually for now.
Of course, you can schedule with
Windows Scheduler or if you have other scheduler.
You can take it up there actually.
Instead of carrying a notebook,
or you can also write it to a database table,
your output, using your notebook with the date timestamp.
In that way, the database at our site is
easy to compare what changed and what didn't change.
With invoke command,
you can actually put date and
timestamp to your output file also.
I was not able to do that from the GUI, so here,
if I'm running it many, many times,
I can have data at this date timestamp app into my filename.
That will probably be easier to sort and all that.
>> Cool. Awesome. Well, this has been really useful.
I think always something to keep in mind
with Azure Data Studio is that the team is
investing so quickly and innovating
so quickly that by the time you watch this video,
some of these limitations we're talking
about might not even be there anymore.
Definitely go check it out.
Taiob, you have some great resources in that notebook.
We're going to put a link to that GitHub page in the description,
so definitely go check that out.
I'm going to go take a look because it looks like you've got
some helpful comments and resources in there.
I just want to thank you so much
for coming on the show. This was great.
I've learned a lot, and I think our viewers did as well.
For our viewers, if you like this video,
go ahead and give it a like.
Leave us a comment, and let us know what
you're going to do with NotebookJobs,
and we hope to see you next time on Data Exposed.
[MUSIC]