White Papers

SQL DB Monitoring – from basics to pro

Part I. Introduction to SQL DB Monitoring

By Roman Yuferev and Ilya Izmailov

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 lots 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 doesn’t 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:

  1. Processor
  2. Memory
  3. Pagefile
  4. I/O
  5. Server
  6. Service Accounts
  7. Instance
  8. Database and tempDB
  9. Performance metrics
  10. Indexes and Statistics
  11. Naming Convention
  12. Security
  13. 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": http://www.viacode.com/blog/2018/06/19/sql-database-monitoring-from-zero-to-hero