This is the first of a series of posts where I will describe how to setup your database development environment to include continuous integration and foolproof automated deployment.
I’ve got to hand it to Red Gate. Their recent launch of SQL Source Control has drastically improved our ability as a team to develop database changes, and reduced the stress and headaches around deployment too.
Developing code in teams has been easy for ages, especially with distributed version control systems such as Git and Mercurial. Databases, on the other hand, have been a flaky mess of saving change scripts, which always have to be run in a particular order, and might even break your system if one is forgotten.
Team Database Development: Old School
Some characteristics of “old school” database development:
- All developers in the team work on a shared database. Well, what happens when one developer is implementing a feature in code that requires a breaking database change? All of a sudden code on everyone else’s machine can’t run until that developer commits their work.
- Developers must remember to save change scripts for every change they make. Changed a stored proc? Added a table column? Write a script for that please. Oh, and be sure to put it into the deployment script for the upcoming release, in just the right place so that it won’t delete all of our customer records. Thanks.
- Static data must be updated in scripts. Added or changed static data? Script me some updates for those please, but do make sure you put them in AFTER the create table script!
- One stressful deployment process. Did one of my devs forget to add a script to add that column? To transform that data? Maybe they forgot to script adding static data, but how will I know until the software is released? Argh!!!
Red Gate to the rescue
In upcoming posts I’ll describe how you can get your development environment to look like this:
- Developers work on their own dedicated database. Do what you want to your copy, and give me your changes once you’ve tested them!
- Confidently make changes to the database without having to remember to script them.
- Update static data on your local development database. Commit, and hey presto, it’s available to other developers, and is guaranteed to be in the next deployment.
- Automated database deployment. No having to remember what order to run scripts in, and confidence that your production database will have the exact same structure and static data as your development and test environments do.
In the next post, we’ll start by setting up your developers with their own dedicated databases.