>> You've been hearing a lot about Azure Monitor SQL insights.
But what does it actually do and how do you get it set up?
Learn more in this episode of Data Exposed.
[MUSIC].
>> Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today I'm joined by Alain Dormehl,
our Program Manager on the Azure SQL team.
Alain, thanks so much for joining us today.
Can you tell us a little bit about what you do?
>> Sure. Thanks to having Anna, Alain Dormehl,
I'm a Senior Program Manager on
the Azure SQL team
and I look after monitoring and a couple other things that we do.
But today's focus is primarily going to be
about some of the new monitoring things we're working on.
>> Awesome. We're excited to learn about them.
I know you're going to be talking to us about
Azure Monitor SQL insights,
which is something that's pretty new to me
and probably a lot of our viewers.
I'd love if you you could just tell us
a little bit about why you did this,
what it is, and how it works?
>> Sure, fantastic. Essentially, the reason
why we did this is because one of the challenges that we've
run into customers over
the last couple of years is this ability to be able to monitor
all the SQL environments irrespective of where they're deployed.
Then there's also this disparity in the data that gets collected
for different deployment types within Azure and outside of Azure.
SQL insights is really a new way of us thinking about
how we get crucial telemetry through to customers,
and today it is an agent-based deployment.
It all deploys within customer subscription
and customers get to control everything from
what data they are collecting to how
frequently they collecting to where they store their data.
There's a lot of flexibility
in how the customers use this product.
One of the big advantages we have is that we actually use
an open source repository in the form of
Telegraf to gather all the data.
It's a very well known and
a very well understood collection mechanism
that our community has been contributing to for many years,
and we're super proud of what we've done here.
Hopefully as we flesh this product out some more,
we'll get to a point to where it's addressing
all the needs of our customers at scale.
Whether you have five databases or 5,000 databases,
you should be able to monitor them in the same way,
and you should also be able to monitor across
hybrid deployments without having
to introduce multiple different tools.
>> Awesome, cool, it sounds pretty great, sounds pretty cool.
I guess my next question is,
how difficult is this to set up?
Can you maybe take us through what
that setup process is lighter
or what it even looks like once it's set up?
>> Absolutely. Before we dive into that,
we've have documented this entire process
of setting up SQL insights.
If you go to our documentation
which we'll link in the description,
there's a full guide that walks you
through how you should be doing this.
But really the major pieces that you have to think about is,
there is obviously the databases that
you want to monitor out there.
Then you have to deploy the agent that goes and monitors these,
and then you have to deploy the storage
that you go install all of this data in.
The first thing we'll cover
is where does all of this data gets stored?
Today your options for storage is Log Analytics.
We're hoping in the future that we'll be able to give
customers more options on where they can go store their data.
But really the very first thing you're going to want to
do here is you're going to want to come,
and you're going to want to create a new Log Analytics workspace.
Now, this is something that I've already done in my environment.
Log Analytics, most customers should already have this deployed.
It's a very common time series storage engine which
Azure Monitor uses extensively
from all the security pieces through to the actual monitoring.
Your first piece would be to come and set up a new Log Analytics.
The only thing that's really important about
this deployment here is that you have to deploy
the Log Analytics into
the same region as the databases that you want to monitor,
so that's the first piece.
I've already set one up,
so it's as easy as choosing a resource group.
You come in, you name the actual instance,
and then there's just options around
pricing that you have to decide on.
Once you have your Log Analytics set, up,
the next thing that you need to set up is your virtual machine.
Today the deployment of
the agent runs on top of a virtual machine,
and those virtual machines
have been certified to run on Ubuntu 18.04,
and that's the version that we recommend.
This is all documented.
It's just a standard Ubuntu VM.
All of the same principles that
apply for any VM you deployed in
your environment would apply here,
so you can deploy this into your own network,
you can secure this using NSGs however you want to do that.
The most important thing here is just think of
this VM as the deployment agent,
but essentially it's running
an app that needs to be able to communicate with your databases.
Whatever that network topology is,
whether that's sitting behind a specific subnet, whatever that is,
you need to make sure that your databases can communicate
to that virtual machine so
that the virtual machine
go collect data and then flow through to the Log Analytics.
So that's the major workflow.
Setting up these VMs,
very simple to do.
It's a very small VM that we run,
and then once you have it set up,
you will then come through to
the the actual setup of SQL insights,
which will then pull down
all the configuration files when you choose to do that.
But before you actually get to setting up SQL insights,
the last piece you have to do is you have to
actually come and set up
your Azure SQL database
so that it can actually accept connections from this VM.
This is the VM again,
you'll see here it has an IP address, a public IP address.
Just for the simplicity of this demo,
we'll be using the public IP addresses.
But if both of these were connected on a private VNet,
you could use the private endpoints to do this.
You have that IP there,
and you'll see if I come and I look at
my firewall rules for
this database that we are going to be setting up,
you'll see that we've already added multiple of
these within the range that we have.
Today is the specific one that we were looking at now.
We have already set up this firewall exception
for when you want to come and monitor this environment.
The last thing that you have to do,
and I'm just going to tab out into
my SSMS is you need to come set up a user,
so the user is important in that.
The Telegraf agent that runs on top of
the Azure monitoring agent needs to have
some type of authentication method into the database.
Today the only authentication
that we accept is SQL username and password.
However, in the future, we'll be able to do
AAD and some other very cool authentication methods.
But really you would just set up
your user provide it with a password,
and then you can check to see if that user actually exists.
In this case, it does.
It's set up within the Telegraf 1 at the instance level.
Those are the major pieces you need.
You need the storage account,
you need the virtual machine setup.
You need the database and you need access
into that database that has been set up
so the Telegraf can communicate to that database.
We've swapped over to SSMS now and
the last piece we need to do here is actually create the user.
Telegraf today uses SQL username and password to
get into the database and pull all of the telemetry that you need.
We always recommend to customers to
use a user that has least privileges required,
and in this case for Azure SQL Database,
it's the view database state permission
that you can assign to this user.
Once you've assigned that,
you can come and you can view it here.
You'll see that I've already created this user earlier,
but this user will just allow Telegraf to get in
and be able to pull all of that data.
The major things you need to think about here is you need to have,
a, the place to store the data,
which was the log analytics.
You need, b, the virtual machine
or the agent that's going to go collect all the data,
which is that Ubuntu 18.04 VM that we created.
Then the last thing you need is you
obviously need all these databases
that you're going to monitor from.
In order for the agent to be able to access them,
you need to create users on
the databases an you also need to make sure that you have
set up the firewalls so that they accept incoming connections.
Oops, didn't mean to tab.
Let's go back there and then let's move forward.
Now, we're going to come and we're going to look at
the actual SQL insights portion of it.
Here on the Azure Monitor blade of the Azure Portal,
you'll see there's this new option here under the insights tab
called SQL and it's in preview today.
You can just click on this and it'll bring you to the screen here.
The first screen you'll see looks something like this.
Now this profile that we're looking at here,
as I said, we've already used this one.
We've already set it up to some degree.
But you can come in and you can see
here's that virtual machine that we created,
which is that agent.
I can come and I can look specifically at this agent.
What you'll see here is all of this is just bound to
a configuration file that essentially just tells
this agent where to go and fetch all of its data.
If we walk through that process,
what you would do as a customer is you would
come and you would add a new monitoring machine.
You choose which subscription it's in.
You would go and choose that virtual machine
that you had set up previously, and then here,
under the conflict, you would specify
all the specifics about how to connect to these databases.
Today we take connection strings in.
As we move forward,
we're hoping that this will become a lot
more automated where customers can
just tick databases out of a box.
But today, you use this process of providing connection strings.
This is also a lot to do
with SQL running in virtual machines because
not all SQL deployments in
virtual machines are registered with the RPs.
It's not to say that the Azure portal
knows instinctively where to find all of the resources,
which is why we give customers this option
of providing connection strings.
You can then, once it's been configured, generally speaking,
it takes about 30 seconds for everything to get configured.
The config file that you
set up here gets pushed down to the virtual machine.
You can then see that we're busy collecting by
looking at the actual logs for the machine that's busy collecting.
You can see here that we are actually collecting
data on the frequencies that we specified.
You can choose how often you go to collect this data.
If we have a look specifically at
what those different data sets are that are available today,
you can see here again, as I said,
you choose the frequency that you collect the data at,
but then also per deployment types,
so per Azure SQL Database or per manage instance or per VM,
you can come in and select what data you want to be collected.
By default, we've assigned the things that we feel
are the most important for you to be collecting.
But there are additional options that customers can choose.
Just know that the pricing for
this solution is based on the amount of data you collect,
so the more frequently you collect data
and the more data sets you are collecting,
that chases up the price for the solution.
Once you've deployed this,
essentially what will happen is you'll start
collecting all of that data and you'll
then be in a position where you can start viewing this data.
We have a very basic out-of-the-box experience here for customers.
But really, I think the power of
SQL Insights is the fact that there is
this rich underlying data set which customers can
come and build their own visualizations on top of.
That's in a nutshell,
the quickest way that you can set up SQL insights.
My recommendation will be go read the documentation.
But really, what's important is
understand that you need a data source,
which is your database,
you need a target for the way all of that data is
going to be going in this case, it's log analytics,
and you need to set up those monitoring agents,
which in this case is a virtual machine that you deploy.
Everything else in this scenario
is things that customers should be very familiar with.
Then just don't forget things like setting up
the permissions for the agent to actually get
into the database and that's firewall rules
and having the correct users on the database.
>> Awesome. Wow, this is pretty cool.
It seems like there are a good number of
pieces that are involved in this,
but I really liked how you took us through the flow
and it makes sense why we have
each of these pieces and how we can go
about setting each of these pieces up.
In your experience working with this in the preview,
do you have any tips for people as
they start to play with this and get it set up?
>> Sure. I think one of the biggest ones we see is just
read the documentation carefully.
We have very well-documented,
how you can use things like Azure Key Vault
to secure these passwords and usernames and things.
We've documented very well how you can set up
fairly complex networking deployment scenarios.
My best advice is just read the documentation carefully.
There are some deployment types today that
we don't support like any of the sub-core deployments,
like the S0s, the S1s, the S2s.
If you try set this up on those databases,
you'll run into a bunch of errors.
Just make sure you read the documentation thoroughly,
read through the FAQ and hopefully,
all the information you need to set this up is
there and this becomes
fairly simple for you to set up in your environment.
>> Awesome, cool. Thanks so much, Alain.
For our viewers, we will put a link to
that documentation Alain is talking about into the description.
We encourage you to go check it out,
try this out for yourself.
Of course, if you have any issues,
you can always file them with the documentation in GitHub.
Alain thank you much for joining us today.
This is really insightful,
to our viewers thanks for joining us as well.
If you like this video, go ahead,
like the video, subscribe to our channel,
leave us a comment and let us know what you think of
Azure Monitor SQL Insights and we
hope to see you next time on Data Exposed.
[MUSIC]