Last Thursday morning @ the PASS Summit I participated in a panel discussion on automating DBA tasks. It was a great discussion and I was humbled by the other panel members. During the discussion I jotted down a few notes that I didn’t get to mention during the talk. The talk was recorded and will likely be available on Quest’s site in the next few days or so.
The first distinction to draw is the difference between scripting and automating tasks. Scripting tasks simply means there is some programmatic representation of the task you want to perform. The program can be in any language you choose: Python, PowerShell, C#, Perl, T-SQL, SSIS, etc. The script does all of the necessary work. Automating tasks simply means the script is automatically initiated by some action. The action could be a SQL Server Agent job, a Windows Scheduler task, an Extended Event (X-Event), a server/database trigger, a Service Broker event, etc. In other words, instead of the script being manually executed in an interactive way it’s automatically executed in an unattended manner.
During the discussion I made the statement that everything should be scripted but only the right things should be automated. This is one of those high-level statements that has a lot of room for interpretation. But that’s the point. You shouldn’t think about scripting something or not. If it’s a task you do more than once (in a day, week, hour, month, quarter, etc) you should script it. If it’s a task that others needs to also perform you should script it. But you should think if it should be automated or not. Only the right actions should be automated. Remember, you probably aren’t going to be there when something goes wrong. Also, if the task requires reasoning that’s not easy to capture in a script you don’t want to automate it. In any event I wanted to capture some best practices for scripting.
Most of the scripts I come across aren’t very good. Sure they perform the necessary task but that’s about it; venture a little off the expected path and everything goes wrong. The script author simply took the T-SQL script and stuck it in a BAT file that calls SQLCMD. Here are some bullet points (in no particular order) that you should consider when creating your scripts.
It’s very easy to ignore these items and end up with a bunch of .bat or .ps1 files that you have no idea what they do. If you spend a little time up front it’ll pay dividends. Your co-workers will come to you to borrow your scripts or for help on their own scripts. You’ll be know as the Script Guru! And many great things will come your way.
Okay, maybe those last two points are a bit over the top. In all seriousness though, we’re professionals and we should treat our scripts to a professional touch.
nicely put Dan. i enjoyed the panel discussion as well, though i doubt i was able to humble you in any way.
nice job on drums that night as well...
Thanks Tom. If I could figure out a way to make drumming my day gig and MS my hobby I'd do it in a heart beat!
Is there a difference between scripting and automation. According to Dan http://blogs.msdn.com/dtjones