SQL Compact Edition files are not portable

Earlier this year my company was preparing to ship a new product. Part of the software used some new data files that would also be installed on the user's system or on the user’s network and shared among multiple users. It was convenient to put this data into a relational database so we used Microsoft SQL Compact Edition.

Our plan was to build the databases, install the files on the user system, open readonly at runtime and query the data. Everyone involved agreed SQLCE would be a fine solution. And it would have been except for one surprising problem.

If the database is built on XP or Vista/Windows 7 it simply cannot be opened readonly on the other operating system. The first time it is opened SQLCE needs to re-index the database because the National Language Support sort order is different on these two operating systems. This was a problem for us for three reasons.

1. We install this data file (and many others) readonly. There is no need for a user to change the files.
2. We install the data files with an elevated account. The user currently running the software may not be able to elevate to administrator.
3. The files could be shared over a network and accessed by different client operating systems. The database would constantly be going back and forth between XP or Windows 7 NLS sort order.

Here is a Microsoft Connectentry that mentions the problem with Windows Server and Windows Mobile. The same problem exists for XP and Vista/Windows 7.

Without any known workaround for this problem I have to conclude that SQLCE databases are just not portable. Yes, they can be converted back-and-forth. Yes, that conversion will happen automatically (whether you want it to or not). But no, the data is just not usable on both XP and Vista/Windows 7 without first being converted – and then it is no longer usable on the other OS!

For us it was a show stopper. Our solution was to use Sqlite instead. Fortunately the code change was minimal because the database access layer was properly hidden behind a higher level API. Only one module had to be changed and both our software and internal tools were back up and running (hooray for good architecture!). Sqlite databases are truly portable, not just with different versions of Microsoft Windows but also with Linux and MacOSX.