The first and overriding thought of the experienced developer given any new development task is, or should be, ‘How the heck am I going to test this?’. I wonder, though, how often developers neglect to ask this question. Nowadays, with Visual Studio, the debugging facilities are so good you can muddle along happily for a long time, fixing problems on the fly. However, eventually the skies will darken around your development project, or your team members ostracize you, as they struggle to understand and test the code you’ve produced.

In fact, the real question to ask is subtly different: ‘How the heck am I going to test this in such a way that it will guide me to find out what is wrong’? In other words, we need not only ‘testability’, but also ease of diagnosis. There are no shortcuts; you must design the application, from the ground-up, for ease of testing and diagnosis, and this is an especially urgent requirement, in a dynamic multiuser system such as a database.

For a database to be inherently testable, it must be possible to control the state of the database under test, easily observe the results, test parts of the database in isolation, and automate all the necessary tests. The database must be clearly partitioned into logical components, and must not share functionality. It must be intelligible and well-structured. It must be possible to script out a whole variety of processes that are ‘idempotent’ in the sense that, each time they are run they should give the same result, so you can check they do so.

We can’t stop there though; we must then make it easy to diagnose. This can vastly reduce the total time and effort of debugging. A debugging framework is like scaffolding around a house. You might think it was pointless putting up scaffolding when you eventually won’t need it and will take it down. However, any experienced builder will just laugh if you try to build a house without it.

So, what is this ‘scaffolding’ when applied to database development? Although there are some startlingly good system functions for investigating the running SQL Server system, in general, they are most effective when used as part of an intelligent instrumentation, logging and recording system that will allow you to easily reproduce any failure or stress condition.

I’ll log all calls to the interface in as much detail as possible (stored procedures are great for this). I’ll build in a lot of reports that will tell me what went on in a batch or process. I’ll create scripts from the logs that can repeat a state that caused problems. I’ll implement whatever I can to log changes to the data.

If development and test are rather separate concerns in your organization, then you’ll find that by working in this way the relationship between developers and testers becomes far more intertwined and cooperative. Maybe I shall call this approach ‘Database DevTest’. After all, having hurdled the database testing fence, with Database DevTest, surely leaping the great deployment barrier, using Database DevOps, should much easier?