Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #110 – Automate all the things

TSQL2SDAY-150x150I’m getting the new year started by joining in the T-SQL Tuesday party. Thanks to Garry Bargsley (b | t) for hosting this month’s topic: Automate All the Things.

What stuck out most to me is when Garry asked the question:

What does “Automate All the Things” mean to you?

It’s not the real focus of what he’s asking us to talk about it, but I think it’s important to talk about. When I think of automation, I think of setting up routines that need to occur on a regular schedule. This means that you would automate system checks, backups, maintenance, etc. that run every night. In some ways, I see automation as a tool that helps with mainly administrative tasks.

Well I don’t work on the administrative side of the house. I work in development so I don’t have to deal with the day-to-day maintenance work that benefits from automation. Now, I do see automation used in other places in development – for example, unit tests that run every time a build is kicked off, QA automation tests, etc. I know database work can have unit tests but it’s somehow hasn’t filter down to the way that I have worked. (Yet.)

However, I have started to recognize when I have to do a series of steps over and over for a reason – usually involving resetting my environment for testing one thing or another. I’ve started collecting the small pieces together and started creating mini scripts so I can do these steps again. And I’ve started modifying them so I can make them work in any situation. For example, I need to move data between two databases but the database names may be different so I added a parameter and made the statements dynamic so I just need to supply the right database names. Or I created a simple PowerShell that has the list of commands to run to do those few steps instead of me doing them separately each time. It’s not always a lot of steps but this way I don’t accidentally mistype something or put something in the wrong place.

In many ways, this is part of setting up automation – identifying the repeatable actions and creating a mechanism to repeat those steps in a reliable way so I know every time I run the script, each action will work correctly. While I’m not scheduling them to run at a certain time – whether it’s a calendar or from some other action, it’s laying the foundation so that if I – or my coworkers – could ever automate these actions, the basic set of scripts is ready to go.

Most of my database work is still done using T-SQL scripts. Call me old-fashioned but I like using these scripts because I can use them directly in a query window in Management Studio or I can call them from PowerShell and I know I’m doing the same exact actions regardless of how I execute the script. I’ve started to use PowerShell more to run these scripts. I still feel like I’m learning PowerShell, but I’m definitely getting more comfortable and improving my skills. None of this may be the cutting edge technology but it gets the job done.

One day, I’ll probably be able to implement what I consider to be true automation. Who knows – maybe someone else will consider what I’m doing “true automation.” But until I can really automate these actions, being able to set things up so that will be possible will have to do.