Hi and welcome to this Azure SQL and SQL Server Database monitoring course. In this course, we will be focusing on monitoring the overall health and performance of Azure and SQL servers and their databases. We will start with relatively new technology that greatly simplifies and enhances system monitoring through to traditional database mechanisms which underlie that technology.

This course is squarely aimed at database administrators and how you can put in place mechanisms to automatically and proactively look for potential issues that may disrupt the delivery of your database services. With that in mind, this course assumes that you have the pre-requisite experience using the Azure portal, SQL Server Management Studio, and familiarity with T-SQL.

My name is Hallam Webber and I'll be your instructor for this course. We welcome all comments and feedback, so please feel free to reach out to us at support@cloudacademy.com with any questions or comments. 

Let's dive in.

In an ideal world, our databases would perform optimally all the time. But for obvious reasons, primarily because databases just by their nature are dynamic, meaning they grow and change over time, what was once an ideal configuration or state, no longer is. Not only can changes be in the form of more data, requiring index and statistics maintenance, but also functional changes by way of different and sometimes competing queries that result in contention for database resources.

Just from this very brief and general description, we can deduce a broad array of metrics and problems that come into play. Database growth means increased storage, I/O, CPU and possibly DTU requirements amongst other things. Without the appropriate metrics, it would be difficult to tell which of these factors were impeding performance. When new functionality is added to a database there can potentially be conflict contention with existing process and queries, resulting in excessive wait times and deadlocks.

Imagine a scenario where your database is experiencing rapid growth and you are at the same time implementing new functionality when users start to complain of slowness and a decline in performance. Without the appropriate performance data, a quick and relatively easy fix would be to upgrade the database service level.

Doing this may appear to solve or minimize the problem, but if the slowness is due to query wait times then you have just masked the problem at some expense. Because there can be many causes of poor database performance, the right diagnostic information is essential. Not just for quickly fixing problems, but as we shall see, preventing them before they occur.

SQL Server, which Azure SQL is closely related to, is a mature product with many ways to access diagnostic information. Most of this diagnostic data comes from dynamic management views and functions that are accessed via T-SQL. Built on top of these are graphical tools like query store that can be found in SQL server management studio.

In the Azure portal within the context of a database, there are various pre-configured metrics related to database activity and load. At the top of the monitoring hierarchy, Azure has intelligent insights and SQL analytics that not only use your databases' historical diagnostic data but also learnings gleaned from the performance of millions of Azure-hosted databases combined with artificial intelligence to model and predict potential performance issues.

We'll start by talking about creating a baseline, so you have a context for your database's metrics and then we'll move on to Azure's pre-built intelligent monitoring solutions, before delving into the details behind the metrics. Finally, we'll look at how you can set up notifications to warn of specific or critical situations.