Following on from my previous article on git source control, I’ll now take a look at a few solutions for the other side of development : Database structure

There are 2 sides to most development projects.  The code, be that business logic, UI or reports is easily  via a variety of solutions (SVN, CVS, git, VSS, etc)

The other side of the coin is that of the database.  Most applications will consume some form of data in their operation.  While some may rely on external services to provide this, most will have some form of local storage to store all the elements necessary for it to function.  Many site will have space for user logins, product information, shopping baskets, news, events, documentation, the list is endless

In the same vein as development, the data stores usually starts as a small collection of tables.  As functionality in the codebase grows, so does the number of tables needed to store all the different types of data that are being coded for.  So, as developers make changes to their sections of code and can commit their changes to a repository, what is there on the database side?  How does one track changes and additions to tables and stored procedures?

It’s definitely something that is becoming more of a necessity in these days of incremental release.  Traditionally, a new database would have to be created in it’s entirety. Not really a problem for development database that are used prior to release.  The development code can be expected to be quite fluid and changes are expected to occur frequently.  Once that database has been released to production, this becomes impossible.  Customers are very reluctant to lose historic data, user tracking relies upon it.  In this case, changes can be made piecemeal and scripts generated to map the single changes to other instances of the database

Fine if you’re using the DEV>UAT>Production strategy.  A single setup coordinator can orchestrate the script from a developer and apply that to the UAT database.  When the QA team are happy that the changes are working, this same script can then be applied to the production database, preserving existing data

However, when development becomes more distributed that a single developer and single production database, things start getting a little trickier to manage.  Sending scripts around becomes impossible.  People might not have the very latest build so scripts that rely on a series of changes will fail

This is where a central source control solution come in.  There are tools for applying these single change in order so that dependencies can be tracked.  Red gate have a fantastic tool which integrates natively with SVN so map changes, and there are some other really usable open-source tools available which can target more databases, but with more of an onus on developer collaboration to make the changes in sequence

Red gate's tool integrates only with SqlServer but is a valuable addition to their Sql toolbelt set of tools.  I’ve been using them for years and can’t fault them for mapping changes to production databases.  Their latest tool is no exception.  It works as an addition to the Sql Management Studio which ships with SqlServer.  Extra tool menus allow you to attach a database to an SVN repository.  Once linked, the tool will make an initial snapshot of it’s structure.  Then each change is managed within the tool, allowing the developer to commit them one by one as they are made.  When another developer needs to get a copy of the development database, they only have to make an empty database, link that to the central repository and the tool then maps the tables and stored procedures across, including any dependencies that are found.  The new developer can also make changes to their database, commit these one by one and the original developer will see these as updates and can apply them to their database.  The process can be scaled up in much the same way as regular development source control  solutions.  Distributed developers are given access to the repository and off they go

The open source solution I’ve looked at closely is deltaSql.  This employs a custom server for mapping changes which runs on Apache, making it cross platform compatible with Windows or Linux.  It uses a similar commit and update strategy but pulling the individual scripts from it’s central server instead of SVN.  This solution also allows for more databases to be catalogued, including mySQL and postgreSQL and Oracle.  Lit integrates into the Eclipse IDE via asset of open source windows so is totally free to use

Both solutions provide a lightweight method of mapping changes from a distributed set of users to a central location over multiple database solutions.  Source control is essential for most projects and databases should be no different.  They are, in some cases, more complex than the code that drives them, so being able to sync the change with your peers is an absolute necessity.  Throw in a few QA houses, production databases and data warehousing solutions and the "Drop and Reload" method is not going to work out.  Both offering provide a less painful way to make structure changes in a controlled manner and provide a means to roll these changes back and start again should testing prove unsuccessful.  Very powerful!