TSQL Tuesday #110

This month's T-SQL Tuesday is being hosted by Garry Bargsley (blog | twitter) - Automate All The Things. Since everyone's environment and experiences are different, he asks, "what does “Automate All the Things” mean to you?".

I love automating things. The thing you lose with automation is a human looking at something in order to figure out whether what is there is within guidelines or not.  With most companies, those guidelines aren't always spelled out well.  Therefore, it needs to be a thought out plan not just something that gets done quickly and not thought of again after.

For me, I think about automation as a project or process each time.  I learned the hard way that I want a lot of error trappings and alerts throughout.  For that reason, when I automate a specific process, I do so in multiple phases: 1) the base code that is needed is run, 2) error trapping is a lot more defined, 3) notifications to end-users are sent at appropriate times, and 4) self-recovery happens to help remediate errors not just report them.

For 2019, I am working on automating the monitoring solutions I am using. I will be combining multiple platforms to have an overall strategy for monitoring. I am taking the approach of listing everything that needs to be monitored, categorizing them into "buckets" and evaluating which platform would be best to monitor that specific bucket. Each bucket of metrics to watch out for have specific requirements which helps identify which platform would be best.  (I will be creating a presentation for this for upcoming SQL Saturdays, so if you are interested, watch for it!)

  • For instance, for real-time performance monitoring, I will be using Spotlight from Quest. That software product has a great UI which shows where to start digging for troubleshooting. Without that UI, I would have to look at multiple things and try to tie them together the same way the UI does for me.
  • For keeping an audit trail of what SQL statements were run and by whom, I will be using splunk. That platform is designed for event/activity based data and for storing and retrieving larger datasets.
  • For monitoring recent job activity and performance, I will be using t-sql. The structure is easy to query and report on from within SQL, so there isn't a need for an external tool.

On top of the actual monitoring, I also need to automate checks to ensure the monitoring is active and running. I think we have all been in the situation where, we thought everything was ok, but then we realized the job that is supposed to monitor wasn't even running!

Lastly, I think automation is a continual process. I don't know of anyone who will be able to say "I'm done". I continually ask myself, how can this be automated? This has become a habit for me now. I may need to add a task for the future, and I will probably keep the change to myself for a bit until I have it settled and running smoothly.

Related Articles

TSQL Tuesdays # 50

T-SQL Database Alert/Mail System

Thank you for visiting!