In my company we use principles of continuous integration in our C#.Net+MS SQL development. We use Subversion for source control management and CruiseControl.Net as integration server. The problem came when we needed to choose a tool for continuous integration of database changes since we did not find any open-source alternative.
The common repository for all source code changes is Subversion (currently we think of migrating to Git, anyway). For tracking changes of database schema changes we use Subversion, too.
In our team each developer has his own database server installed on his PC and he is an administrator of his own database server. The core of everything is the SCM. Developers check out latest code from there, integration server uses it as well to get code base and integration tests. The idea connected with tracking changes of database schema was to include changes under version control. Then both developers and integration server were able to retrieve database schema changes and to handle them.
Database Change Scripts
If a developer wishes to do some changes in database schema, he can freely do that on his local database without fear to influence work of the others. As the result of his work he is expected to create a database change script that includes all schema changes needed to be implemented. Let’s say we need to add a column to the table. The change script will contain these SQL commands:
ALTER TABLE ORDER ADD STATUS INT NOT NULL DEFAULT 0 GO
The script will add a column “Status” to a table “Order”. You can feel it would be very annoying for a developer to change database schema only via change scripts in regular daily work. We established a helpful practice how to make things easier for the developer. The change script should be the result of his work that he wish to be integrated. We work as follows:
- When developer is going to do schema changes (on his local machine, of course), first he creates a clone of database version that was already fully integrated to source control. Let’s say that database is in version 1.5 so he creates a clone of it with help of DbTracer:
cmd> dbt create /database=reference_db_v_1_5 /db-version=1.5 /connection=(local)
- At this point he has a reference database that will serve him as a reference point when he will compare it with his test database. The test database is created in the same way as reference one:
cmd> dbt create /database=test /connection=(local) /db-version=1.5 /override-if-exists
- Now the developer is ready to do whatever he wants in the “test” database. For example he can add a column “Status” to a table “Order”. Of course you are not limited to add only one schema change to one change script.
- When he is ready with schema changes he compares the test database with a reference database “reference_db_v_1_5″. Tools for comparing databases are very much helpful here. In my team we use EMS DB Comparer.
- The tool generates a change script. It is possible to modify it manually if we are not satisfied with the result. After that, the change script is ready to be checked in to version control.
Change Script Names
We use the following convention for our change scripts:
<major_version>.<minor_version>.<revision>-<some description of change script>.UP.sql
For example:
003.012.0541-add column Status to table Order.UP.sql
In this example the change script lies within database major version=3, minor version=12 and its revision=541. There is also a short description “add column Status to table Order” defining what is inside the change script. Additionally, the keyword “UP” in the change script is reserved for future when we will need to distinguish between upgrade change scripts and downgrade change scripts.
Now the change script is ready to go to version control.
Our Solution – DbTracer
We searched for a simple tool that would maintain a our database migration scripts. Additionally, it should provide following features:
- command line interface
- storing SQL database version in the database itself
Unfortunately, no such tool existed that would have been acceptable. Hence, we inspired by Alexander Kleshchevnikov and wrote our own tool DbTracer. Currently it is in experimental phase for public. We use it internally in our team but still API changes are going on.
Further Reading
Related posts:
