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:
- 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.
- The SQL/e runtime (7 DLLs are about 1.4MB in size.
- SQL/e has the same database size limitation as SQL Express, 4GB.
- 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.
- SQL/e will use the same set of classes that SQLCE and SQL Mobile have used (System.Data.SqlServerCe.*)
- 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).
- 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).
- 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.
- 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).
- 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).
- SQL/e doesn't support CLR user defined data types.
- 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.
- SQL/e has a single user security model.
- 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:
- 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.
- 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.
- 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.
- 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.
- SQL/e supports Reporting Services.
- 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.