Fog Creek Software
Discussion Board

DB schema updates in daily builds

Every day I need to release a website with an MSSQL DB in the backend to a test site. The problem comes when I need to move the new DB schema to another server. The problem is that client checks the website every day and enters a lot of test data. He do not want to loose the test data every day. So I need a solution which automatically transfers the updated schema but leaves the data intact.

Of course it's not always possible (eg. a new mandatory field in the DB). In that case the best would be to receive a message to enter default values or autoclean those data which cannot be updated.

Do you guys know any solutions to similar problems?

Wednesday, March 19, 2003

I solved this problem a couple of weeks ago.

Basically I built a 4 stage process:
1 extract the schema from an MSSQL 2k Db and write to text file (rawschema.sql) - use 'Generate SQL' script.

2 replace specific elements of the rawschema.sql file e.g database name with tokenised string ($DatabaseName)

3 extract data (from the same db) and create a dataload.sql script. I built a tool that does this using SQL-DMO. You just logon and it does the rest

4. (The main tool) allows you to create a site specific installation script (myinstall.sql) by replacing the tokenised strings in rawschema.sql with values from a template file, which you can change on the fly.

Take myinstall.sql and run it on target database, followed by dataload.sql

Its not perfect; its not a process for end users, but it does turn a repetitive hours long task into something that takes minutes, with the added bonus that you can easily create release schema, or build demo systems quickly.

I did consider making it more automated; a single stage process, but time is money. I also considered using DTS, but discarded the idea as inappropriate.

For further details, including VB code, mail me.

Wednesday, March 19, 2003

We maintain a schema SQL and an "update.sql" which contains only updates to the current schema. On new sites we run the schema SQL, and for current testing sites we run only the update sql (if one exists). This works on a daily build scenario. (I'd have to commit hara-kiri if I had to delete all my test data for each build)

For mandatory fields here's the thing - there usually is a default value (or some value you can use for a default for existing records). Create the new field but don't have it mandatory. Then populate it with the new default value, then alter the table to make it non-null.

Some databases allow you to create the new field with a "Default" value and NOT NULL, which solves the problem, but I'm talking about generic cases, and cases where you want current records populated with a certain value, but not for NEW records created after the build.

Also some databases have a problem with altering tables to make NULLABLE fields Non-Null - you must then create a temporary table with the same structure, copy data to the temp table, drop and recreate the old table with the non-null fields, and then copy back from the temp table. Remember to drop the temp table also, and to recreate any indexes and keys.

Deepak Shenoy
Wednesday, March 19, 2003

I don't think it covers exactly what you're talking about, but this article by Martin Fowler should be of interest to you:

Herbert Sitz
Wednesday, March 19, 2003

Thanks Herbert.

I knew about Martin Fowler already having read some of the things on the site and having owned (and sadly left on a colleague's desk at a company far, far away) a copy of the refactoring book.

This was a new article for me though. I wish I had seen it about 6 weeks ago. It is a reasonable facsimile of the strategy we have started implementing at my current job.

I'll post back here or to another thread in about 3 or 4 months to let people know how we got on; what parts worked well and how we implemented things. In marketing speak, what were the 'key learnings'.

Thursday, March 20, 2003

Try the program called ERWin (now called something else, from CA Software).

We use it all day for updating databases without losing the data.

Thursday, March 20, 2003

*  Recent Topics

*  Fog Creek Home