So I am in the middle of building a pretty substantial Windows Forms application in .Net 2.0/C#, which will rely very heavily on database integration. Currently development is proceeding using Sql Server Express 2005 for the database server. It is a very robust product that supports anything that you can do on the full edition of SQL Server, and is freely distributable. Stored procedures, triggers, the works.
However, when making the first internal release intended to be installed by other people, I ran into some problems, specifically related to the way that SQL Express works. As it is really the same thing as the full version of SQL Server (with limits put on DB size and other licensing issues), it requires a big download (around 35mb), large db file footprint (around 20mb) separate installation, and runs as a service on the client machine. Though the install can be packaged as part of your overall distribution and set to install “silently”, it is still an issue that requires ongoing support and trouble-shooting. Additionally, you can sometimes run into problems with the SQL Express service, permissions, problems attaching the database to the server, etc. It is a robust solution, but the potential problems involved in installing SQL Express on thousands of client machines and getting it to run flawlessly all the time is enough to give me pause.
One alternative is the newly release SQL Server Compact Edition (3.1). It is a repackaged version of the old SQL Server Mobile (and renamed version of the pre-release SQL Server Everywhere). It has a very small DB footprint (less than a MB), and (the key feature) it does not require a separately-installed DB Server. Instead, you distribute the SQLce runtime dlls with your application and they are launched into a separate process as needed. No worrying about large DB Server installs, security issues, separate services, etc.
Of course, SQLce does have its downsides. For all that you gain, you sacrifice view, stored procedures, foreign keys, triggers, transactions, etc. In other words, you have a nice, easy-to-use, portable and easily distributable DB engine for use in .Net applications, that gives you tables as your one and only database feature. Though this is workable and it may be possible to transition from SQL Express to SQLce, a lot of functionality and maintainability is lost in the process.
In search of a (non-Microsoft) alternative for an embedded DB candidate for .Net applications, I just came across VistaDB 3.0. This seems to have everything that I am looking for. Basically, all of the database processing features that SQL Express has to offer (other than stored procedures), including views, foreign keys, constaints, ADO.net data provider, integration with Visual Studio and much more. It claims to be T-SQL compliant, supporting .Net data types and Unicode and offers its own database management program and a utility for migrating over your existing Sql Server databases. And it is an embedded databse with a small footprint, requiring no installation on the client end and running in its own process at runtime.
The only downside that I can see is that I have never heard of them before. It looks like this product has been around for at least a couple of years, and the new 3.0 release is a big one for them. I have not seen any real reviews of it (other than a preview on Larkware, and lots of blogs that copied their PR material in response to an offer of a free license for publicity). Has anyone used this product in a .Net 2.0 Windows Forms application that is being widely distributed? Any problems? Recommendations?