Although development teams are inching closer toward their ideal of a fully automated build and deployment strategy for their applications, a stage remains that is obstinately resistant to automation. Database deployment, and the handover to production support is both inevitable and necessary, but it is currently a manual process. This is particularly tricky in larger companies. The production DBAs and SysAdmin team have to inspect the database deployment script to root out at least the most offensive crimes against security, documentation, performance, set-based logic and compliance with legislation. It can be a frustrating phase for the developer and DBA alike. Surely, there is a way to automate this process, at least partially?

Why is this dark side of the deployment process so painful? The first problem is that it is a purely manual process, and the second difficulty is that, typically, it occurs a long way down the development process, when both sides feel the pressure of deadlines keenly.

A recent article by Dave Ballantyne suggests a possible solution. It makes use of a SQL Parser, plus PowerShell scripting, to look for particular patterns in T-SQL database synchronization scripts. Dave’s mission was to seek out and remove from his database scripts “pollution” arising from replication-related objects that exist in production but not in Source Control, but the technique seems to have much wider application.

Using a full SQL parser, which understands the context of each word extracted, along with regular expressions, we can dissect the script with very granular control, looking for all sorts of coding misdemeanors. In a recent article, Jorge Segarra describes an amusingly evil way to prevent use of SELECT *, but possibly a kinder approach is to automate the parsing of deployment scripts, looking for this “anti-pattern”. We could also ferret out other common code smells such as lack of WHERE clause, use of hints, bad security practices (“trojan” stored procedures that grant elevated rights), and so on.

With regular releases, and an easier, quicker way for DBAs to run the obligatory checks on code, there is a strong incentive to perform these checks much earlier in the process, and to catch problems quickly. This would surely allow the culture of production and support to seep further into development, and allow production staff to offer advice that will lead to much less painful deployments.

We’d be interested to know what you feel are the major difficulties of deploying database applications regularly Are they technical or organizational? Has this traditional ‘dark side’ now been filled with the light of Agile processes, or is it still a problem?

Cheers,

Tony.