T-SQL Tuesday #50–Automation

tsqltuesdayI missed last month and I was a bit sad, but I was traveling the week before and speaking, and then was on vacation. No time to write a post, and it was a tough topic from Robert Davis. However I’m back this month, and I’m thrilled to participate in topic #50.

This month’s topic comes from @sqlchow who gives us the topic of automation. How would we do it? What would we do? What tools? It’s a great topic.

This is part of the monthly blog party, started by Adam Machanic. To participate, grab the logo above, write a post on the 2nd Tuesday of the month, and link to the invitation (from @sqlchow this week). If you want to host, contact Adam (blog|twitter).


I’m going to tackle the topic of tools, since I’ve been working on the Powershell Challenge.

I’ve used lots of tools for automation in my career. I started with DOS and batch files professionally, which were easy for me after a few years of Korn shell and C shell working on Sun OS in college. The idea of scripting in a batch file made perfect sense, so much so that I had alias batch files for various Unix commands, like ls, ps, etc. that I used often. We even had 411.bat on our network, that would grep a text file of our internal phonebook to return extensions.

As I moved to SQL Server, I found myself frustrated with some of the limitations that SQL had, especially while working with files. Batch files weren’t great, and a full C++ or VB program was too heavyweight. I eventually moved to VBScript, and learned to love the FileSystemObject. I had tried Perl briefly, but abandoned it because getting ActivePerl installed on all machines was too much of a hassle.

However I’ve started to move to PowerShell (PoSh) and I am starting to really like the idea of quick modules and cmdlets written in the language. It has a lot of the piping and simplicity of Perl, but the power of working with objects. It’s also quick to write scripts. While I find it cumbersome for now, that’s me, not the language.

These days, if I needed to automate something in SQL Server, I think I’d really look hard at PoSh if I needed to work outside of an instance. Meaning access the host system, work with files, or connect to multiple instances.

Inside once instance, I still love T-SQL scripting where I can use it.

One further note, scheduling is an important part of automation. While there are some good enterprise level schedulers, they aren’t always available. That’s why I tend to use the SQL Agent to do lots of scheduling for me. It’s more reliable than the Windows scheduler built into all Windows hosts, and provides some notification capabilities.

More importantly, I can query the status of my automated tasks.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.