T-SQL Tuesday is a monthly blog party hosted by a different blogger each month.

T-SQL Tuesday 50 – Automation

This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month. This month, SQLChow blessed us with a topic I am very passionate about. This months T-SQL Tuesday topic is automation.

I have a confession to share. I am a Lazy DBA. Those who know me won’t be shocked by reading this. Those who don’t know me. Trust me, I mean this in a good way.  My lazyness over the years has actually motivated me to be a better DBA and data professional.  I learned early on in my career that in order to be productive I must automate. No longer can we do manual daily checklists. We lose several hours that could have been spent on tasks that show our value not just hold the status quo. Automation allows us to end the cycle of repeating tasks and allows me to spend that time doing things that provide value, save the company money and make us happy.

Early Stages of Automation

Once in my career I was blessed with an opportunity to be a Database Administrator overseeing thousands of production databases. Quickly, I noticed there was no automated process for a daily checklist.  How did we know if a database backup failed due to low disk space? Hopefully, we got an email from the SQL Agent. Hopefully, someone remembered to setup an SQL Agent notification. I knew this wasn’t the answer. One of my first tasks was automating this whole process so we knew which databases passed and failed an automated daily checklist. I was able to leverage Policy Based Management (PBM) and Central Management Server (CMS) with Powershell to get this done. Little did I know it at the time, but this basic move changed my DBA Career. I got to speak at the PASS Summit in 2011 on how I evaluated my automated daily checklist against 1000+ servers during my morning coffee break. Starting to focus on performance I noticed a better way to pull this information without PBM. I build an automated framework using Powershell and CMS to automate the process to get my failed backups quicker. Still today meet DBA’s today who didn’t know you can automate your daily checklist only using native tools built into SQL Server.

Current Stages of Automation

Today, I am much more focused on performance and proactive monitoring. Learning from my past I knew I wanted to automate as much as possible. This didn’t change even though my core skills were changing.  In the past year I built some nice automated solutions that help me with performance tuning.  When I am in charge of a new instance I automate the process of monitor disk latency, proactively automate the process to monitor wait statistics. Once I have a good automated baseline I can drill deeper as needed. For example, I can find out which queries are causing my waits. It has gotten me to the point where most of the time I can find the root cause to SQL Server performance problems in ten minutes. The automated benchmark process does the heavy lifting for me so I can respond and stay as proactive as possible and provide value instead of running processes that should be automated.

 Future State of Automation

I see more things being automated.  More and more parts of the current “Production DBA” role as we know it today will be automated. This is going to open us up to doing amazing things. One day, an end to end performance tuning process will be automated.  I look forward to seeing things that we thought were not possible be possible and automated. For example, automating server procurement and deployment once fell into this realm. Now, it’s already here. It’s known as “the cloud”. I will be honest, I was shocked to see how easy and quick it is to deploy an Windows Azure Server.

What are your thoughts about automation? Where do you see it going in the future?

The post T-SQL Tuesday #50: Automation for LazyDBAs! appeared first on JohnSterrett.com SQL Server Consulting.