SQL/e - Microsoft to release an Embedded Database version of SQL Server

Last year I wrote about how we need an Embedded Database for .NET applications. Microsoft's current .NET database lineup runs as data servers, which is completely inappropriate when you need a simple data format for a desktop application . My post was based on the problems I faced while working on an RSS aggregator built for speed and scalability - I spent all my time troubleshooting problems with open source embedded database engines (SQLite, SharpHSQL, Firebird), and eventually just gave up on the project for the time being.

Stefano writes about what could very well be the solution to this problem - SQL/e (Microsoft SQL Server Everywhere). There's more information on Steve Lasker's FAQ post, but Stefano does a great job of summarizing the FAQ:

  1. SQL/e is targeted specifically for general desktop usage.  It runs in-proc, doesn' t offer data as a service, has a lightweight model for applications that need to share the resources of the users machine with other applications besides the database engine. The Server Versions (like SQL Express) runs as a service instead.
  2. The SQL/e runtime (7 DLLs are about 1.4MB in size.
  3. SQL/e has the same database size limitation as SQL Express, 4GB.
  4. You cannot use SQL/e as a web server database. SQL/e is targeted at the desktop database. The plan is for SQL/e to throw a not-supported exception when the hosting process is IIS.
  5. SQL/e will use the same set of classes that SQLCE and SQL Mobile have used (System.Data.SqlServerCe.*)
  6. SQL/e will support synchronization with a Server. Developers can use Merge Replication or Remote Data Access (RDA), a lightweight sync technology available to the SQL Mobile platform that will equally apply to the SQL/e product.  In addition to these sync technologies, Microsoft is working on a new set of sync components based on the ADO.net programming model (maybe shipped with Orcas).
  7. SQL/e doesn' t allow any code to be placed in the database. It's a pure data format and it doesn't have any code (no sprocs, views, triggers, extended sprocs, macros or ability to run XP_CmdShell).
  8. SQL/e files can be emailed and their extensions can be changed to launch your application. The SqlCeConnection object can handle any extension you wish.
  9. SQL/e supports multiple connections. You can now have several connections in your UI layer and another connection for background synchronization (the connection limit is 256 connections).
  10. SQL/e doesn't have the XML data type. SQL/e will place XML in an nText datatype when data is synchronized between SQL Server and SQL/e (so, you still have the XML storage, but SQL/e will not have X Path query support in its engine).
  11. SQL/e doesn't support CLR user defined data types.
  12. SQL/e data file can be shared between device and desktops, simply copy their .sdf file from the device to the desktop and back without any conversions.
  13. SQL/e has a single user security model.
  14. SQL/e has integrated encryptions features and when creating a SQL/e data file you have the option to encrypt the database.

Other thoughts I had while reading the FAQ:

  1. Since it's pretty much just SQL Mobile wrapped up in such a way that Microsoft can support it as a desktop database format, you can start developing against it today if you have VS2005 installed; it's just not supported. The only supported platforms until SQL/e is release are Mobile, Tablet, and Desktop if you have VS or SQL installed. Does not supported mean not allowed? I believe so - there's reference to license restrictions the prevent usage of SQL Mobile as is on unsupported platforms.
  2. Since there's no support for stored procedures, I'd assume the preferred data access method would be via an ORM like NHibernate. There's already an MsSql2005 Dialect for NHibernate, so hopefully it can be modified for SQL/e very easily.
  3. Steve indicates that this is just SQL Mobile with some restrictions lifted and deployment scenarios simplified, so this is a mature database engine. This database is used by Media Center PC and several other Microsoft applications.
  4. SQL/e is being considered a data format, which is why it's fine to rename the data files, associate them with your application, etc. Microsoft considers SQL Express files as executables since they can contain and execute code (CLR, xp_commandshell). SQL/e data files are just data.
  5. SQL/e supports Reporting Services.
  6. A CTP release is expected in June (at TechEd), with a final release by end of the year. Apparently it has to do with some deployment issues such as ClickOnce deployments without administrative rights; I'd much prefer that the June release had a go-live license without the advanced deployment features. I think my solution will be to use Firebird via NHibernate and switch SQL dialects to SQL/e when it's available.

5 Comments

  • It sounds like SQL/e == SQLLite.

  • why do you need an database. serialization is so fast. i'm currently writing my own Media Library that plugs in into media player and winamp. all done in c# 2.0. and my data is completly object orientated. the search overall is about 7 times faster as winamps library with about 27k of media files. the saved data on the harddrive takes about 20 megs. loaded in less than 30 seconds. so why i ever need an database that needs an convertion of my oo structure?

  • Cristoph - Serialization might work in some cases, but it doesn't scale well and requires you to handle a lot of things databases normally take care of - searching, sorting, etc. Plus, a good database engine can make frequent small updates and still recover from an application crash. In your case, frequent updates will require high CPU to serialize, and infrequent updates make you succeptible to lost data.

    It depends on the application - your media player may not work with data that changes frequently, but many applications do.

  • Why did you drop Firebird?

  • yea, there are some limitations. but i also thought of some transaction files. so tracking changes in another file (if its just normal running) and if it crashes, it'll merge them.

    with .net 2.0 i think searching and sorting is not really a problem. it was really easy to add this stuff and its also really fast.

    and with an in process database you have the same problems with crashing.

Comments have been disabled for this content.