Update SQL Server 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

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 2

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