Using msi to create and maintain SQL databases

Jan 14, 2011 at 12:23 PM

As a SQL database administrator, I am currently experimenting with the possibility to use Wix for database creation and maintenance.

As I am pretty new to creating msi's in general and Wix in specific, any help would be highly appreciated. I am specifically interested in how to deal with the following aspects.

  • New release and upgrades. I typically handle database versions through extended properties on database level. Using this, I guess I should be able to have an msi detect what version a database is, and based upon that version, execute one or multiple scripts to upgrade the database to the last version.
  • Using a single msi to create multiple databases. Is it possible to use an msi to create e.g. a second database instead of having it jump into maintenance mode?
  • Creating a combo containing the databases available on the selected server instance.

Any help on any of the above topics is highly appreciated.

Jan 14, 2011 at 12:55 PM

I had shared some thoughts on deploying databases here.

Our most recent projects that deploy databases keep a schema version table in the database. Every time someone needs to change the schema, they write a new sql_upgrade_<new_version>.sql file. We wrote a simple stored procedure that looks at which version the database is, takes a parameter for the latest upgrade script number available and spits out a .sql script that lists which SQL upgrade scripts to execute via a :r SQL inclusion command. Then the installer executes the generated script. We do this since version 0, so a clean database setup is running upgrade 0 through latest. This way the MSI doesn't really do anything that you can't do without an MSI. Unfortunately I don't have a full working installer to share, but that's something we'd like to contribute back to msiext soon - or maybe someone can (re)develop it based on a demo (it's really not that complicated).

We have one installer that creates two databases. This stuff is easy since all the ODBC extension objects can be nested under Component elements and follow component rules. Obviously the UI workflow needs to be adjusted and you need to save properties such as DATABASE_SERVER into FOO_DATABASE_SERVER and BAR_DATABASE_SERVER during the UI sequence.

The current functionality has support for SQLDMO, which lists instances available around you. But it sounds like you want to enter a server, then list instances. This means doing some discovery of databases via SQL Browser service (if that's on). That would be nice. You'll have to write it.


Jan 14, 2011 at 2:35 PM

Thanks for sharing your ideas.

I am not familiar with the :r SQL inclusion command (yet). Your approach seems indeed rather straightforward. I was personally thinking along a different path, but haven't been able to test my findings yet. The idea I had was to work through patches using installer's default methodology to manage versions. I'll try to come up with a simple example in the next days and share it with you. Would certainly be interested to collaborate on some demos.

My idea on multiple databases is a little different. I understand you can create as many component elements as necessary (even if they all contain the same scripts). What I would like to realize is different. A user should be able to use the installer for either creating a new database or upgrading an existing database. Upon a first install (NOT Installed), the user would only be able to create a new database. Next time however, the UI would ask what the user would like to do (create new or change existing). So, I basically do not know upfront how many databases to create (and therefore cannot have multiple components doing the same thing). I have read some stuff about instance transforms, but am not sure this is the right way to go. I'll have to check further on this.

No, that was not my question. I just want to list DATABASES on the selected server (not instances on a server). It is properly due to my (still) limited knowledge of Wix that I do not know how to include these in a combobox. Getting the results from the server would be as easy as a 'select * from sys.databases'.

Jan 14, 2011 at 6:54 PM

Feel free to send patches to the demos that make the examples more interesting or complete.

I think doing MSI patching is a total overkill. We found the best thing to do was to stick to major upgrade and not rely on MSI information for databases. So MSI is just a vehicle.

I don't see "many databases" in your example. I recommend 1 msi = 1 database. The UI can easily figure out of the database exists (or just rely on previous version of MSI installed).

You can execute SELECT * FROM sysdatabases easily, but populating an MSI dropdown requires actual code. You'll have to write a custom action for that (and you can model it on the DMO ones in MsiExt). It would be a great contribution to MSIExt btw. Note that SQL 2000 and 2005 have different schemas (sys.databases vs. sysdatabases I think).