How to deploy complex SQL solutions through an installer?

Question

Part of the setup routine for the product I'm working on installs a database update utility. The utility checks the current version of the users database and (if necessary) executes a series of SQL statements that upgrade the database to the current version.

Two key features of this routine:

• Once initiated, it runs without user interaction
• SQL operations preserve the integrity of the users data

The goal is to keep the setup/database routine as simple as possible for the end user (the target audience is non-technical). However, I find that in some cases, these two features are at odds. For example, I want to add a unique index to one of my tables - yet it's possible that existing data already breaks this rule. I could:

• Silently choose what's "right" for the user and discard (or archive) data; or
• Ask the user to understand what a unique index is and get them to choose what data goes where

Neither option sounds appealing to me. I could compromise and not create a unique index at all, but that would suck. I wonder what others do in this situation?

Answer 1

Check out SQL Packager from Red-Gate. I have not personally used it, but these guys make good tools overall and this seems to do what you're looking for. It let's you modify the script to customize the install: http://www.red-gate.com/products/SQL_Packager/index.htm

Answer 2

You never throw a users data out. One possible option is to try and create the unique index. If the index creation fails, let them know it failed, tell them what they need to research, and provide them a script they can run if they find they have a data error that they choose to fix up.