I’m looking at the practicalities of using Continuous Delivery/Agile techniques where the database is a “high scale” project in Agile terms, and therefore relatively difficult (for more detail about this Agile terminology refer to Scott Ambler’s writings).
As well as thinking about the big issues, I want to start putting together some of the pieces to make it work. I’m doing this in Sql Server 2014 Transact-Sql. I’ve started with automated testing of reporting procedures. There may be utilities that can do this; finding suitable tools is part of the research. It still seems worthwhile having alternatives in Transact-Sql if possible. There’s no purchase or installation to approve, and the code is easy to understand and to edit.
To avoid confusion the test code will not sit in the same schema as the application code. I decided to kick off with “test-first” methods as much as possible. Test-first makes me think before diving into the detail. I haven’t decided how far to go with it, but for schema changes it’s perfect, and leaves you with a script that can be used to check that all the objects needed are present. This could be useful at various times before or after installation. There definitely are other ways of doing it, but in Transact-Sql it’s simple and with TDD you get the code automatically; it’s not on anybody’s “to do” list.
I’m starting to put the code onto GitHub here and you’re welcome to take a look. I’ve written a stored procedure “spObjectExists” which simplifies checking of objects in the database. For the moment I’m not trying to put any other checks in stored procedures; the other script in the repository shows how in-line tests can be written. It’s still quite simple. I’m new to GitHub and Open Source so please let me know if you have any problems accessing it.