How to include database changes during application publish

deploymentnetsql server

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?

Best Answer

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.

Related Topic