Back To Basics: Managing Databases

5 06 2010

On a new clients site the other day, observed that over time the more companies I work for the deeper my knowledge for applying effective work practices becomes. In other words, over time you see things that work well, and things that don’t. I’m talking about simple practices that when applied to teams result more quality and/or efficient software.

Some companies I’ve worked for have demonstrated mature ITIL based change control processes with dedicated change control teams and separate environments for development and testing, but sadly over time they have become so out of sync with the production environment that they provide absolutely no indication of what will happen in the real production environment. And management wonder why production deploys are so unsuccessful having spent gazillions on ITIL training and manuals. A simple problem with a simple solution, but in the real world can be rarely practiced.

Another that I see regularly is an effective way of managing database change across a development team. Here I present a technique so primitive and proven, that there is no requirement that you have the database tooling that ships with the likes of Visual Studio Team System (VSTS) 2008 Database Edition or Redgate SQL Compare. If a team has such tools at their disposal then great use them—they can make life easier, but I am dumbfounded by the number of environments I see where database change is completely unmanaged.

Yes, databases and their associated artefacts (functions, triggers, message broker queues and so on) should be managed, and versioned. Again a simple problem with a simple solution, but in the real world tends to be practiced poorly.

In the source repository that the team uses, create a directory hierarchy that implies some sort of sequence (e.g. prefix with a numeral). Start off with scripting the database and its requirements, such as filegroup options and collation types etc. Remove any code generated guff, to keep the scripts as clean and as readable as possible. Then move on to tables, and then objects that work with the tables such as foreign keys, triggers, procedures, functions. An example structure could look like this:

– “01Database”
– “02 Tables”
– “03 Foreign Keys”
– “04 Triggers”
– “05 Stored Procedures”
– “06 Functions”
– “07 Queues”
– …
– “10 Data”

Over the lifecycle of the project this structure should be completely populated with the necessary artifacts to build the target database from scratch. No restoration of backups needed.

Because the number of scripts contained in a single directory could become overwhelming with time, a copy of the below batch script “all.bat” could be placed in each directory that enumerates and concatenates every “.sql” file in the containing directory to produce one large sql script “all.sql”. Running in 150 stored procedure scripts then become a simple matter as running in “all.sql” contained in the stored procedure hive.

@echo off

@rem type NUL>"_all.sql"
del /F /Q "_all.sql"

for /f %%a in ('dir /b *.sql') do (
type %%a >> _all.sql

When it comes to scripting the data (lookup data and sample data should be versioned), I find it hard to pass up the simplicity of the sp_generate_inserts gem I found a few years ago. Its basically a stored procedure that get created in your master database (therefore resolvable in any db’s on the same instance), that provides a rich set of options for scripting your data (e.g. EXECUTE sp_generate_inserts footable, @ommit_identity=1).




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: