Update SQL Sever Database Schema with software update

Question

How do you update your SQL sever database when installing your product's update? Are there any tools that will integrate with windows installer? My typical schema changes are:

• Adding/removing columns
• Adding/removing tables.
• Adding views.
• Adding/alter indexs.

Answer 1

Not sure about integration with the windows installer, but you might look into Red Gate's SQL Packager

Answer 2

InstallShield lets you execute SQL scripts as part of an installation. Not tried it though, just remember it was on the GUI last time I looked!

Answer 3

You might want to look into SubSonic's migrations. First, it's a great way to version your DB. Second, it shouldn't be too hard to figure out how to run the exact same scripts from an installer.

Answer 4

In my experience it is better to do db schema updates when your software connects to the database, rather than at install time. You want to do the following things:

• Identify each schema change with a unique identifier, such as a guid
• Include a list of all the changes you can apply with your product, for example compiled into a resource during your build
• Have a table in the database to hold a list of schema changes that have been applied
• when you connect to your database, scan that table to see if any changes are needed

This is all straightforward enough to do from within your running code, but not so easy to do in your installer.

Answer 5

I think you have for each version of your software a bunch of database updates. Why don't you write these updates as a T-SQL instruction, to be tested-executed when the new version of your software is first launched? Just open the connexion to your database from your software and send the DDL instructions as you would send any SELECT or UPDATE instruction. I would also do something similar to what proposes Jack Paulsen: maintain a list of these T-SQL instructions with a double identification system: one linked to the database/software version it applies to (can be uniqueIdentifier), another one (number) to keep the instructions in a serial order (see my example: instruction 2 cannot be executed before instruction 1)

Example:

//instruction 1, batch instructions for version#2.162
USE myDatabase
GO
ALTER TABLE myTable
    ADD myColumn uniqueIdentifier Null
GO
//instruction 2, batch instructions for version#2.162
USE myDatabase
ALTER TABLE myTable
    ADD CONSTRAINT myTable_myColumn FOREIGN KEY (myColumn) ...
GO

For a complete description of ALTER, DROP and CREATE instructions, see your T-SQL help. Just be carefull enough to (for example) delete Indexes and Constraints linked to a field before deleting that field.

You can of course add some extra UPDATE instructions to calculate values for added columns, etc.

You can even think of something more complicated, checking if previous upgrading steps (that led to database version #2.161) were correctly executed.

My advice: as you write these T-SQL instructions, keep also trace of their "counterparts", so that you can at any time (debugging time for example) downgrade your database structure to previous version.

Answer 6

Adam Cogan recommends creating a patch table that is used to record each and every update beyond your initial release. Instead of changing your schema through SSMS or Enterprise Manager make sure you script each change...both applications allow you to script your changes and then not apply them. Save the scripts to files (probably add them as resources) and then simply check the patches table each time you application runs.

Adam has some rules to better SQL databases here

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx