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

Hi Richard - some random thoughts (not sure I can fully answer though)

1. the Microsoft Sync Framework is one option. I haven't had a chance to fully appreciate what it can do once you've deployed it after the initial first time (which seems to work fine). There's a MSDN site for it here

2. You can execute scripts on a mobile device, but not through something like SQL Management Studio, so in theory you could manage/maintain T-SQL scripts but the down side is that the T-SQL would be convoluted (to CE's supported statements) and I don't know a way to "automate" execution - but the Sync Framework might hold some answers..

Answer 2

If one of your key criteria is going to be working efficiently over a small pipe, the only real choice you have is to store a DB Schema Version (maybe somehow tied to the scripts checked into your CMS) and when an update is needed, the change scripts are sent over the wire and applied in order. You would probably want to keep a log in your DB as well of these scripts being applied so you can gracefully handle disconnects, reboots and other potentially nasty problems.

Answer 3

Is SQL Server Management Studio any use for you?
http://technet.microsoft.com/en-us/library/ms172933.aspx

Answer 4

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.