How to include database changes during application publish

Question

I am maintaining a WinForms application, which talks to a SQL Server database. Sometimes I have to change database schema (for example to alter a sql procedure or add new one). For this purpose I have a SchemaChange.sql file, where I put the corresponding sql code.

When I create an installer for my project, the msi package is created. It contains my application, referenced assemblies, COM+ assemblies,... Alongside the msi package I provide an SchemaChange.sql file, which has to be run on the production sql server.

But sometimes I forget to add something to SchemaChange.sql file during development or I forget to execute it on production server after upgrading my application on client machines. Any advice how to automate this to avoid further problems?

Answer 1

I can tell you what's worked for me. There may be reasons why it does not work for you.

First, I wrote a schema diff tool. It was based on a physical database design reporting tool I also wrote which examines a database and prints a report of all the tables, columns, relationships, indexes, constraints, etc. The diff tool simply runs this on two database instances and reports any differences. This diff tool allows me to detect differences between things that should be the same.

The next important piece is hygiene. Changes are never made ad-hoc to the production database. They are made with an SQL file which performs the "update" once we know it is safe to apply the update. We know that because we already ran it on the pre-production instance of the database and did some testing.

How many databases you hav apart from production and pre-production is really an issue of how your project is organised, and isn't so relevant apart from to maintain the discipline of not making ad-hoc changes (apart from adding users or storage devices, for example).

The next important piece of the system is the development database. By this I mean the database used to test code during development immediately prior to release. This is typically shared by the developers (who may also have their own database for making private changes and experiments).

During development. every time a database change is made, update the build-from-scratch SQL and also make a stand-alone SQL file which applies just this change.

To make a release I would do this:

1. Create a blank database (I will call this PREV)
2. Apply the database setup script for the previous release
3. Load test data into it (perhaps data sampled from production). This is important for correctly testing things like constraints and column types.
4. Apply the SQL scripts for performing the upgrade (i.e. the stand-alone files I mentioned above)
5. Do any release testing you need on this, but in parallel:
6. Create a second blank database (I will call this NEXT)
7. Run the install-from-scratch SQL to build he database in NEXT
8. Run the comparison tool to compare PREV and NEXT. Apart from the fact that NEXT has no data, they should be identical.
9. If all is OK, export the data from PREV because you will need it next time (as step 3).

Following this proces means you always have two things handy: 1. A create-from-scratch script which can be used to build new database instances for new uses (e.g. for support reasons, for clients, etc.) 2. Upgrade scripts which are guaranteed to work to bring the production system to an exact match with the development and pre-production systems (to avoid unexpected differences in the databases).

To summarise using the terminology from your question, the key thing is to make sure that even the development database is only changed by running SchemaChange.sql scripts, and also to ensure that you have a sufficiently robust testing regime that your tests will tell you when this didn't happen for any reason.

Answer 2

Simple, never make changes to a database in any way except through a source controlled script. If you make changes using the GUI you are likely to forget them. If you write a script, it is code, that you save in your source control just like any other code. So basically, stop treating database changes as if they aren't code.

Answer 3

Go give LiquidBase a try. I've also gone the route of manually creating sequentially numbered SQL scripts, but now having used a database change management system like LiquidBase makes me never want to go back.

Answer 4

For my environment, I built a dedicated tool for managing database updates. I created several database tables to track schema (table, field, index, indexField, history) and a screen to manage those tables. I also track meta-data such as when changes were made, the last time a schema change was applied, etc.

Our developers now use this program whenever they want to modify our database. First they make any required changes to the tables. Then they open up an apply schema screen which shows a list of changed tables and they can select which ones to apply to the database. We can then create the update script and apply the update script.

When we're building a production release, the developers can then go in and flag which table updates they want to be included in the release. Our release process will then create & apply the schema changes for those tables.

Answer 5

I like using Migrator.NET (and fluent migrator.net) for my projects. All db schema changes are part of the solution, complete with rollback logic. Each change or logic change is implemented as a 'migration' class, then it's simply a matter of upgrading to the latest version or rolling back to a specific version.

Migrator.NET stores the current migration version in your sql server database, so knows which set of migrations to apply to any database to bring it up to date.

Answer 6

The way we do it is

1. The database schema is all stored in source code repository.
2. When a build is done a database is built from the scripts and a backup is made of the empty schema and included in the installation MSI.
3. When the MSI is run by the client we use the RedGate Compare API to compare the packaged backup to the clients database. It then dynamically creates an upgrade script and run on the client.

There are a couple more steps, like backing up the client database before running the scripts, but this is the gist of it.

The most important thing is version control the scripts and automating the upgrade process. Then not much can go wrong.