Monitoring the Microsoft Data Platform with SQL Sentry: Introduction
Day in and day out, the SentryOne Professional Services team improves the lives of our customers, both new and long-time users alike, by teaching them how to leverage the powerful database performance monitoring capabilities of our products. We guide users through the process of building a customized alerting and response system and expose SentryOne features that help improve proficiency in identifying, troubleshooting, and resolving performance issues in their database environment.
Truth be told, our customers are smart people who are fully capable of deploying, configuring, and using our software. So, what does our team bring to the table? Our experience and insider knowledge, which we leverage to guide customers in optimizing our feature-rich and flexible monitoring solution to meet their specific business and technical requirements.
However, not all SentryOne customers take advantage of a professional services engagement. So, in this blog series I will bring some of the information we routinely share with professional services customers to a wider audience. Why open the vault to everyone? For several reasons:
- We know not everyone has the budget for consulting services.
- Every SentryOne customer and prospect can use this information to get more out of their investment in our software, and in turn their even larger investment in SQL Server
- We love our customers and want you to succeed!
A ground rule for this blog series is that I will avoid duplicating the abundant information already available in the SentryOne product documentation, certified training, and blog. Those resources provide foundational information that every SentryOne user should become familiar with. However, by their nature, those resources are largely focused on individual product features, and sometimes stitching it all together can be a bit challenging. This series will take a more holistic approach, guiding you toward adopting and using an enterprise-class monitoring, alerting, and troubleshooting platform. Links to supplemental resources will be included in the appropriate blog posts.
A blog series cannot address every possible alerting and optimization scenario confronting today's Microsoft Data Platform professional. With that in mind, I will take a “teach a person how to fish” approach to the subject matter, presenting the knowledge and skills needed to craft solutions and to know when another option might be better suited to a task.
Finally, this series will be focused primarily on monitoring SQL Server performance using SQL Sentry, as SQL Server is the data platform the overwhelming majority of our customers work with, either exclusively or primarily. For brevity’s sake, I will refer to our database monitoring solution as SQL Sentry in this series, but the underlying concepts, solutions, and examples are relevant to all monitored targets.
So, with that behind us, let's get started.
The Basics—SentryOne Components Overview
SentryOne’s monitoring solution is comprised of three primary components—a Windows service to collect data from monitored targets, a database to store the collected data, and a user interface to view collected data and provide administrative functions. Today, I will introduce each of these components briefly. Future posts in this series will dive deeper into each component.
Monitoring Service—Data Collection and a Whole Lot More
SQL Sentry uses a polling architecture to collect event data and performance metrics from monitored servers. Examples of events include a SQL Agent Job failure, a SQL Server instance shutdown, or a T-SQL block exceeding a defined threshold. There are, of course, many more.
Performance metrics are data points collected from the Windows and SQL Server performance library, WMI, DMVs, and other sources. CPU Usage Percent, Transactions/sec, Windows and SQL Server memory counters, and wait statistics are all examples of collected performance metrics.
The monitoring service collects all of the above data. It also:
- Establishes and maintains connections to monitored targets.
- Writes collected data to the SentryOne database and performs aggregation and purge processes on that data as it ages.
- Detects when enabled conditions evaluate true and initiates the configured action(s). For example, when an alert has been configured to send an email to the DBA team when a database backup runs longer than a specified duration, it is the monitoring service that detects the backup duration threshold has been exceeded, gathers all the relevant data, composes the alert message text, and executes a command to the SMTP server to send the email.
- Constructs and executes ALTER INDEX REBUILD or REORGANIZE commands when SQL Sentry Fragmentation Manager is enabled and configured.
The SentryOne Database
The SentryOne database is a SQL Server database that stores event data, performance metrics, historical data, and target server configuration data collected by the SentryOne monitoring service. It also stores all configured settings, conditions, contact data, and SentryOne component data.
Future posts in this series discuss the SentryOne database in depth, including coverage of maintenance recommendations, performance troubleshooting and resolution, and data mining.
User Interface Options
A key SQL Sentry component is the user interface that exposes collected data in an organized fashion using table layouts (data grids), visualizations, and reports. The UI also provides facilities for product administration and feature management, including user security, data collection and alert configuration, and adding and deleting targets.
There are currently 3 SentryOne user interface options:
- Windows client—A rich Windows application that provides read/write access to data stored in the SentryOne database. The many elements of this interface will be explored in depth in upcoming blog posts.
- SentryOne Portal—A browser-based UI providing read-only access to collected data. The SentryOne Portal is primarily a subset of the performance analysis features available in the Windows client. The SentryOne Portal’s feature set is growing and evolving rapidly.
- Power BI Content Pack and Tableau Report Pack—View and analyze SQL Sentry monitoring data using Power BI and Tableau.
The next installment in this series will discuss installing, configuring, and managing the monitoring service in more detail and present common architectures that maximize the efficiency and effectiveness of your SentryOne landscape.
Justin (@jh_randall) leads our Professional Services Organization with the perspective of nearly 30 years of IT experience. Professional Services provides customers with mentoring, consulting and training services focused on configuring and tuning SentryOne software, and SQL Server performance troubleshooting and optimization. Justin's blog focuses on helping our users become highly valued DBAs who use our software to prevent and solve real-world SQL Server problems while making positive contributions to their organization's success.