I recently got asked about the future of SQL Server Compact, and in this blog post I will elaborate a little on this and the present state of SQL Server Compact.
Version 4.0 is the default database in WebMatrix ASP.NET based projects, and version 2 of this product has just been released.
There is full tooling support for version 4.0 in Visual Studio 2012, and the “Local Database” project item is a version 4.0 database (not LocalDB). In addition, Visual Studio 2012, coming in august, will include 4.0 SP1, so 4.0 is being actively maintained currently. Entity Framework version 6.0 is now open source, and includes full support for SQL Server Compact 4.0. (Entity Framework 6.0 will release “out of band” after the release of Visual Studio 2012).
The latest release (build 8088) of version 3.5 SP2 is fully supported for Merge Replication with SQL Server 2012 (note that "LocalDB" cannot act as a Merge Replication subscriber), and Merge Replication with Windows Embedded CE 7.0 is also enabled.
On Windows Phone, version 3.5 is alive and well, and will of course also be included with the upcoming Windows Phone 8 platform. Windows Phone 8 will also include support for SQLite, mainly to make it easier to reuse projects between Windows Phone 8 and Windows 8 Metro.
On WinRT (Windows 8 Metro Style Apps), there is no SQL Server Compact support, and Microsoft is currently (doubt that will change) offering SQLite as an alternative. See Matteo Paganis blog post also: http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications
So, currently SQL Server Compact is available of the following Microsoft platforms: Windows XP and later, including ASP.NET, Windows Phone, Windows Mobile/Embedded CE.
On the other hand, SQL Server Compact is not supported with: Silverlight (with exceptions), WinRT (Windows 8 Metro Style Apps).
So I think it is fair to conclude that SQL Compact is alive and well. In some scenarios, SQL Server "LocalDB" is a very viable alternative, notice that currently LocalDB requires administrator access to be installed (so no "private deployment"). See my comparison here.
12 comments:
Erik, will you be extending your tools to support SQLite? If not, any suggestions on how to take an existing SQL/SQL Compact database and create SQLite-compatible LINQ methods?
Thanks.
Philip
Philip: Maybe, I assume first step would be a tool to create a SQLite script from a SQL Compact database
Philip: The new version 3.1 of my SQL Server Compact Toolbox add-in includes a feature to script a SQL Compact database to SQLite
Hi Erik,
Here is a late comment regarding the future of SQL Server Compact.
My feeling is that the situation becomes more an more confusing :
* SQL CE 4.0 does not support merge replication nor the synchronization framework
* Visual Studio 2012 does not support SQL CE 3.5
=> replication with SQL CE requires Visual Studio 2008
* The SQL CE engine on Windows Phone is now referred as "Local Database", and Microsoft does not guaranty file format compatibility with PC versions.
* The SQL CE engine on Windows Phone does not include a replication client
=> no simple solution for replication data on Windows Phone.
Add to this the fact that SQLite is the database engine for Windows 8 (and also theorically usable on Windows Phone 8).
So much versions with heterogeneous limitations. We can legitimately wonder about the future of embedded database engines...
Thanks
Michael
Mick: Thanks for your thoughts, Mick. SQL CE 4.0 does work with SyncFx, and you can use 3.5 just fine with VS 2012, just not in Server Explorer.
>SQL CE 4.0 does work with SyncFx
I agree that there is a trick for doing so (http://jtabadero.wordpress.com/2012/04/20/sync-framework-and-sql-compact-4-yes-you-can/), but is there anything official ?
Thank you,
Michael.
No, nothing oficial.
So if I'm looking at creating an application for Windows Phone 8 and I want some sort of database storage, I should use SQLite or SQL Compact Edition 3.5 or SQL Compact Edition 4.0. I have no need to replication.
Hi Jeff, you can use Local Database (SQL Compact 3.5) or sqlite with Windows Phone 8. But you can only use sqlite with Windows Store Apps
According to Microsoft, SQL Server Compact is in "in deprecation mode with no new releases planned near future": link.
Do you still think that "SQL Compact is alive and well"? To me, it sounds like it will share the same fate as Silverlight.
Lukamicoder: Yes, I have seen that, and agree, SQL Server Compact will not see any future Development, only maintenance/support. Regard it as a "mature/complete" product.
The main disadvantage LocalDB has is the missing password protection. A SQL Compact Connection can easily protected with a password in the connection string. Securing a Local DB (especially the database files which can be copied by a attacker) is very difficult or impossible.
Many people use sql compact on a desktop computer and had no big secure server. Also they have very little (or no) knowledge about sql. Writing an installer which installs a LocalDB secure on their system is very difficult.
Therfore we use further the sql compact and the LocalDB is (for us) a junk product for local usage.
Post a Comment