Automation starts by doing something once, very very carefully: T-SQL Tuesday #110
5January 8, 2019 by Kenneth Fisher
After a while the intros for my T-SQL Tuesday posts get a little boring. So without any further ado, it’s that time, Garry Bargsley (b/t) is our host and the subject is
Automate all the things
Now Garry asked us to tell about something we automated or our favorite technology for automation. He gave us a list of some great technologies (T-SQL got an honorable mention? Really Garry? Really? Honorable mention??) I frequently end up going a bit meta on T-SQL Tuesday and this month is nothing different. What I want to start by pointing out is that the language you use to write the code is not automation. Automation is the technology you use to run the code. A job program (SQL Agent maybe?), events or triggers are the most common way to handle this. Honestly depending on the tool you’re using (Policy Based Management for example) you may never write any code of your own. And having said that I’m going to completely ignore the automation part and talk about the program you are automating.
There is a very simple, basic process that you should be using when you write automation code.
Step 1: Write the code to perform the task once.
Step 2: Test and make sure that code is bulletproof.
Step 3: Test and make sure that code is even more bulletproof.
Step 4: … well you get the idea.
…..
Notice anything here? Working with automation is a lot like working with dynamic SQL. You aren’t going to be sitting there watching it and you have very limited control over the inputs. Once you have a piece of code that works on your limited set of examples you need to make sure it works on every possible input. If you are working with database names you have to account for spaces in the names, symbols, hidden characters, even emojis.
I want to point out that Step 1 is likely to be doing multiple things. Just as a for example, let’s say you want to create a piece of code that checks the page verify setting on all of the databases on an instance. You want to make sure it’s CHECKSUM and if it’s not change it. Yes, you may be doing multiple ALTER DATABASE commands but it’s still a single task.
Make sure all of the databases on the instance have CHECKSUM as their page verify setting.
Now finally we add the automation part. In this case, it might be a scheduled job that runs once a week.
Step n-1: Add in the automation piece.
Step n: Test the automation.
And yes, test your automation piece. It’s frequently pretty simple, but you’re going to be annoyed if in a month you go back and check and the job never ran, the trigger never fired (or never fired correctly), etc.
SQL Studies 
Great post.
Testing your automation is one thing. but things also happen to systems because I doubt any of us work/program/automate in a vacuum. That’s why I prescribe to the email be all the time methodology. If the automation succeeds I get an email, if it fails I get an email. Frequently these success emails are funneled into a folder via a rule in outlook, but I can tell at a glance each morning judging my the number of unread items in the folder if everything is looking good. If the number is less than expected, I can investigate to see what didn’t fire off as anticipated. If the number is what I was expecting, I can mark all the items as read or delete them from the folder with a couple of clicks pretty easily. Just some food for thought.
Personally I’m a fan of collecting all of the information together in a single place then sending out an email that either says “all is well” or “here is the list of jobs that failed”. But yes, error/success reporting is also an important part of automation.
Thanks for the post. In the automation I have done, I would create a logging process. So failures were logged to a central repository and then also send alerts of failures via email. I would then also do a roll-up of failures in a weekly “report” to see how things were working. Personally, for me I just want to know when my automated processes are failing. Event storms drive me crazy. I also I think its important to define the scope of what needs to be automated otherwise you end up going down various rabbit holes.
Absolutely! And yes, this is exactly what I would recommend. But when you “fix” your automation procedures I would assume you just make them more bullet proof right? Make sure they can handle the current situation + any new situations that error brought to mind.
for sure. It is an ever evolving process as new issues that were not expected come to light.