SQL DB Monitoring – from basics to pro
Part I. Introduction to SQL DB Monitoring
These days Microsoft SQL Server tends to be more than an ordinary database engine for Windows-centric datacenter. Its capabilities and cloud integration features are growing as well as its deployment scenarios. The main goal of this post is to guide you through the multiple tools that can be used to monitor SQL Databases taking into account various environments, special needs and monitoring tools you already have.
We will review SQL Server monitoring inventory and the corresponding tools on a "simple-to-complex" basis. Let’s begin with an overview of several options available for you to monitor a typical SQL Database.
First, it is important to clarify the meaning of SQL Database monitoring. You can easily find plenty of videos, articles and blog posts about SQL Server monitoring focused on manually executed diagnostic scripts using the SQL Server Management Studio (SSMS), however, this approach is almost unsuitable for enterprise customers. Enterprise-level database monitoring is when you have an automated system customized for deployment and that system performs continuous 24-hour monitoring. There is a rich inventory of original Microsoft tools you can use to monitor SQL Server deployments of different sizes and various purposes.
System Center Operations Manager (or SCOM) is a universal IT monitoring tool that can monitor virtually anything along with thorough customization and tuning features, which are highly appreciated by many IT specialists. Essentially, SCOM is a Microsoft System Center suite product for monitoring different workloads from Microsoft and third-party providers like HP, Dell, Cisco, IBM, Oracle, etc.
There are lots of Management Pack extensions that you can download and use for monitoring a particular workload and extending SCOM capabilities. Microsoft provides management packs for almost every server product including SQL Server, Always On, Mirroring, Replication, Analysis Services and Reporting Services, and Azure Database. The management pack designed for SQL Server along with SQL Server product group is the most popular package, and it is widely used for SQL DB monitoring.
SQL Database and components monitoring is included with the following management packs:
Microsoft SQL Server packs are designed to monitor on-premises deployments of SQL Server (Windows + Linux), and Business Intelligence components (Analysis Services, Reporting Services, etc.). Azure SQL Database can be monitored with the corresponding Management Pack as well.
Apparently, there is a relatively new pack for monitoring the SQL Managed Instance: an automatically managed cloud instance running within the Azure SQL Database cloud service.
Along with common SQL monitoring practice, SCOM provides great centralization capacity and can be smoothly integrated with an IT Service Management system. In addition, you will not fail to notice this remarkable SCOM tool for SQL DB monitoring in Dashboards which can give intrinsic and valuable information to any sophisticated professional doing operational monitoring and root cause analysis.
Owing to its boundless centralization opportunities, SCOM is a keystone monitoring tool for large-scale concerns that works with Microsoft Windows Server and Unix-based hosts.
Apart from on-premises and mixed approaches suggested by SCOM, I will mention that the Azure Monitor tool provides useful and handy monitoring to deal with SQL Azure cloud services. It covers vital goals such as streaming, storage and querying of all monitoring data coming out of Azure resources along with decent visualization and automation. Azure Monitor also provides you with timely updates on operational information. With that, the log search option makes it easy to filter and aggregate the logs and has detailed logs and metrics visualization.
Therefore, Azure Monitor represents a reasonable (and free) choice for such cloud services as Azure Data Warehouse, Azure Analysis Services, Azure SQL Database and Azure SQL Database Managed Instance as it works easily and free right out of the box. At the same time, its relatively short data retention policy may encourage you to use more powerful tools, such as Azure Log Analytics.
Azure Log Analytics (formerly part of Operations Management Suite) is a Microsoft Azure tool, which provides decent SQL database monitoring capabilities. This platform is used to manage stand-alone and cloud resources using the Azure Portal. In addition, Azure Log Analytics smoothly cooperates with SCOM, which makes it a great SQL Server monitoring tool. Like SCOM, Azure Log Analytics also supports some specific monitoring scenarios with extensions called solutions.
For SQL Server, it provides two solutions which are described below.
SQL Health Check solution works with SQL Server on-premises and performs a regular assessment of your SQL database and instance health. It can identify abnormal behavior, potential issues and weak points that need to be addressed before it becomes a real problem.
Azure SQL Analytics solution is designed to monitor Azure SQL Database resources, subscriptions and elastic pools. It provides a clear visualization of the metrics and makes it possible to create new operational monitoring scenarios and customize the existing ones.
Azure Log Analytics appears to be a flexible cloud monitoring solution that does not require laborious server installation and configuring. On the other hand, free tier of this tool has limitations on the amount of data collected and a certain data retention period. Usually it is more than enough to do some POC, but a full-blown enterprise scenario will require you to upgrade from the free tier. Learn more about Azure Log Analytics pricing by following this link: https://azure.microsoft.com/en-us/pricing/details/log-analytics/
BPCheck (Best Practice Check) tool does not require any configuration steps to use it due to the fact that it’s just a package of the T-SQL scripts. In fact, it is a kind of a “Lego set” that can be downloaded and used free. It works almost everywhere, supports all SQL Server versions (2005+) and all platforms. Build a bogie or a spaceship – it is up to you.
This tool has quite an impressive set of metrics and health checks for the SQL Server including:
- Service Accounts
- Database and tempDB
- Performance metrics
- Indexes and Statistics
- Naming Convention
- Maintenance and availability
Despite it being a rather geeky tool, you can run it from SSMS and even build some automation on top of it to scale this tool to an enterprise level. Moreover, it can turn into a heaven-sent recovery base if your primary monitoring system suddenly collapses.
At that, a short review of the available Microsoft SQL Server monitoring tools is over.
In the next post, we are going to talk about particular usage scenarios for these remarkable tools.
You can learn more about this topic from recording of our webinar “SQL Database Monitoring – from zero to hero”:
Part II. Monitoring Microsoft SQL Server with SCOM
Turning to our previous post presenting the SQL Server monitoring tools, it is time to go deeper into the practice. This post is devoted to the monitoring keystone - System Center Operations Manager aka SCOM and its operations with management packs. Prerequisites and installation of SCOM is considered a bit corny in this post, as you can find lots of thorough instructions about this. Therefore, we skip it and delve into importing and working with SQL Server management packs.
You can either download a special management pack from microsoft.com or import it from the catalog by means of the corresponding SCOM feature. By the way, the last option will help avoiding possible troubles with lack of management pack dependencies.
In most cases, management packs work smoothly out of the box, but it’s good to mention that SCOM default action account is mapped to either Local System account, or any Domain User account. In other
words, SCOM does the monitoring under the above-mentioned accounts. Therefore, make sure that all the rights are granted correspondingly.
Upon successful installation, it’s time to go and make some coffee until the discoveries get their initial job done and you can see the results of it in the Monitoring section. First, let’s go there and check out the contents of “Microsoft SQL Server” folder – there should be presented all the installed SQL Server 2005-2016 management packs stuff, and each inner folder is devoted to a separate management pack. As for the latest SQL Server 2017+, it will create its own folder as soon as you install the corresponding management pack.
If you are ready to go deeper, examine the “SQL Server Roles” view to get acquainted with the discovered instances you have, their types and configuration.
And if something is already wrong with your instances, you can see it right there: nice, clear and in full color. Need a more detailed information on a certain issue? Select an instance and open the Health Explorer to see what is up.
It displays a hierarchy of all objects of the selected instance along with thorough descriptions and knowledge articles for any of it. And, of course, yellow and red icons alert you that there is something wrong. All you have to do is to drill down to the source object that makes troubles and find a way to make it stop. You are confused about the necessary actions. The resolution can be available right in the “Knowledge” section of the guilty object.
The Operations Manager console provides a set of views to examine the parameters and metrics of the monitored objects visually along with the corresponding alerts and events.
For instance, go to the Performance View and observe all the metrics currently collected for the chosen instance and its objects. There can be hundreds of them in some cases.
The Diagram View displays the way the instance objects are connected to each other at length. And says if there is something wrong in passing.
However, if you want an all-inclusive viewing service, you should certainly go and see the Data Center Summary Dashboard:
You can see here the aggregated health state of the whole enterprise rolled up in the corresponding tiles and check the colors to get jerry. The logics here is the same: click a tile and it expands with lots of useful information:
Too much information? Configure the Dashboard to your liking – edit and delete the tiles, or even add your own ones. Need more information? Double-click the tile and drill down to a deeper level of the instance.
Don’t be afraid to get lost – the navigation will always show you where you are and drive you back if needed.