>> Have you used SQL Assessments and
SQL Server on Azure Virtual Machines?
Learn all about it this week on Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today, I'm joined by Ebru,
a Program Manager on the SQL Server team.
Thanks so much for joining us today
>> Thanks for having me, Anna.
>> Always a pleasure and today we're going to be talking about
some new capabilities available for
SQL Server running in Azure Virtual Machine.
Why don't you kick us off by telling us a little bit
about this and maybe a little bit of history or context?
>> Sure. SQL Assessment is all
about making sure your SQL Service is configured properly,
that it's following configuration best practices.
We have SQL Assessment API which is part of
SQL Server PowerShell module and SMO and on
top of that API we built user experience in
Azure portal specifically for
SQL Server on Azure Virtual Machines.
>> Cool. This is something that's been
around for a while but now it's
available directly in the portal
for SQL Server running on Azure Virtual Machines.
I think it wouldn't be great or
awesome if you could just take us through.
What does it look like? What can we find? That sort of thing.
>> Sure. I'd love to.
If you have a SQL Server on
Azure VM 2012 or higher version and registered with SQL extension
then you'll see this feature SQL Assessments on
the SQL VM page and I can show you how to enable
and all that but I want to first start
by showing you-all the recommendations
and what you would get out of this feature.
This is the fun part. We store the results in
your Log Analytics workspace and we show the results in
a workbook and this workbook has a lot of
information so I'll walk you through it.
We are looking at the results, assessment results,
collected at this specific date and time and from this drop-down,
you can reach all the results.
We're looking at all the issues you can see here in this chart,
how many you pass and how many you need
to do something about broken down by severity.
We have high, medium,
low and informational severity levels and
this bottom part is where the recommendations are.
This first grid shows you-all the recommendations grouped and by
grouped I mean it shows you for
each recommendation how many times
it occurred in your environment.
If you click on one of these recommendations,
you'll see, for example,
this particular information it's high severity,
it's in the performance area or memory area tags tell
you which area the recommendation falls under and
this particular recommendation is for
your server and we're saying please enable the lock pages in
memory option and if you click
on it you'll get a little bit more information
about why we're telling you to enable this option.
If you want to further read about it you can follow this link.
>> Cool.
>> Let's get some more recommendations.
This one, for example,
is about full backups.
We're recommending that you have full backups
not older than a week and in this particular environment,
these three databases all hit this issue so you see
three instances of the same recommendation here.
We run the assessment against the server and all the databases.
We have recommendations some of them are specific to
SQL Server on Azure VMs like we're recommending that,
for these databases you move the data files from
the C drive to premium SSD data disks.
But most of our recommendations are
generic SQL Server recommendations like if you
were telling you there's
a specific foreign key
you have but there's no corresponding index.
That's not a good situation or you can have unused indexes.
You create an index but it's not being used.
You should probably check it out and remove
it if it's actually not used.
This is a demo environment.
That's why we have one of these.
In an actual production environment,
you can imagine there will be multiples of these and if you
clear the selection in this first grid actually you'll
see all the recommendations we have for your environment.
Like I said, in a production environment
there will be thousands,
four or 5,000 recommendations.
>> You can use those severity
to figure out which ones you should do first?
>> Exactly. That's what I was going to talk about.
Just looking at thousands of results in
a grid it's like how do you go through it?
That's why we have these filters here.
You can filter by focusing on just your instance
or any particular database you
care about or you can filter by severity like you said.
We obviously recommend you start with the severity issues.
You can filter by area,
you might want to focus on performance first
or you might want to make sure your trace flags
are set properly just pick the area you're interested
in or you can select any of the recommendations.
This is the same as clicking on one of these rows here.
Another option you have is exporting
these recommendations to Excel.
Excel is good with dealing with large amounts of data.
Or if you're familiar with Kusto Log Analytics Query Language,
you can open it in the window.
I'm not going to show it but it just opens it in
the Log Analytics query editor and you can see the data that way.
>> Awesome. This is really cool.
I had a question maybe we were going to get to it.
It looks like this was done on a particular day
at a particular time and you showed us we have historical runs.
How often does this run or
how often do you recommend people run it?
>> We expect people to run it weekly
or monthly maybe because the configuration doesn't
change that often so that depends
on your environment but probably weekly is the most common we see.
Speaking of doing multiple runs,
we also give you some views into what happened in this run.
You did one run,
you looked at all the issues,
in the next run, you want to see just what's new now.
You can see that in this tab or
if you fix some issues you want to confirm that
those things are resolved since the last answer you can use
those tabs for that.
>> Nice.
>> Let's go back and see how we enable
SQL Assessments to get to all of these results.
It's quite simple really.
All you need to do is tell us
your Log Analytics workspace name
because that's where we're going to store the results.
This VM is already associated with one so it's grayed out,
but if it wasn't this dropdown would give you a list of
all the available workspaces in
that subscription so you would
just pick the one that works for you.
The other thing you can do is enable scheduling.
You can do this at enabled time or you
can do this from the Configuration button anytime.
You can do an assessment anytime on-demand
but you can also set a schedule
so we can run this automatically for you.
Like we talked about, we have
weekly or monthly options with all the day of week,
occurrence, and time you
set and then automatic run happens for you.
Once you hit "Apply" we
install Microsoft monitoring agent for you,
if it's not already installed and configure
it to talk to your Log Analytics workspace,
that's the agent is going to upload your results and then we
also deploy SQL Assessment as well.
I'll show you another machine where
we enabled it and did some runs.
As you can see now,
run assessment is enabled.
This is the on-demand run.
By clicking on this button you can do a run anytime you want.
On the bottom half you see,
the last 10 runs you've done and if you set
a schedule you'll also see when your next run is scheduled for.
If there's a failure,
you can click on it and see more about the failure.
For example, if you set
your Log Analytics workspace to clean up after a while,
your results might disappear.
There is recommendation on how to fix that.
By clicking on this link
then that takes you to the workbook I just showed you here.
>> Got you.
Cool. This is really awesome.
>> Thanks. That's mostly what I wanted to talk about.
There's a couple more things in the workbook
but you can discover them on your own.
The most important part is
just understanding what the recommendations are
doing and just following
up on the high severity ones to start with.
>> Awesome. Thanks so much, Ebru. This has been really useful.
I've certainly learned a lot at about
SQL Assessments and then even some of
the best practices around using SQL Assessments.
For folks that might be just
getting started with SQL Assessments,
do you have any tips or tricks or advice?
>> I would say just try it on one of your systems
and learn the assessment feature
and then just start using it everywhere.
We really think this will bring a lot of value to you,
because this is SQL Server teams,
years of experience best practices
provided to you so we hope you make use of it.
>> Awesome. Great. Well, thanks again for coming on the show.
To our viewers, thanks for watching.
If you liked this video, go ahead,
give it a like, comment and
let us know what you think of SQL Assessments.
We'll put links in the description for you to learn more and
we hope to see you next time on Data Exposed.
[MUSIC]