Automation starts by doing something once, very very carefully: T-SQL Tuesday #110

, 2019-01-08

T-SQL TuesdayAfter 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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads