T-SQL Tuesday #130 – Eitan’s Laws of Automation

This month, Elizabeth Noble is hosting #tsql2sday with the topic of automation. This is very similar to previous couple of T-SQL Tuesdays hosted by Ken Fisher (127) and Jess Pomfret (123). I already wrote about the topic of automation extensively in those past posts. So, instead of repeating myself, I’m gonna write about this topic a bit differently.

What I’m gonna do is try to look at the topic of automation and productivity in more general terms, in the hopes of conveying to you my overall methodology while working on a given task.

I will try to formulate a list of “Laws” or “Maxims“, that together will try to represent my personal approach to handling tasks, with the main goal in mind to maximize productivity.

I call them: Eitan’s Laws of Automation ™©® (working title, name pending, all rights reserved, copyright bla bla bla). They’re subject to change as I may better refine them in the future. But for now, here they are:

0. Zeroth Law: What doesn’t require real-time human interaction – can be automated
1. First Law: What CAN be automated – SHOULD be automated
2. Second Law: What CAN become generic enough – CAN be automated
3. Third Law: If performing an operation more than once, strive to make it generic

These “laws” are my guiding principles, my methodology for approaching tasks, my Modus Operandi, if you will. And I’ll do my best to articulate them for you so that you too could benefit from them.

Eitan’s 0th Law of Automation: What doesn’t require real-time human interaction – can be automated

This is less of a law, and more of an underlying assumption or principle. Although it should be quite basic and self-evident:

How would you know WHAT can be automated? First of all, it would have to be something that’s independent from a GUI (Graphical User Interface), and as such – independent from any human interaction during its execution.

Instead of GUIs, look into implementing as much of your work as possible using scripts, command line utilities, scheduled tasks, jobs, etc., especially if it’s something that you’re doing periodically.

Not only that, but these scripts should be as “autonomous” as possible – i.e. be able to handle errors, and log their progression and error messages somewhere that can later be investigated in the event of a problem.

For example, Microsoft Power Automate (aka Flow) has built-in logging mechanisms.

Powershell scripts can use the Start-Transcript and Stop-Transcript cmdlets, which can be used to log your output to log files.

Both Windows and Linux command line scripts can use STDOUT and STDERR to log their output to files.

SQL Server scheduled jobs can log their output to history in the MSDB database or to log files.

And so on, and so forth.

Eitan’s 1st Law of Automation: What CAN be automated – SHOULD be automated

When you’re looking to improve productivity, there’s no time to lose, and therefore no task too small to be automated.

Whatever it is that you’re doing in your day-to-day work, if it’s something that CAN somehow be automated, then it SHOULD be automated.

You gotta learn to live with such a mindset that would be constantly looming over everything that you do – “can I automate this somehow? if yes, then I gotta“.

This is your most important maxim.

Eitan’s 2nd Law of Automation: What CAN become generic enough – CAN be automated

If you’re writing a script, or a command line, or whatever it is, which is dependent on some kind of literal value, you should ask yourself – “can I use a variable instead of this literal value? can this be parameterized?”

It would be the difference between something like this (where the database name and the backup paths are literal values):

BACKUP DATABASE [AdventureWorks] TO DISK = N'D:\Backups\AdventureWorks_full_20200101.bak'

And something like this (where variables are used instead):

DECLARE @DBName SYSNAME
DECLARE @BackupFilePath NVARCHAR(4000)

SET @DBName = 'AdventureWorks'
SET @BackupFilePath = N'C:\Temp\' + @DBName + N'_full_' + CONVERT(varchar(19), GETDATE(), 112) + N'.bak'

BACKUP DATABASE @DBName TO DISK = @BackupFilePath;

If your answer is “yes, it can be parameterized”, then that means your script can be generic, and if it can be generic – then it can be automated.

Eitan’s 3rd Law of Automation: If performing an operation more than once, strive to make it generic

You don’t have the time to be doing the same things over and over again.

But also, how would you know when it’s time to write something as generic instead of with hard-coded literal values?

Well… I REALLY wanna tell you that the answer is “always”.
I truly believe that adopting yourself a habit of always writing “generically” can have a true benefit to your productivity in the long-run.

This is the sort of thing that would make your scripts adaptable, flexible, robust, and automateable.

But, I also understand that adopting such a mindset could be challenging. So, I would at least recommend adopting a rule-of-thumb saying that “if you do this thing more than once, then it’s probably time to make it generic”.

And either way, if in doubt – make it generic! Just in case, ya know?

As for the next steps after that – see the preceding laws written above 😉.

See Also

• My GitHub Repositories with open-source “generic” projects
• Same here at the Madeira Data GitHub repositories
• My GitHub Gists with various “generic” scripts
• Blog post: Let SQL Server Write Code for You