THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Scripting for the out-of-band attention

I have written a lot of throw-away scripts, some of which were written to control the execution of a large number of SQL scripts. The logic of these control scripts is often simple, but their execution can last for a long time (e.g. hours). When writing this type of control scripts, people would generally give some thoughts to improve robustness in case things crap out unexpectedly in the middle of their running.

When a script is long running, chances are people may change their minds after the script has started running, and want to abort it. I might be wrong, but it seems that not much explicit consideration is given to this change-of-mind scenario in scripting-related discussions.

True, if you have spent a lot of time making the control script and the controlled SQL scripts super robust to the extent that you can just Ctrl-C to abort their execution and expect things to be in a good enough state, good for you! But we are talking about throw-away scripts. As much as we want to make them super robust, it may not be cost effective to actually do so. Typically, a throw-away script is written to deal with a scenario that is sufficiently narrow and we know what craps we may have to deal with and/or how we can handle them if the script indeed aborts unexpectedly. However, that does not mean that we should just go ahead with Ctrl-C, well, unless you enjoy dealing with craps that may come out of Ctrl-C. It can be done better.

Let me give a specific example. 

Assume that you have data on some gadget items, orders, item stocks, fulfillments, and item returns, and for each month, you have a separate table for items, a separate table for orders, and so on. Since you need to convert all these tables to different schema for an upgrade, you have prepared T-SQL conversion scripts, one for each table. If your data go back for many years, you have a lot of migration scripts to execute for the upgrade. Being a lazy database professional, you write a control script to loop through all the months, and then another loop to go through the tables for that month, executing their respective conversion scripts in these two nested loops.

Now, just when you have started running this control script, you may suddenly realize that maybe you should have divided the tables into several groups, and execute the conversion scripts for each group in parallel. So you want to stop the current execution, and re-arrange your script. Or you just want to stop the execution of the control script for whatever reason that you may fancy.

As said, you can hit Ctrl-C and hope for the best. Or you can give some thought to this requirement when writing your control script, and build the logic into your script so that it responds to external events and exits the script gracefully at a certain desirable point. This can be something extremely simple. For instance, in the control script for the above-mentioned conversion, I may add a simple check upon the entry into each loop to see whether a dummy file can be opened. If yes, the script continues; if not, the script exits. This gives me a means to tell the script, out of band, to abort by simply creating or deleting that dummy file. Alternatively, if you want to be fancier, you can have the script re-check a config file repeatedly at certain points of its execution. By changing the config value, you can alter the script behavior out of band dynamically. The point is that there are many simple ways to signal the control script out of band if you give this potential requirement a little bit of thought in advance.

If you are writing a serious piece of software, what I'm rambling about here goes without saying. Scripting is a different beast; you often have to trade off robustness for 'time-to-market'. I just wouldn't want to see you going overboard in the process, forgetting that a long-running script may behave much more gracefully if it's prepared to respond to the attention out-of-band.

Published Saturday, November 20, 2010 10:53 PM by Linchi Shea


No Comments
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement