Custom Real Time Monitoring Solution (T-SQL Tuesday #66)

T-SQL Tuesday #66 is hosted by me (yay, fun!) and is all about monitoring. We all monitor something while working with SQL Server, and there are so many topics to choose from. As a Data Warehouse developer I use Adam Machanic’s sp_WhoIsActive all the time, I look at the Integration Services Dashboard in SSMS to monitor SSIS package executions and I check the Job Activity Monitor for a quick overview of what’s currently running on our servers.

However, I decided to write about a custom Real Time Monitoring solution we use in my company (Storebrand) that my coworkers in Lithuania created. I couldn’t resist this opportunity to show one of our cool solutions that I get to use and to brag about how talented my coworkers are 👏🏻

Storebrand Real Time Monitoring

There are many great monitoring solutions available out there. In my department we actually use several solutions from vendors to monitor our SQL Servers and Business Intelligence environments, but we also had some very specific monitoring requirements. None of the vendor solutions available at the time were able to provide all the functionality we needed, so we built a custom real time monitoring solution:

• Easily create and edit personal projects, or collaborate on shared projects
• Web-based interface to access projects from any computer in the network
• Pick and choose specific files, databases, cubes, jobs and even job steps to monitor in each project
• Create status and quality checks, and subscribe to get alerts via e-mail or SMS if the checks fail

This example shows a monitoring project during a job execution. Live animations and everything! ;) It is really easy to see that something has gone wrong just by glancing at this project. I can see that there are some data quality issues in the Data Mart and that new data has not been loaded into the cube, but at least the cube is still available for querying. I can see that the .csv file has not been updated like it was supposed to and that it hasn’t been loaded into Staging. Everything else seems ok and data is currently being loaded from the Oracle Source into Staging:

All the objects can be configured and moved to create a personalized monitoring project. Maybe you have a complex solution and only want to monitor parts of it? Maybe you have a complex SQL Server Agent Job and only want to monitor the first steps? Maybe you want to add row count checks and subscribe to alerts? All of that can be configured.

Summary

This post showed an example of a custom Real Time Monitoring solution created by my coworkers. It’s a great solution for our DBAs and developers to keep track of the parts of the Enterprise Data Warehouse they’re currently responsible for or working on. It’s even useful for our data scientists and advanced business users because they can get read access to projects and see what’s happening behind the scenes in real time. But most of all, it’s a fun solution that my coworkers have spent a lot of time on 👏🏻

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓