>> The SQL Server team has been really great about
putting out best-practices checks for you for years and years.
In this episode, we'll learn how you can actually access these
through PowerShell and do them in a PowerShell Notebook,
this week on Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today, I'm joined by Aaron Nelson.
Aaron, thanks for joining us on the show today.
>> Thanks for having me, Anna.
>> Of course. Before we get into the topic for today,
I'd love to hear a little bit
about your background and what you do.
>> Sure thing.
I'm an Azure Cloud and AI Consultant here at Microsoft,
and I'm a pretty huge fan of
automating SQL tasks with PowerShell and other automation tools,
and I came here to show you guys
a little bit of things that are available to you now.
>> Awesome, great. I'm looking
forward to learning about this because I don't know
a ton about the SQL Assessment API or some of the capabilities.
So I thought it might be nice to start with a brief overview of
the best practices history and how we
evolved to where we are today, which you're going to show us.
So do you think you can start by explaining that?
>> Sure thing. Yeah.
To give you just a little bit of background before we dive in,
what we're going to be looking at today is
the SQL Assessment Checks,
which is an evolution of what originally started out
in the best practices check of the SQL Tiger Team.
They've evolved it beyond just being a bunch of T-SQL scripts,
and they've now got it so that it's
in PowerShell so that you can go
ahead and run it as a whole or run just individual checks,
however, it's best for your environment.
Really easy to scale it for your environment.
>> Awesome, cool. Seems like we have this thing
that obviously the experts on our team came up with and
it allows people to check and see if they're
using the best practices and assess their servers.
I'd love to take a look,
if you have something to show us,
and we can get right into it.
>> Yeah. Absolutely. I've got
a PowerShell Notebook right here for you,
and we can start running through it.
>> Awesome.
>> These assessment checks are
already built into the SQL server module,
and you can pick that up on the PowerShell gallery.
You want to go ahead and install that module,
and then these commandlets that you're about
to see are going to be available to you right there.
There's two main commandlets that we're going to talk about today.
The first one that we get there is
the invoke SQL Assessment commandlet.
Just a little bit of how you use that,
is you take your SQL Server Instance
or number of instances that you want to
assess and you pass it into by
using the pipe character into this SQL Assessment commandlet,
and you let it go run off and run
its check and tell you what it thinks about your sequences.
I'll go ahead and kick this one off right here.
That's going to run for a few seconds.
It runs a list of predetermined checks
that the Tiger Team has developed over the years.
Even though it's doing a list of checks
that it thinks are important,
it's important to also note that you can customize this and
add your own checks to what the output is here as well.
>> Awesome. That's cool. I can use this
to run these checks against
just my SQL Servers or does this also apply to a managed instance?
>> As of right now, what you can do is,
your on-prem SQL Servers,
you can do all of those going back to SQL Server 2012.
Then for your Azure SQL Managed Instance,
you can run those today,
right now with the tool as it is
and get those assessment checks there as well.
>> Awesome.
>> One of the checks that I want to bring your attention
to is just an easy one to explain,
is just the latest CU,
and that's one thing where with things being in CUs these days
and no longer working off of
service packs and them coming up more rapidly,
you can get behind and not even realize it,
that there's a new version out.
So when a new version is out,
a few days later,
the new version of the module goes out and
updates this information with what the latest version is,
and you can run
the checks and it'll tell you if you're out of date,
and if you are, what the next version is that you need to bump up
to in order to be able to stay up to date.
In addition to running the instance level recommendations,
you can also run this just at
a database level if that's what you need to run.
There are different rules that can be run.
Obviously, there's some at
the instance level though would run if you're running
an instance-level check and then database-level checks
if you're running just down here at the database level.
If you want to run
just that check to see if you're running against the latest CU,
you can just do that right here.
By running and grabbing your instance,
sending it to SQL Assessment, and for checks,
you can just tell it latest CU,
and that I'll give you back whether you've
passed the check or whether it
has a recommendation for you right here.
>> Cool. You showed us, at the beginning,
how I could ran all of the checks,
but this is a way to just run
a single check if that's all I want to run.
>> Yep, absolutely. Obviously, with
one of these commands where you're checking the latest CU,
you're probably not going to want to run it against
an individual instance of SQL Server,
and they've got some various options.
If you're familiar with registered servers or
the central management servers,
you can actually take the output of that,
pipe it into your invoke
SQL Assessment command and run those checks,
and then even further along from that,
if you're doing these on
a regular basis and you're going through and you're fixing things,
you'll probably get to the point where you fix
things and now you want to see,
"I know I've solved a couple of problems,
but what's my list of things I still have to go."
Also for the boss,
I want to show,
"Hey, look, I've been fixing things around here."
To that point, they have also included
the ability in these commandlets,
they have this option here called flatten output,
and that's just a simple switch parameter.
When you add that, it converts the way that it's putting
the output out to the pipeline tool.
It's basically telling it,
"Hey, I'm going to write it to a SQL server.
I'm going to save this."
So if you want to do something like that, it's very easy,
you just use the right SQL table data commandlet,
that's also available in the SQL Server module,
and tell it your instance,
database name, schema name,
and table name that you want to write your results to,
and it'll go ahead,
run the checks against everything,
in this case, looking at your central management server,
get all those assessments done,
take all that output,
put it into your SQL Server database,
and then you can build a Power BI report on it
from there or do whatever it is that you want to do with it.
>> Got it. Cool.
>> So that's the bulk of what I had to show for you today.
I just want to help people be able
to do the best practices checks against their SQL Server.
Take some of this knowledge that is out
there available for them free of
charge and be able to get
to work on it and start solving problems.
Sometimes, ones you didn't even realize you had,
they're just lying out there.
Surprise, surprises.
Now you know about it.
The great thing is, as you saw in the initial,
up here, at the very top, when we run these,
we get this nice description of what
it is and how to remediate that situation.
>> Awesome. Well, thanks, Aaron.
This has been really informative.
Personally, I've learned a lot.
I'm sure folks are familiar with
the Tiger Team and some of these advancements,
but maybe they haven't seen how you can do it in
PowerShell and how we're making it available in Azure as well.
Thanks for coming on the show. I really appreciate it.
Just one question as we closeout,
what's one tip you have for folks as
they get started with this SQL Assessment?
>> The biggest tip is probably to just go ahead
and go over to the SQL Server samples
and take a look
at a lot of information there, links to the doc page.
Especially if you want to know how to customize these,
you can customize them in JSON files,
and they also have Notebooks,
one for very simple assessment
just to get your feet wet with them,
practice a little, and get started.
Then tutorial 1, that's a little bit of a deeper dive and
really dive into all the different ones that you might want to do.
Some of them that you won't,
but really give you
that full idea of the scope of what's available out there.
So definitely go in and take a look at what's available on
the SQL Server samples
GitHub repo is probably my biggest tip here.
>> Awesome. Well, thanks so much, Aaron,
we will put a link to this in our description.
For our viewers, please be sure to check that
out and run through
the Notebooks that are already out there today.
Aaron, again, thanks for joining us.
For our viewers, if you liked this video,
please like this video.
Leave us a comment if you learned something,
and we hope to see you next time on Data Exposed.
[MUSIC]