SQL CE vs VistaDB
Microsoft SQL Server Compact Edition or SQL CE is a small footprint client-only edition of SQL Server. SQL CE is Microsoft’s database solution for mobile and desktop development.
SQL CE is not a managed database, but has managed wrappers for use from managed code. SQL CE features an unmanaged architecture and was not designed specifically for the Microsoft .NET Framework. In fact it was designed for mobile devices and then ported to desktop machines later out of recognition that this was a needed product space not filled by Microsoft.
VistaDB is an excellent alternative to SQL CE (SQL Compact Edition)
VistaDB provides managed programmers a great alternative to SQL CE (Compact Edition). VistaDB has none of the limitations of SQL CE, and runs on more platforms (Mono supports Linux and Mac OS X). CLR Procs, CLR Triggers, Full Text Search, Views, Image (BLOB data) and other missing features in SQL CE are present in VistaDB. If you need an embedded database with XCopy deployment for a managed environment, look no further than VistaDB.
We have heard over and over from users looking for SQL CE embedding tutorials that the process is too complex and just not intuitive. Our process is very simple; add a reference to our assembly and build.
To deploy VistaDB copy ONE DLL for both 32 and 64 bit machines with your databases. You can even embed our DLL within your app using ILMerge and have a zero copy deployment.
SQL CE requires you deploy different editions based upon the platform, and they cannot both be referenced in your application. You are required to target your application to x86 or x64 when binding to SQL CE. Do you want to support and test two versions of your product with two copies of the SQL CE engine? VistaDB only has 1 Assembly for both 32 and 64 bit.
Embedding VistaDB with your application is easiest option available for .Net developers
End user can change SQLCE runtime on your app?
Do you want the engine your application is using to be automatically promoted without your app knowing about it? SQLCE will automatically promote all applications using it when a service pack is applied. Take a look at this release note about SQL CE 3.5 SP2 Beta 2.
The existing installations of SQL Server Compact 3.5 or SQL Server Compact 3.5 SP1 on the computer are upgraded to the released version of SQL Server Compact 3.5 SP2 Beta 2 by installing SQL Server Compact 3.5 SP2 Beta 2 using the MSI file.
That means if an end user installs that beta build, your app would be automatically using it, and there are breaking changes in that service pack.
Even using SQL CE Private Deployment can still lead to problems with bindings and conflicts with the unmanaged code. See the SQL CE private folder deployment errors blog post for the workaround (a binding redirect).
Even when making a private deployment of SQL CE the unmanaged dlls have to be registered on the machine to prevent load errors due to locally installed versions.
VistaDB can truly be embedded with your app through ILMerge, or lives in the same directory as your application. You never have to worry about the user changing out the assembly on you post release with a different version.
32 and 64 bit deployment hassles with SQL CE
SQLCE was originally built for mobile devices, but Microsoft opened it up in 3.1 to include desktop support. But the engine is still unmanaged code and requires complex deployment of 32 and 64 bit engines. Look at this note buried in the release notes on deployment.
Due to changes in SQL Server Compact SP2 and additional 64-bit version support, centrally installed and mixed mode environments of 32-bit version of SQL Server Compact 3.5 or 3.5 SP1 and 64-bit version of SQL Server Compact 3.5 SP2 can create what appear to be intermittent problems. To minimize the potential for conflicts, and to enable platform neutral deployment of managed client applications, centrally installing the 64-bit version of SQL Server Compact 3.5 SP2 using the Windows Installer (MSI) file also requires installing the 32-bit version of SQL Server Compact 3.5 SP2 MSI file. For applications that only require native 64-bit, private deployment of the 64-bit version of SQL Server Compact 3.5 SP2 can be utilized.
Yes, in order for you to “safely” deploy the 64 bit version you have to install the 32 bit version also to avoid “intermittent problems”. Those two MSI files are over 6 MB in size, and your installer will have to be 64 bit aware to determine what install order to use.
VistaDB has a single assembly you deploy with your app, external users cannot upgrade the version without you knowing about it and there are no issues with mixed mode deployment. Our assembly will run 32 bit if your application is 32 bit, and 64 bit if you are 64 bit. Easy as it gets.
Do you need to deploy a database management tool for your users? SQL CE does not have an xcopy deployable DBA tool for administrating the database. VistaDB ships the GUI DBA tool Data Builder and an unbranded version with full source code (DBA Sample Tool) is available. This allows you to build custom admin tools for your own applications quickly and easily.
SQL CE does integrate with the SQL Management Studio, but which version you use depends upon the runtime engine. Management Studio 2008 is required to view and edit SQL CE 3.5 files. But the full install of SQL Management Studio is far from being lightweight, and is definitely not xcopy deployable. The VistaDB DBA Sample Tool is xcopy deployable and can be customized to only expose the functionality you desire.