T-SQL Tuesday #110 - Automation

For T-SQL Tuesday #110, Garry Bargsley (b|t) asks, what does “Automate All the Things” mean to you? What do you want to automate or what automation are you proud of completing? What is your go-to technology for automation?

Automation certainly seems to remain a popular topic in the data community. My perception is that its rise in popularity and use has coincided with maturation of PowerShell, its community, and scripting tools and libraries. It's been a welcome sight to see so many DBAs and admin-types embrace scripting. While my career has always been tightly coupled with data, I started out as a developer. The mindset (and even the need) for automation was ingrained in me very early in my career.

Waaaay back in the Windows XP era, I was a junior VB6 developer for a small software company. Client/server apps and N-tier were all the rage, although web apps were getting a lot of attention. I was in charge of building the installation package for deploying our app. The app was comprised of a handful of exe's and dll's, along with SQL scripts, report files, ASP files, and some other redistributable bits. When I started out as the "build master", I did everything manually. Those steps included things such as:

• Telling all the other developers I was about to start a build and yelling at them to check in their code.
• Getting the latest code from several branches of Visual Source Safe.
• Incrementing version numbers in the source code.
• Compiling all the exe's and dll's in a specific order.
• Changing GUIDs within the InstallShield project.
• Copying the finished build files (setup.exe, etc.) to a UNC share for the QA team to access.
• Sending out a "new build" email.

I soon realized just how fragile the process was. One missed or bungled step and the build was mostly worthless. I'd frequently get questioned if I "missed something" because bugs that were supposed to be fixed were still reproducible. Files that were supposed to be "new" had last weeks date/time stamp. Doing everything manually wasn't consistent enough. I had to adapt and evolve. So I did.

I was still pretty green, but a few small steps led to some larger ones. I wrote a DOS batch script to copy the deployment files from my machine to the network. Then I realized I could invoke Visual Source Safe from the command line to get all the latest code (with the current date/time stamp too). Remember the old "Windows Messenger" Windows NT service? Back in the day, I would bombard my fellow devs with message boxes telling them the build was about to start. If there were tasks I couldn't accomplish in DOS, I'd use VBScript. I got fancier with time--the InstallShield application had an API that I could use to update GUIDs, link new deployment files, and start a new project build. I learned how to send email from a script. I started spinning up a new VM from a template so the build machine was always "clean". Almost anything was possible!

By the time I left the company, the build process was consistent, repeatable, and fully automated. All I had to do was click a file to launch a script. Someone even drew a cartoon to commemorate my achievements:

Fast Forward

These days, I'll automate just about anything: launching reports, generating passwords, gathering data, to name a few. I use mostly T-SQL for automation because the majority of my work is within an instance of SQL Server. For multi-instance environments, I may use PowerShell (and especially dbatools). However, I still write a fair amount of code in C# with Visual Studio. And you know, I still use DOS batch files for many tasks.