I have come across the question of managing databases during development in a number of forums and I thought I would outline the way my company manages the database for our product. We build an ERP (
Enterprise Resource Planning) system for manufacturing companies. As you can imagine, the database is an important part of our product.
1. Each Developer Should Have Their Own Database
The first thing to mention about our database environment is that every developer has SQL Server installed on their development machine and develops on a local copy of the database. This allows the developer to make changes without having to worry about how it will effect other developers. It also allows them to test using stable data (it's difficult to test your code if other people are making changes to data that you need).
2. Script Database Changes
When a developer needs to make a change to the database they make their changes to a script and check it into VSS. These scripts are named in a way to ensure that they run in order. We use the product version (<major>.<minor>.<maintenance>) followed by a script number and then a readable name (eg, 1.0.0.100 My Script.sql). The naming conventions allows us to just process the scripts alphabetically instead of having to create a build configuration file for the order. We leave gaps in the numbering so that we can add scripts without having to rename all of the scripts that come after it. For example, if we add another script to our build after 1.0.0.100 My Script.sql, we would call it 1.0.0-110 Another Script.sql (see screenshot below).
[image lost in blog update]
3. Scripts Should Only Run Once
In order to prevent a script from running multiple times, we keep track of what scripts have been run in the database. The table is fairly simple, the key field being the name of the script (the name of the file without the extension).
Our table looks something like this (we actually have more columns, but these are the most interesting ones):
[image lost in blog update]
Before a script does anything interesting we make sure that it has not been run yet by checking this table. If it has been run, it should just exit gracefully. The sql for this looks something like this:
DECLARE @ScriptName NVARCHAR(255)
SET @ScriptName = '1.0.0.100 My Script'
IF (SELECT COUNT(*) FROM VersionHistory WHERE ScriptName = @ScriptName) > 0
BEGIN
PRINT 'Script ' + @ScriptName + ' has already been run.'
RETURN
END
PRINT 'Run script ' + @ScriptName + '.'
INSERT INTO VersionHistory (ScriptName, StartTime)
VALUES (@ScriptName, GETDATE())
-- Your SQL here.
UPDATE VersionHistory
SET EndTime = GETDATE()
WHERE ScriptName = @ScriptName
Once a script has been released to production it should never be modified (not all of the developers at my company agree with me on this point). This makes testing the script much simpler. Of course, if there is a bug in the script, you need to fix the bug, but you should do that in another script.
There are a couple of different options you can pick from to fix a bug in a script, the one you choose will depend on the severity of the bug.
- If the bug is easily correctable the simplest solution would be to leave the buggy script alone and create another script to hotfix it.
- If the bug is not easily correctable (eg, it irretrievably destroys data) you should remove the script from the installation process and replace it with a corrected script with a new name (just add 1 to the order; eg, 1.0.0-101 My Script.sql). This new script is not intended to fix the error for people that have already run the original script. If the buggy script was already run on the database, the new script should report an error.
If customers have already run the bad script and are unable to rollback their database (perhaps the bug wasn't noticed until months later), you will probably need to create another script for those customers. This script should only run if the bad script was run. If it hasn't, it should exit gracefully.
4. Script Objects Separately
There are some database objects that can easily be recreated without having an impact on data such as stored procedures, functions, views, etc. We place each one in a separate file so they can be easily maintained.
During the build process we place them all in a single file per type (eg, a single script for all stored procedures). This ensures that they are run in the correct order (we place them in order based on dependencies to other objects). It also makes it easier to manually upgrade databases when necessary (it's easier to run a single script then a hundred or more).
5. Create Your Database With Your Scheduled Build
We have a nightly build process that creates the database. The build basically grabs a copy of the database from the previous release to production and runs the latest version scripts on it. When we release a new version of the product, the scripts are removed from the current branch in source control so that the script directory only contains scripts for the current release.
We have recently released version 8.1 of our product and have been using this process for many years. Although there may be better ways of managing your database while in development, this process has worked well for us.