Thursday, 17 March 2011
I have spent a lot of time migrating database changes between environments to support the various stages in the sofware development lifecycle. I have succeeded in automating aspects of this, and it has always been something of a holy grail of mine to create better automation in this area. Now a product has been released that seems to have achieved this, and done it very well.
Almost all serious software development now makes use of source control, and many projects use continuous integration. Code written in C# and Java is nicely controlled using products such SVN and TFS. Developers can view the history of check-ins and releases, see what changes were made , revert to old versions etc. Source control also provides the basis for continuous integration using products such as Team City, which marshal scripted builds and tests. Some products such as TFS, go even further to provide traceability between source changes and project tasks and requirements. All lovely stuff, that provides massive productivity gains!
There is however one major component that has been slow in coming to the party: the database.
A lot of software development involves the design and development of database objects such as tables and sometimes views, functions and procedures. In addition to this structure, a database often contains static reference data that needs to be present in order for a system to operate. Examples are transaction types, or user privilege types that need to be stored as records in the database. The changes in the database are typically co-dependent with code changes, so that related changes in code and database should be released at the same time, and the system fails if they are not.
So here is the problem:
Today it is rare that database development and release is smoothly integrated with the development and release of code. This lack of integration and standardisation creates a number of difficulties:
- Integrating database changes into automated build/test cycles is manual or awkward
- Mistakes often occur, due to poor synchronisation of changes between code and database
Just to be more clear, let’s use an example of a team developing a financial modelling product in C# which stores financial models and client information in a SQL Server database. The team uses Team City, so that when developers check-in a change, the code is automatically built and tested on a server. Check-ins can easily be tagged and a release built and deployed in a test or production environment. Now, a change is made, involving the addition of a new field to the model and a change of another field from integer to string. To support this change, a column is added to the FinancialModel table in the database, and another column is changed from Int to Varchar. When the code change is deployed, the system is broken unless the database changes are also deployed. Conversely if the database changes are deployed, the system is broken until the code changes are deployed.
The database changes would ideally be managed in the same way as code , using source control and automated deployment . But this is not so easy, and here is why:
- Database objects don’t exist as simple files that can be versioned.
- Database deployment cannot simply involve the copying and replacement of the target objects, because this may involve loss of user data. For instance, deploying a change to the FinancialModel table cannot involve simply replacing the FinancialModel table, because valuable data would be lost.
Deployment of database changes, involves the creation of alter and update scripts. Often these are hand-crafted, although there are tools that can automate the production of database synchronisation/deployment scripts. Examples of these tools are Red-Gate SQL Compare for SQL Server and Toad and SQL-Developer for Oracle .
Now, there is a new tool that takes the next step and integrates database with source control. This is an important enabling technology for software developers, because it allows the database objects to be included in the continuous build and release cycle that works so well with code.
A user review with some screenshots.
Unfortunately, this is not open source. The costs are:
- SQL Source Control: $295 (A copy is needed for each developer who needs to checkin/out changes on the database)
- SQL DeveloperBundle: $1500 (One copy is needed per build server, to push changes to the target database)
- Fully integrated with SQL Server Management Studio
- Connect databases to Team Foundation Server (TFS) or Subversion (SVN)
- Commit new, edited, and dropped object to source control with a couple of clicks
- Easily get the latest version of objects from source control
- View SQL differences between objects in the database and source control
- Undo (revert) uncommitted schema changes
- Automate schema deployments with SQL Compare Pro and data deployment with SQL Data Compare Pro
- View development history for easy access to specific database versions
- Support for Vault, Vault Pro, Mercurial, Perforce, CVS, Git, and Bazaar due Q1 2011
- Unfortunately only works for SQL Server at the moment. (An Oracle version may come out in future because Red-Gate have an Oracle version of some other key tools)