SQL Server Compact - Schema Management

Question

I've been searching for some time for a good solution to implement the idea of managing schema on an SQL Server Compact 3.5 database.

I know of several ways of managing schema on SQL Server Express, SQL Server Standard, SQL Server Enterprise, but the Compact Edition doesn't support the necessary tools required to use the same methodology.

Any suggestions/tips?

---

I should expand this to say that it is for 100+ clients with wrapperware software. As the system changes, I need to publish update scripts alongside the new binaries to the client. I was looking for a decent method by which to publish this without having to just hand the client a script file and say "Run this in SSMSE". Most clients are not capable of doing such a beast.

A buddy of mine disclosed a partial script on how to handle the SQL Server piece of my task, but never worked on Compact Edition. It looks like I'll be on my own for this.

What I think that I've decided to do, and it's going to need a "geek week" to accomplish, is to write some sort of a tool much like how WiX and NAnt works, so that I can just write an overzealous XML document to handle the work.

If I think that it is worthwhile, I'll publish it on CodePlex and/or The Code Project because I've used both sites a bit to gain better understanding of concepts for jobs I've done in the past, and I think it is probably worthwhile to give back a little.

---

Edit on 5/3/2010:

If someone is willing to "name" the project, I'll upload the dirty/nasty version that I've written for MS SQL to CodePlex so that maybe we can start hacking out a version of SQL Compact. Although, I think with the next revision of the initial application that I was planning, I'm going to be abandoning SQL Compact and just use XML Files for storage, as the software is being converted from an Installable package to being a Silverlight application. Silverlight just gives a better access strategy.

Answer 1

I am currently looking into Migrator.Net. This allows you to write changes to your database, called migrations, directly in C#.
These migrations can contain everything from simple table additions/drops, column modifications, to complicated data update code.

When your application boots, it can verify what version the database is currently in and apply any migrations that are required to bring it up to date. All this is handled automatically. The code to run this update is as simple as:

Assembly asm = Assembly.Load("LocalModels.migration");
Migrator m = new Migrator("SqlServerCe", "Data Source=LocalModels.sdf", asm, false);
m.MigrateToLastVersion();

I am having a couple minor issues with the Compact support (it assumes the default schema is dbo). But I don't think it will be too difficult to fix them.