Automation: every lazy DBA’s best friend; in some situations, a ticket to sanity.

T-SQL Tuesday #15: Automation is the way to a DBA's heart

This month’s T-SQL Tuesday is brought to you by Pat Wright (blog | @SqlAsylum). The 15^th topic for the monthly blog party is, as has been mentioned, Automation in SQL Server. I’m pretty excited to read this month’s posts to see what kind of crazy things everyone does. Most of these posts are probably going to be big on example scripts and code samples, as one would expect for such a topic. This one–for better or worse–won’t.

I have to admit that I haven’t done a lot of from-scratch automation in my day. Lots of things on the list at work right now, but implementation is still pending. As a result, I was afraid that I wouldn’t have anything to talk about this month, but I thought of a goofy direction that I can take this in.

Like many of us who do it for real now, I started my path to DBA-ness (uh… that’s unfortunate) as an Accidental DBA. Since I didn’t know any better at the time, I did a lot (OK, pretty much all) of administrative DBA tasks with the UI. Need to back up a database? Right-click | Tasks | Backup! Want to create an index? Fire up DTA! Use the GUI to pick the columns. Need to do that for more than one DB? Guess you’re going to be there for a while.

This technique obviously gets the job done (for the most part), but there is a lot of room for improvement.

“Automation” doesn’t have to be fancy

If you’re using the GUI for a lot of tasks, there’s an easy & cheap way to “automate” a lot of what you’re doing. Simply: Script stuff out. One doesn’t need to be a master of T-SQL syntax to start doing this, either. With SQL 2005 and above, making the transition from GUI to scripted tasks is pretty easy.

The Backup Database dialog's "Script" menu

Just about every dialog box in SSMS has a “Script” button at the top. This control will script out whatever changes have been made in the dialog box. For example, if you bring up the Backup Database dialog, fill out the options & destination file location as desired, and then use the Script button to output that to a new Query window, you will wind up with a complete, functional BACKUP DATABASE command with all of the same settings that were selected in the GUI window. Mash F5 on that puppy and you’ll have your backup, just like you wanted it.

How does this classify as automation?

Spirit of the law, folks, spirit of the law 😉

Alright, I admit I might be stretching it a little bit here. I also know that just because two techniques solve the same set of problems doesn’t mean they can be classified the same way.

That said, consider some of the reasons that you automate big tasks:

• Ease of consistent repeatability
• Removal of the human element
• Speed
• Autonomy if you’re out of the office and someone is filling in

These same things make running T-SQL scripts instead of using the GUI for tasks a better idea:

• As long as you don’t change the script before you re-run it, the same thing will happen repeatedly (unless of course the script does something like add a particular column to a table a second time). This is especially important when doing things such as migrating a new table through Dev, Test, Stage, and Production over the lifecycle of a project.
• Setting options in a GUI window is prone to mis-clicks or flat-out forgetting to change a setting from the default.
• The script is ready to go—running the action is as fast as opening the script file, checking it to make sure it is the one you’re expecting it to be, and mashing F5. This makes implementing the change a fast process, instead of having to click a bunch of radio buttons/checkboxes/whathaveyou, then verifying all of the settings before hitting OK.
• If you’re out of the office, but something still needs to be deployed, it’s easy for the fill-in DBA (the boss?) to grab the scripts that have been prepared and run them. This is easier than walking through a list of checkboxes to check on a UI screen OR you try to remember everything from memory if the correct settings haven’t been written down.

Considering how I grew into a DBA, making this leap from pointing and clicking for just about everything to typing out ALTER TABLE instead, took some work. In the end, scripting everything is better in pretty much every conceivable way, even if it is hard at first.

If you’re a little GUI-heavy still and like the idea of automating the work that you do, letting go of the UI and embracing the big, blank T-SQL canvas is Step 1. The effort will be worth it, and you’ll feel like you’ve really automated tasks.