Handling change in a database schema

Question

I am in the fairly initial stages of building an app which will hopefully be sold in the 1000's - more likely it'll be hundreds, but either way more than enough to cause a good sized headache for database schema changes.

I'm rather restricted to a windows / .net / sql server environment as a number of our customers will be self hosting the app I'm building in their web servers and extra bits installed on their servers never goes well and there's always a number of complaints / lost business as a result. So sqlalchemy is out, as it runs in python.

What I'm after is a tool that will allow me to easily migrate between versions of the application. I’ve found migrator.net and I’ve used it before, but never in production as the development team appears to only work on it annually, not exactly an active project.

What alternatives approaches are available?

UPDATE I forgot to mention that I want to be able to use this in a continuous integration environment, so whilst data dude and sql compare are nice gui tools, I really automation.

Answer 1

Add the upgrade script to your application. If you don't have that anyway, create a settings table where you store the current schema version of the database. Example:

CREATE TABLE SETTINGS(KEY VARCHAR(255), VALUE VARCHAR(255));
INSERT INTO SETTINGS VALUES('version', '1');

When starting the application, query this table (if it doesn't exist then no previous version was installed), and check the version. I would only maintain separate upgrade scripts for version 1 to version 2; version 2 to 3; 3 to 4 and so on. I wouldn't maintain upgrade script for version 1 to 3 or so directly, usually it's not required. The upgrade scripts typically contain ALTER TABLE and CREATE TABLE statements, but sometimes also UPDATE.

Answer 2

If you use Visual Studio 2010 Ultimate/Premium Edition or Visual Studio Team/Database Edition 2008 you will find all you need in the Visual Studio.

You can compare two databases (for example, dev and prod) and generate SQL Script for modifying the data. You can easy exclude some tables or some columns from the comparing. You can also examine the results and exclude some entries from generation of the script. So one can easy and flexible generate scripts which can de used for deployment of the changes in the database. You can separetely compare the data of two databases from the structure (schema comparing). Just go in "Data" menu and choose "Schema Compare" or "Data Compare". You can read http://msdn.microsoft.com/en-us/library/dd193250.aspx and http://msdn.microsoft.com/en-us/library/dd193261.aspx to understand how it works. So you can refresh data in dev with the data from prod or generate scripts which modify prod database to the last version of the dev database.

I started to use this feature with Vinsual Studio Team Edition 2008 and continue to use it Visual Studio 2010 Ultimate. It is exactly what I need. In some cases I need modify a little the scripts, but 95% of the job do Visual Studio.

If you can not VS2010 Ultimate or Premium and no VS2008 Team or Database Editions I would you recommend to look at some products of http://www.red-gate.com/ (like http://www.red-gate.com/products/SQL_Compare/index.htm) with the close features.

Answer 3

Red_Gate's SQl Compare is good for keeping two databases in synch.

However, I think the ciritical thing is to keep your changes in Source Control under a specific version. Treat your sql code just like any other code that belongs to a version. Never allow your devs to make changes using the GUI, All changes should be scripted and put into source control as part ofthe version. Once you do that if someone is in version 1 and you want to upgrade them to version2.3, then you just need to run the sripts for each version in order and you will have a datbase in version 2.3.

Answer 4

The pro version of SQL Compare has a command line that can be used for continuous integration. This is documented here: http://downloads.red-gate.com/HelpPDF/ContinuousIntegrationForDatabasesUsingRedGateSQLTools.pdf

Ideally you would maintain your schema in source control to get the most out of this, which you can do using SQL Source Control.

SQL Compare can migrate between two given databases, but source control provides a more mature development process.

If this doesn't achieve what you're looking for, please comment on this 'answer'.