Database Version Control

There are two types of projects I am involved in these days.

1. Custom enterprise applications where we design the database and manage it.
2. Web applications, that we host, where each customer has his own copy of the database.

In both cases there are many copies of the database installed.

• Developer databases
• Test database (both internal and customer)
• Production database

It is obvious that these databases should be under some Version Control so that can be easily synchronized.
Usually when a database is upgraded, a new version of the application is issued. If only one application is using the database then application-database sync is easy.
When multiple applications are using the same database then this problem is quite difficult to solve.
This is my solution to database versioning problem:

1. In each database create the table insVersion:

   CREATE TABLE insVersion (
       nVerMjr INT NOT NULL,
       nVerMnr INT NOT NULL,
       dVer SMALLDATETIME NOT NULL DEFAULT (getdate()),
       sVerDsc NVARCHAR (1000) NOT NULL DEFAULT (''),
       CONSTRAINT PK_insVersion PRIMARY KEY CLUSTERED (nVerMjr,nVerMnr)
   );
   

2. Keep a "model database" without any production data but with all initial records (lexicons etc) and ready to deploy. Upgrade the model database with every database release but keep a backup of all the versions that ever went in production.
3. Keep a "testing database" with all production data needed for unit tests and ready to deploy. This database should be accessible by the team members to copy for development and testing. Upgrade the testing database with every database release. Care should be taken when new data are added (for new unit tests) so that old tests are not broken.
4. There is always ONE person responsible for the design of the database and all database changes (NOT a DBA. It is a member of the team and better be an expert in database modeling). When a team member wants to make a change he has to get approval and submit the change script to that person. The change scripts are the responsibility of that person.
5. Development databases are never shared. Each developer has a copy of the database on his own computer.
6. Acceptance testing is done in a "Testing database" that is NOT one of the development databases. Testing rollout contains the same steps as release rollout.
7. Before each "Acceptance testing release" create two artifacts:
   • Change script. A set of SQL DDL commands that upgrate the version of the database (ALTER TABLE etc…). A test or production database should never be upgraded by hand. That should be done ONLY using a change script. A change script contains ALL the changes needed to upgrade the production databases from one version to the next. DO NOT produce a large number of "mini scripts".
   • A new "model" database. Most experts recommend creating a set of SQL DDL commands that create the database. I find it really difficult to accomplish. Upgrading the existing model database is easy. Just run the change scripts.
8. There is an issue in synchronizing application versions with database versions. I will not expand on the topic but I must mention that if any application is broken by the database changes then the major number is changed. Otherwise the minor number suffices. That way the applications know the Major number of the database they are made to work with. When an application finds that a newer version of the database is installed, it asks the user for an upgrade and stops working.

Typical change script (ProjectNumber_Major_Minor_Database.txt)

0002_02_04_pkBa.txt:

--VERSION 2.04----------------------------------------------------
USE pkBa;
PRINT 'Server    = ' + @@SERVERNAME;
PRINT 'DataBase  = ' + DB_NAME ();
PRINT  GETDATE();
PRINT '';
DECLARE @nVerMjrCur INT, @nVerMnrCur INT,@eVerUp NCHAR(5), @nVerMjrOld INT, @nVerMnrOld INT, 
    @nVerMjrNew INT, @nVerMnrNew INT, @sVerDsc NVARCHAR(1000);
--EDIT VALUES--------------------------------------------------------------------------
    SET @nVerMjrOld=2;
    SET @nVerMnrOld=3;
    SET @eVerUp="MINOR"; //OR MAJOR
    SET @sVerDsc='New column Dividents.cDivPrev';
---------------------------------------------------------------------------------------
IF @eVerUp="MAJOR"
    SET @nVerMjrNew=@nVerMjrOld+1;
    SET @nVerMnrNew=0;
ELSE BEGIN
    SET @nVerMjrNew=@nVerMjrOld;
    SET @nVerMnrNew=@nVerMnrNew+1;
END
SELECT @nVerMjrCur=nVerMjr, @nVerMnrCur=nVerMnr FROM pkVersion ORDER BY  nVerMjr, nVerMnr;
IF @nVerMjrCur<>@nVerMjrOld OR @nVerMnrCur<>@nVerMnrOld 
BEGIN
    PRINT 'ERROR: WRONG VERSION';
    PRINT 'Database version is '+@nVerMjrCur+'.'+@nVerMnrCur+
        ' instead of '+@nVerMjrOld+'.'+@nVerMnrOld;
END ELSE BEGIN
    PRINT 'Upgrading FROM: '+@nVerMjrCur+'.'+@nVerMnrCur+
        ' TO: '+@nVerMjrNew+'.'+@nVerMnrNew;
    BEGIN TRANSACTION;
    ---------------------------------------------------------------------------------------
    INSERT insVersion (nVerMjr,nVerMnr,sVerDsc) VALUES (@nVerMjrNew,@nVerMnrNew,@sVerDsc);
    ---------------------------------------------------------------------------------------
    ALTER TABLE dbo.Dividents ADD cDivPrev money NOT NULL 
        CONSTRAINT DF_Dividents_cDivPrev DEFAULT 0;
    ---------------------------------------------------------------------------------------
    IF (@@error=0) 
        COMMIT TRANSACTION;
    ELSE
        ROLLBACK TRANSACTION;
END
------------------------------------------------------------------------