Introduction
Overview
Intelligent Insights
SQL Server Management Studio
Wait Statistics
Index and Statistics
Storage
Alerts
Summary
The course is part of this learning path
Information is at the heart of most software systems and the lifeblood of many organizations, so you want the database that stores this information to be efficient and reliable. But as we know, things happen; sometimes bad things. One of the ways that we can prevent bad things from happening is to know about them in advance like the old saying says, "To be forewarned is to be forearmed."
Azure SQL in its many forms has an abundance of features that help you to monitor the state of your databases and database server. Ranging from prebuilt automated monitoring that is augmented with artificial intelligence through to dynamic management views, SQL Server monitors and logs all aspects of the database engine’s operation and configuration. Intelligent Insights and Azure SQL analytics enable you to easily access the wealth of diagnostic and performance data in an easily digestible format.
This course introduces you to the different database monitoring and notification technologies available, how they work, and how to use them. If you have any feedback relating to this course, feel free to contact us at support@cloudacademy.com.
Azure SQL and SQL Server Query Performance Tuning
Learning Objectives
- Understand the key elements of database monitoring
- Learn about the features of Intelligent Insights, Azure's AI-based database monitoring service
- Create graphical reports using SQL Server Management Studio
- Understand how wait statistics can show you where threads have to wait and how this can be used to monitor performance
- View and fix index fragmentation
- Monitor database storage
- Implement notification alerts on various database platforms
Intended Audience
This course is aimed at database administrators or anyone who wants to learn how to implement systems that can find potential issues that may disrupt the delivery of their database services.
Prerequisites
To get the most out of this course, you should have experience with SQL Server Management Studio, be familiar with reading and writing SQL, and have an understanding of basic database architecture and administration tasks, like indexes and backups.
Course Related SQL Scripts
https://github.com/cloudacademy/azure-sql-database-monitoring-dp-300
The first thing we need to do is set up an Azure SQL analytics resource. This can be done by creating a new resource and searching for Azure SQL Analytics. SQL analytics requires a log analytics workspace, so you can go with the default or create a new one as I have done here, selecting your subscription, resource group, and location. Click OK, let it validate, and then click create.
Once the Azure SQL analytics resource has been created, we then need to link it with a database, which can be an Azure SQL database, or a managed instance, or an elastic pool. This is done by going to your database resource and under monitoring go into diagnostics settings and add a diagnostic setting.
Under the category details, we can select all the different types of logged data we want to stream, and under destination details, I'll select send to log analytics.
Next, I will select my subscription, the log analytics workspace I've just created, and I'll give the diagnostic setting a name and click save. You can also add security auditing to your log analytics by going into auditing under security, turn on auditing, and check log analytics, select the appropriate log analytics workspace and click save.
Now that that has all been set up, let's go back to the homepage. There are a number of ways we can navigate to SQL analytics. I can go there through the recent resources list, but I'm going to go there via log analytics workspaces. Here I will select the insight workspace, and then go into workspace summary.
We can see that we have two summaries here, security insights and SQL analytics. Going into SQL security insights we can see a few issues have been highlighted. If I go back to the workspace summary, I can see there is a one Azure SQL database under SQL analytics, and clicking through into the detail we can see graphs of some of the metrics that have been collected. This is a very new database and it has just been set up with me as the only user so there isn't really a lot to see here.
One thing I should point out is that it does take a little while for the data to be populated from your database, so if you have just set up and go there straightaway don't expect to see anything. Give it a few minutes for the data to be streamed through to the dashboard.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.