Selfelected

Database versioning

Since I have abandoned Visual studio database edition because of it not being able to handle tables with data I have returned to my old, proven, way; checking in/committing the update scripts and storing a database version number in a Setting table.

In Microsoft SQL Server Management Studio (or whatever it was called earlier) there is a setting to always create update scripts whenever a change is saved.

Then whenever a change is done, store the update scripts with consecutive version numbers.  These versions are not the same as the release versions but one version number per commit.

013_01_S_Customer_Added the CompanyColour field.sql
013_02_D_Customer_Set the CompanyColour to red except blue for McCarthysen.sql
013_03_S_Customer_Set CompanyColour to notnull and no default value.sql
013_99_D_Setting_Updated database version.sql

013 is the version number.  Next commit is 014 etc.
01, 02, 03 are consecutive numbers.
99 is the final number for this commit.  It always contains an update in the Setting (or whatever you call it) table and sets the version number record to 13.
S and D are Schema and Data respectively.  I have learned that it is good to see this already in the script’s filename.
Customer and Setting are the names of the main manipulated tables.
The rest is free text that explains what the script does.

It is now very easy to see how far a database is in the development flow.  Select the the row from the Setting table and notice its number.  Then run all scripts with higher version numbers in the right order.  The scripts are easy to order by their names directly in the version manager or in the file explorer after a checkout/getlatest.

This technique works well with several developers.  Store the scripts locally until they you are ready to commit the code.  Update the version number if someone already used the version number.  Commit.

A tool like SQL compare does approximately the same job.  If you have only a few updates with a few databases it is way faster to just create a diff-script and run it.  Contrary to VSDatabase edition SQLcompare handles tables with data.  It costs money but 1) there is a trial version and 2) it is way cheaper than hacking the code yourself.
There are other tools as well and I have used one but I cannot remember the name of it.  It was not as easy to use as SQLcompare if I recall correctly.

If you have to handle several versions while developing the procedure I have described here is good since it is so easy to check the version of the database.

(I just stumbled upon a situation where saving a view and only updating the output fields from lower to upper case (customername -> CustomerName) did not result in a script file.  In this case there was no problem in scripting a drop/create script through the Object explorer tool box though.)

>Visual studio net database edition does not handle data

>

The title is not totally correct; it does handle data. But it cannot do version management on your database if you have data in it properly.

Like this:
A year into the project you/the customer realize that a Customer can be split into InternalCustomer and ExternalCustomer and a decision is made to make Customer the base class and the other two children.  An accordingly correct decision is made to create two new tables in the database and have them have their corresponding fields.

Technically, or database script wise, this means
1) create two new tables
2) select data from the Customer table into the two child tables <- oops
3) drop some fields in the Customer table

Visual studio database edition can handle 1) and 3) but there is no way to tell it to run a custom script in between.

This unfortunately makes the product useless for me.

>Slow sqlserver express

>

I got informed of something somewhat strange the other day.  Microsoft SQLServer Express runs (connects) faster if you have a query window to the database already.

Like this:

Have a freshly booted machine with only Visual studio running and your web application of choice.  The time for a refresh of you web page might take 5 seconds.

Now start Management Studio and open a query window.  Let it stay open.
Refresh your page and you are down to a second.

The figures might of course change but they are what I have on a dual core 2GHz 4MB machine for a project of size X and database of size Y.

—-

I haven’t checked if the same thing happens with another script editor like for instance AnySQLMaestro but guess it does.  I guess it has to do with connections – as long as there is a connection to SQLExpress, it doesn’t close.  If so, it corresponds with the product’s raison d’être, a database for applications.

—-

I have noticed a correspondence between debug-start-delay and quality of code.  I say that every second wasted waiting on debug start makes the developer unwilling to test more thoroughly and leads to more bugs in the product.

—-

Another trick to make web debug start faster here.

>Managing versions of a database

>There are in SQLServer2005 ways to version control it through tools by Microsoft. Since I havn’t had the time to learn how to and some of us use other databases there is a common technique that works so so but at least gives us a chance of reverting to an older version.

Any time I decide that this is version x.y. I check in the database as binary. Image or backup doesn’t matter as long as it is recreateable without anything else.
After that all updates must be scripted. These scripts are checked in in the same folder as the database.
One can then get the database and use the date stamps on the scripts to get the exact version of the database.

After a while there are too many scripts so we check in the database again.

One then sorts the files on date and starts by getting the database. Then run the scripts to the proper version.

This is not a perfect way; it is easy to forget to script a change. But it is a start at least.