Skip to content

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

T-SQL TuesdayT-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

Example Project
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:
Storebrand Real Time Monitoring

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.

Click on the thumbnail below to see an example of how to configure jobs:
Storebrand Real Time Monitoring - Job

Click on the thumbnail below to see an example of how to filter objects:
Storebrand Real Time Monitoring - Filter

Click on the thumbnail below to see an example of how to create objects:
Storebrand Real Time Monitoring - Create Group

Click on the thumbnail below to see how easily objects are moved:
Storebrand Real Time Monitoring - Move Object

Click on the thumbnail below to see an example of how to subscribe to alerts:
Storebrand Real Time Monitoring - Subscribe

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, and I can’t wait to see how they will improve it in the future :)

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Be kind to each other. Thanks!

Share Your Thoughts?