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.