Steve Lasker's Web Log

Information about SQL Server Compact Edition (SQLce)

On April 6, 2006 Paul Flessner announced SQL Server Everywhere Edition, know known as SQL Server Compact Edition.  http://www.microsoft.com/sql/letter.mspx  SQL Server Compact Edition a new SKU in the Microsoft product platform.  Paul announced that we would have a CTP in the summer of ’06 with final release in the 2nd half of ’06. 

So, what is SQL Server Compact Edition?  Here’s some Q&A that will hopefully help.  Some of this is still not finalized.  So take this information as our current thinking, not a final decision.  The purpose of this post is to give you our current thinking and solicit feedback. 

Q: Is SQL Server Compact Edition a new V1 product? 
A: No.  SQL Server Compact Edition is a new product name to acknowledge the new breadth of reach.  SQL Server Compact Edition is the SQL Server Mobile edition product with its limitation to the mobile platform removed.

Q: What’s SSEv and SSE?
A: Just my shortcut for SQL Server Compact Edition.  SSE is how I refer to SQL Server Express.  These are NOT official abbreviations, just my shorthand. 

Q: Should I be concerned about yet another V1 product?
A: No.  As noted above, SSEv is SQL Mobile with the license restriction removed for the Windows desktop platform.

Q: SQL Mobile and SQL CE were released on the Mobile platform, should I be concerned about SSEv and its stability on the desktop platform?
A: SSEv has been used on the desktop platform within several Microsoft products for years.  MSN Client, Media Center PC and several other products have already been using SSEv.  In order to test SQL Mobile and its predecessor, SQLCE, Microsoft has always tested the codebase using the Windows Platform.  While we do testing specifically on the mobile platform, most of our testing is actually done using the Win32 platform.

Q: Is this a modified version of SQL Express? 
A; No, SSEv is based on the SQL Mobile codebase.

Q: Will SQL Express be continued?
A: Yes, SQL Express is our free version of our server product, SQL Server. 

Q: Which database should I use for my desktop apps?
A: This is a longer discussion than what I’m trying to cover in this Q&A, but both SSE and SSEv will be available for the general desktop scenario.  However, SSEv 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.  SSE is a free version of the server product.  It runs as a service, and while SSE has been tuned to be more efficient about resource usage, it is a server based product.  General guidance is to start with SSEv.  SSE is well suited when you want exact functionality of the server platform, but are willing to deploy a server based product that will require significantly more resources than SSEv.  When considering local data stores, consider the bigger picture of the problem.  A users machine is a different operating environment than a server. 

Q: Will SSEv have the same licensing constraints as SQL Mobile?
A: No.  SSEv will be enabled on all Win32 desktop platforms. 

Q: Can I use SSEv as my web server database?
A: No.  SSEv is targeted at the desktop database.  Our plan is for SSEv to throw a not-supported exception when the hosting process is IIS.

Q: Why won’t SSEv be enabled for web applications.
A: SSev is targeted at client database scenarios.  The SQL Server SKUs, such as SQL Express are targeted as “data as service”.  By maintaining these two product lines as separate targeted scenarios we can enable features that would only apply to each scenario.  One size fits all means one size fits no-one.
SSEv will use the same set of classes that SQLCE and SQL Mobile have used.  System.Data.SqlServerCe.*  Because of this, if a website developer started with SSEv then wanted to switch to one of the Server based SQL SKU’s, such as SSE, they would have to change their ADO.net data access code. 
If a developer was building a client application and started with SSEv and wanted to scale their application to multi-user, they wouldn’t necessarily get rid of their local database.  The design we’re shooting for is for developers to keep their local database using SSEv and synchronize the data in their SSEv database with a server database using one of the various sync technologies.  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 SSEv product.  In addition to these sync technologies, we are working on a new set of sync components based on the ADO.net programming model that we hope to ship in the Orcas product.

Q: Will Microsoft have a unified ADO.net provider?
A: We are considering various ways to abstract our different database products while minimizing the code a developer would have to change.  Rather than wait for a unified provider, we decided the client and data service scenarios are independently big enough to make the products we already have available for developers to start programming today.

Q: What is the feature set of SSEv?
A: For the ’06 release, it will be the same feature set as SQL Mobile.  http://www.microsoft.com/sql/editions/sqlmobile/sqlmobile.mspx

Q: When will SSEv be available?
A: If you have Visual Studio 2005 you already have a version you can start developing against.  http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx
 We plan to release a CTP of the SQL Server Compact Edition product at Tech Ed ’06 and RTM later this year.

Q: If the feature set of SSEv is the same as SQL Mobile, why do I need to wait ‘till June for a CTP and the end of the year for a final release?
A: While SQL Mobile has already been available for the Win 32/Tablet PC platform, and we already have a lot of experience with it in released Microsoft products, we have some installation decisions we want to finalize.  Our plans are to have a CTP of SSEv that have an installation model other than just copying the dlls to your application directory.

Q: SQL Mobile can be ClickOnce deployed without administrative rights.  Will SSEv have this same ability?
A: This is the main reason we’re not releasing immediately.  Because SQL Mobile can be deployed as private dlls, Microsoft doesn’t currently have a direct way to service these dlls in the case of a critical servicing issue.  We have several technical solutions that we are actively working on and we hope to be able to maintain this non-admin deployment model with SSEv. 

Q: Is SSEv suspect to the same threat that MSDE incurred with Slammer?
A: Well, never say never, but the main difference between MSDE and SSEv is MSDE and SQL Express run as a service.  While SQL Express has its port listening features disabled by default, it does run as a service and these features can be turned on.  Because SSEv doesn’t run as a service, doesn’t ever listen to network traffic, and doesn’t run without the hosting of an application, it doesn’t have the same “surface area” as its server based siblings.  Another key feature of SSEv is it doesn’t allow any code to be placed in the database.  It’s a pure data format.  Because of these differentiations, users should be much more comfortable with SSEv.  Although I’m unaware of any servicing issues in the SQL CE, SQL Mobile history, we do not want to assume we’d never have an issue.  Because of this, we are exploring several servicing options that will hopefully maintain the ClickOnce deployment model without requiring admin rights for installation of SSEv

Q: Can I use SSEv data files as my application document format? 
A: Yes, SSEv data files are pure data.  Unlike the server SKU’s, SSEv doesn’t have any code. No sprocs, views, triggers, extended sprocs, macros or ability to run XP_CmdShell.  Because of this, we do consider SSEv files a doc safe format. 

Q: Can I change the extension of SSEv files and associate that extension with my application?
A: Yes.  Because SSEv has a doc safe format, SSEv files can be emailed and their extensions can be changed to launch your application.  Visual Studio 2005 will only support .sdf file extensions, however, this is a limitation of the designers.  The SqlCeConnection object can handle any extension you wish.

Q: Does SQL Express support the changing of its extension? 
A: No.  SQL Express data file format, .mdf, contains code.  Because of this, Microsoft limits file based ADO.net connections to the MDF extension.  Users should consider .mdf files like exes.  They are just as capable of running code as an exe.

Q: How big is the SSEv runtime?
A: For all 7 dlls, the entire runtime is 1.4mb.  This is less than a SQL Express .mdf file.

Q: Does SSEv support multiple connections?
A: Yes.  One of the changes in SQL Mobile 2005 was the addition of multiple connection support.  You can now have several connections in your UI layer and another connection for background sync?  I believe the connection limit is 256 connections.  If you hit this limit, it would be interesting to know why? 

Q: Will SSEv support files placed on network shares?
A: Yes, but this is not meant as a multi-user scenario.  Because we know that users place their documents and settings directory on other servers we’ve enabled this scenario.  However, since SSEv is not a client/server database infrastructure the SSEv engine that opens the file will only share that file with other applications on the same machine.  If another user attempts to open the same data file from another machine the user will receive a connection open error.  We are working hard to make sure we don’t paint ourselves, or allow developers to paint themselves into a corner and travel down a dead end path.  SSEv is a user centric data store.  SQL Server is our data service platform.

Q: Does SSEv have a different set of data types than SQL Server?
A: No.  SSEv has a strict subset of the SQL Server Datatypes.  Developers that build their apps initially with SSEv will not have any issues converting their data up to SQL Server SKUs.  This was not the case with Jet to SQL Server conversions.

Q: Does SSEv have the XML data type?
A: No.  SSEv will place XML in an nText datatype when data is synchronized between SQL Server and SSEv.  So, you still have the XML storage, but SSEv will not have X Path query support in its engine.  This is something we’re considering for a future release.  However, the specific implementation has yet to be decided.  Our main goal is to keep SSEv as small and lightweight as possible.  The LINQ project opens up many interesting possibilities.  In future version we may leverage LINQ to get XML query capabilities without having to build an XML query engine directly into the SSEv engine.

Q: Does SSEv support CLR UDT data types?
A: No.  Because SSEv runs in proc with your application, the idea of hosting the CLR within the database would be a little redundant.  The database is actually hosted within the CLR.  You can place UDT data types in binary data columns, but just as with the XML data type, you will not be able to query into properties of these CLR UDTs.  Also like the XML data type, we may be able to leverage features of LINQ to enable this scenario without having to increase the size of the SSEv engine.

Q: Does SSEv have any unique features from its SQL Server products?
A: Yes.  Because SSEv is focused on local, in-proc data scenarios we can expose features that would otherwise be difficult to expose without scaling limitations.  SSEv will support the SqlCeResultSet api that was shipped in SQL Mobile 2005.  This feature enables developers to create a databindable, updatable, scrollable cursor.  As a user makes a change to a row, the values are immediately pushed back to the database. There’s no need for DataAdapters or DataSets.  SqlCeCommand includes some Seek like features that allow developers to leverage specific indexes as well.

Q: Does SSEv have Typed DataSet support and Drag & Drop support with the Data Sources Window?
A: Yes.  In Visual Studio 2005, developers can now create Typed DataSets for both their desktop and device projects.  For a walkthrough see: http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx

Q: Can the same SSEv data file be shared between device and desktops?
A: Yes.  Since SSEv is based on the SQL Mobile codebase, developers can simply copy their .sdf file from the device to the desktop and back without any conversions.

Q: Does SSEv have a security model?  How does this compare to SQL Express?
A: Yes.  SSEv has a single user security model.  Because SSEv is targeted at per/user data scenarios, the assumption is the data is already partitioned to the user.  The application would provide the Username and Password in the connection string.  Because SSEv is targeted at desktop scenarios, there is no way to bypass the username and password authentication model.  While most would consider the SQL Server product line more secure, they would be correct for server scenarios.  However, the security model for the SQL Server product line is at the entry to the server, not the .mdf file.  Once an end user has access to the .mdf file, the user could use the SQL Express User Instance feature to connect to the mdf file as the DBO.  This bypasses any SA password that may have been applied to the database.  Therefore, under typical scenarios, SSEv will have a better security model that will limit users from viewing the data without your application, or the username and password.

Q: Does SSEv have any encryptions features?
A: Yes.  When creating a SSEv data file you have the option to encrypt the database.  While not nearly as granular as the SQL Server encryption features, this level of encryption provides security against users trying to prod into your data files without permissions.

Q: Can two users on the same machine share the same SSEv data file?
A: Yes.  While SSEv isn’t a data service platform, two users on the same machine would leverage the multi-user features.

Q: Does SSEv support multiple connections on different processes?
A: Yes. 

Q: Does SSEv support stored procedures, or views?
A: No.  Because SSEv is targeted at lightweight, in-proc scenarios these features aren’t available.  However, these features are typically used to provide multi-user abstraction from the underlying database schema.  Because SSEv runs in-proc with your application these features would typically provide more overhead than value.  Because SSEv runs in-proc with your application, why limit yourself to T-SQL within a stored procedure?  By creating a data access layer to your SSEv database you can use the full power of the .NET Framework. 

Q: Does SSEv support Triggers?
A: No.  Triggers do offer some interesting scenarios for eventing models.  We are looking at different models for implementing triggers that could benefit from an in-proc model. 

Q: Is SSEv available for native development with C++?
A: Yes.

Q: Is this a definitive list of features and decisions for the SQL Server Compact Edition product?
A: No.  This is our current thinking and baseline of the SQL Mobile product we are leveraging for SQL Server Compact Edition. 

Please send feedback on any and all comments.  Is there something I didn’t cover?  Let me know and I’ll add it to the list.

Steve