New Embedded Database Support with ASP.NET

111 Comments

• >SQL CE 4 can now run in “medium trust” ASP.NET 4 web hosting scenarios
  
  Very nice! Thanks.
  

• Out of interest, how is this different from SQL CE 3.5? Is it a fully managed database or is it still unmanaged code + a .net Interface?
  

• Awesome work..
  
  If I'm including the SQL CE in the app_data folder and it's deployed to an Azure WebRole, does that mean it's readonly (or rather all changes will be lost when the web role instance is recycled) ?? Sry 4 this question I'm still getting my head around using azure to host my asp.net apps (that use a database like SQL CE)!
  
  
  

• @Michael,
  
  >>>>>>>>> Out of interest, how is this different from SQL CE 3.5? Is it a fully managed database or is it still unmanaged code + a .net Interface?
  
  The biggest improvement is that it now designed and tested to work in a multi-threaded environment. Previous releases of SQL CE only worked in client-apps and would corrupt/crash in server environments. SQL CE 4 specifically supports server scenarios.
  
  We also now support shared hosting and medium trust - which wasn't supported before. We are also updating SQL CE's SQL support to be more compatible with SQL Server and enable easy optional migration.
  
  Hope this helps,
  
  Scott
  

• The best part is that now we don't need to get a hosting plan that includes an SQL Server, we can just use this!
  

• @liquidboy,
  
  >>>>>>> If I'm including the SQL CE in the app_data folder and it's deployed to an Azure WebRole, does that mean it's readonly (or rather all changes will be lost when the web role instance is recycled) ?? Sry 4 this question I'm still getting my head around using azure to host my asp.net apps (that use a database like SQL CE)!
  
  SQL CE will work against any database in the \app_data folder on a hosting account that supports both read and write disk access. Most low-end shared hosting accounts do enable this. Azure unfortunately doesn't though - since they want to be able to easily scale out your application across multiple machines (one of the benefits of cloud based computing). For Azure you'd want to use SQLAzure - since that gives you full elasticity.
  
  The typical shared hosting account at DiscountASP, GoDaddy, etc though would support SQL CE.
  
  Hope this helps,
  
  Scott
  

• Scott, how about SQL CE for Silverlight client side?
  ..Ben
  

• @Ben,
  
  >>>>>>> Scott, how about SQL CE for Silverlight client side?
  
  That is something we are looking to enable as well in the future. Not there just yet though :-)
  
  Sorry,
  
  Scott
  

• Hi Scott,
  This is definitely awesome!!!
  I'm wondering if all this applies to regular winforms apps, I didn't really use SQLCE 3.5 and i'm wondering if this version will support regular winforms apps for embedding/xcopy deployment since the post stresses on asp.net.
  

• My biggest gripe, or view on this, is that pushing database updates to the server is pretty painful. Relatively speaking, you need to run update scripts against the database, and the setup of this tends to be fairly manual, not going "with" the deployment, but as an aside usually. This is because the data in your local/test db is not the same as the live/production db... I'm always surprised that database versioning and migration script generation/deployment isn't easier than it is.
  
  Just the same, this is a pretty nice option, and probably better than some of the other embedded options if it's really xcopy deployable.
  

• This looks great. I look forward to seeing more details about the limitations and usage expectations for this new database option.
  
  +1 for SQL4Silverlight
  

• Hey Scott, yet another fun update - great to see so many new things coming out so quickly after VS2010.
  
  Any chance SQL CE 4 will work with Data-Tier Application (DAC) packages?
  

• I would love to hear that sql ce 4 is supporting Geography types. My group has switched to sqlite with spatialite extensions because it bridged the gap between embedded and function in GIS. Any support for us GIS types?
  

• Hi Scott,
  
  While I'm really excited about this move and appreciate the effort that you guys did in this release, it would really help if SQL CE would also support features like Stored Procedures and Functions, as we are using SQL Express and while we love its features, stability and scalability we are having quiet a bit of deployment problems, and given that we are trying to push hard that users download a trial of our software the largest % of issues occur with the installations of SQL Express, and there's a ton of internet posts of all sort of problems, so ideally for trials we'd like to deploy our app with SQL CE however our issue is that it doesn't support SP's and UDF's. It would be really cool if these are added at some point.
  
  Regards,
  Shloma
  

• What is the profiler experience? Is there one? or if we are running and ORM do we need to use a 3rd party tool like the EF / nhibernate profiler?
  

• Thanks Scott.This is a great news after the IIS express
  

• @Tag - I see know reason that this wouldn't work with WinForms or WPF. The binaries are there - reference them and start working.
  

• IIS Express and now this. Great news in so little time. Awesome work
  

• What is the storage capacity of SQL CE 4?
  

• I'm assuming since stored procedured aren't supported, you won't be able to do ASP.NET Membership or Profiles with it. Correct?
  

• Will it support Silverlight ?
  

• Hi scott,
  I'm glad to find SQL CE. This would save my time from running the messy database script everytime in a shared hosting environment.
  Would there be intellisense for the SQL CE in Visual Studio???
  

• @Taq,
  
  >>>>>>>>> I'm wondering if all this applies to regular winforms apps, I didn't really use SQLCE 3.5 and i'm wondering if this version will support regular winforms apps for embedding/xcopy deployment since the post stresses on asp.net.
  
  You can actually use SQL CE 3.5 with winforms and wpf client applications today. It supports embedding it with the application and xcopy deployment already for client scenarios.
  
  Hope this helps,
  
  Scott
  

• @Paul,
  
  >>>>>>>> I would love to hear that sql ce 4 is supporting Geography types. My group has switched to sqlite with spatialite extensions because it bridged the gap between embedded and function in GIS. Any support for us GIS types?
  
  SQL CE doesn't currently support geographic types. It is something the SQL CE team is looking at though for the future.
  
  Hope this helps,
  
  Scott
  

• @Eric,
  
  >>>>>>>>>> What is the profiler experience? Is there one? or if we are running and ORM do we need to use a 3rd party tool like the EF / nhibernate profiler?
  
  I don't believe we currently have a profiler for it. The EF/NHibernate profilers (for example Ayende's) should work though I believe since I think they tie in at the provider level (I could be wrong about that though).
  
  Hope this helps,
  
  Scott
  

• @vik2000in,
  
  >>>>>>>>> will it work with asp.net 2.0?
  
  Yes - I believe it will work with ASP.NET 2.0. With .NET 2.0/3.5 the hoster would still need to configure something for it to work in a shared hosting environment.
  
  ASP.NET 4.0 includes support that enables it to work without the hoster having to configure anything.
  
  Hope this helps,
  
  Scott
  

• @Prabir,
  
  >>>>>>>>> but will it run on .net 3.5 without .net 4 installed?
  
  Yes
  
  >>>>>>>>> is it mono compatible? (otherwise i think most people may still stick to sqlite for embeded database)
  
  It would probably work with Mono on Windows - but the database itself is C++ code that only works on Windows and so wouldn't work on Linux.
  
  >>>>>>>>> wat are the limitations? (max 4gb file size limit? stored procs? triggers? foreign key) i think an article or link to limitations like this one http://sqlite.org/limits.html would be of great help.
  
  It currently supports databases up to 4GB in size - although we are looking to hopefully increase that. It supports foreign keys. It does not supports sprocs or triggers.
  
  >>>>>>>>> and windows phone 7 support would be great.
  
  It isn't on Windows Phone 7 just yet.
  
  Hope this helps,
  
  Scott
  

• @csokun
  
  >>>>>>>>What is the storage capacity of SQL CE 4?
  
  Currently 4GB - but that might increase before it ships.
  
  Hope this helps,
  
  Scott
  

• @Lee,
  
  >>>>>>>>> I'm assuming since stored procedured aren't supported, you won't be able to do ASP.NET Membership or Profiles with it. Correct?
  
  We are looking to potentially ship a set of providers that work with it (and do not use stored procedures). The first beta won't have this - but it is something we'll hopefully enable in the future.
  
  Hope this helps,
  
  Scott
  

• @nishit,
  
  >>>>>>>>>> Would there be intellisense for the SQL CE in Visual Studio???
  
  Designer support is enabled. If you create model classes using EF or NHibernate then code intellisense would be provided for those inside VS.
  
  Hope this helps,
  
  Scott
  

• What are the constraints ? size, tables, types, reader/write etc
  

• If I have my own box (no shared hosting scenario), is there any compelling reason to use SQL CE4 over SQL Express 2008?
  

• Scott one thing I would like to see is better two-way support for migration. Upsizing will be common but for dev scenarios it's often useful to downsize so you can test support for both SQL server and SQL CE (assuming the schema is compatible with CE). A tool to create a CE database from a compatible SQL database (excluding features that aren't supported of course) is something that has often kept me from using SQL CE even in desktop scenarios. I have several tools that work with various DBs and being able to work with either is nice, but keeping both in sync is a pain. A migration tool would make this much easier as you could work with SQL Server for dev and do the downsize to allow non-SQL machines use SQL CE.
  
  Just a thought...
  

• Scott, great news. But read that sprocs are not supported . What about views?
  
  Arun
  

• Nice work.
  
  Can we use it for small windows applications?
  

• Excellent news :) in how many months can we expect the release?
  

• Can SQLCE4 disable constraints. It's frustrating that to do ETL into a SQLCE database, I have to delete all constraints and re-add them.
  

• From you post, it appears that SQL CE 4 out of box only has tooling support for VS2010 & VS2010 Express. Is there any issues with using SQL CE 4 on Shared Host account that only supports .NET 3.5? (Understand we can down target to .NET 2.0 from VS2010 project templates) Thanks.
  

• This is awesome! help for developers, also a big step for Microsoft in the market SQLite almost dominates. From now on, no need to use Access .mdb files on WPF projects but SQL CE with Entity Framework. But I wait for the news of SQL CE support for Silverlight.
  

• This will be very very nice! And a Membership provider will be very appreciated.
  

• Great option. This is something we are looking to use in our brand new CMS built on top of ASP.NET 2.0 MVC. Would love to get more details on how up-sizing works, if client decides to upsize to full-scale SQL Server.
  

• Hi Scott, sounds excellent!!
  
  Can you talk a little bit about LinqToSQL support?
  

• @rstrahl: I have free, open source tools for migration both ways here: http://exportsqlce.codeplex.com
  

• Whether CE 4.0 support multiple application to access it simultaneously for reading?
  

• For Windows Azure. If you can somehow set the path of data directory, then I suppose you can use Azure Drive to save data files there and be able to access them from multiple instaces/roles. As soon as the beta is out I'm going to test it and let you guys know because now it's just in theory.
  

• As I said on Twitter, this is the best news I have seen since ASP.NET 4. Really really happy.
  

• Now only migration api is needed. :)
  

• Will the 4.0 version be compiled for AnyCpu? 3.5 required two different installers for x86 and x64...
  

• Please add designer support for NHibernate in Visual Studio 2010.
  

• > There are no license restrictions with SQL CE.
  
  So it's released as public domain then Scott?
  

• Stored procedures and functions are a MUST when talking about migration even if behind the scenes in SQLCE they do not really preform like a full SqlExpress or higher RDBMS. The point being that you could develop your application as you would normally and when you convert to full sql server your procedures would be there.
  

• Can you define "light-use production scenarios"? At what point does SQL CE become a non-viable option?
  
  Thanks.
  

• This is VERY exciting!
  

• this is great news, could you let us know when it's gonna be released ? :)
  

• Please support views!
  

• Scott the thing ive been frustrated in the past with "Service-based Database" that you create in Visual Studio was tehre was no way to manage it through SQL Server Management Studio unless you wanted to go through the trouble of attaching/detaching from your local sql server express (if you had it installed). I hope you create some way for hook SQL CE 4 into SQL Server Management Studio so you can do most of your table/view/storedproc design from there. Much prefer SQL Server Management design tools over whats in Visual Studio. It also would be nice to eventually add this ability to Service-Based Databases.
  

• All I can say is WOW!
  

• Why do MS continue to use/develop Jet engine? I see SQL ce, and especially v4 as a full replacement. This situation adds confusion to developers.
  

• Awesome!!..We have been waiting for such a small tool for our interim app which speaks with our main app!
  
  Great work again!
  

• Will it support fulltext search?
  

• Scott,
  
  All small MS databases such as SQL CE 4, SQL Express have limitation on the data file size that rules out real life usage - please notice why SQLite, mySQL are selected - mostly because of this restriction.
  
  It is a very similar decision to release WPF without a datagrid control for a couple of years - result - no one used it.
  
  Please remove the artificial file size limitation, other wise it is not suited for production and no one will use it.
  

• Scott.
  
  "It isn't on Windows Phone 7 just yet."
  
  This sounds VERY interesting!
  Would you like to expand on it a little?
  
  A. "But it will be soon."
  B. "Sorry I mis-typed."
  C. "Gosh with in memory databases and 'the cloud' you'll never need it."
  D. "No further comment."
  

• Scott,
  
  This sounds really interesting and exciting. One quick question, I'm assuming in order to make schema updates to an embedded database one will need to stop the website to unlock the db file, then download it and make changes locally. Is this correct?
  

• Brilliant!
  
  Kudo's guys - this (and IIS Express) are things that we as IT software development companies *NEED* to be better businesses.
  
  Quick technical question, will we be able to use the ADO.NET classes from the System.Data.SqlClient namespace or would we need to switch to objects from System.Data.SqlServerCe?
  
  (Just would like to know since some projects do not use the abstract classes/interfaces and targets the provider directly).
  

• I'm glad you fixed the admin install issue, but SQL CE has several other problems. The biggest for me is the lack of backwards compatibility or even ability to detect what version a database file is. It seemed like the only thing we could do is try to open the file, and if it failed try to upgrade the file and reopen it.
  

• Scott, how should I use SQL CE in my app? Through special ADO.NET provider (like SqlConnectionCE) ? If so, that about what easy migration to SQL Server do you talk? We have to change all ado.net code.
  Or SQL CE can be used through "normal" SqlProvider (for SQL Server) ?
  Thanks.
  

• Scott how abt the security level of SQL CE ?
  Will be that be same as SQL Server or since it si file ASP.Net features will be applied.
  
  Thanks,
  Thani
  

• This is really cool news!!!
  
  hmmm loving it, I am excited to use it in my back end support system for my small company.
  
  Thanks Scott.
  

• Hello,
  
  I have a few questions:
  
  1 - Does this support file stream?
  2 - And can I use a T-SQL script to create the database, its tables, relationships, etc?
  3 - When will the final version be released?
  
  Thanks,
  Miguel
  

• This is really SQLite with a new Microsoft 'name'
  

• This are Very great news! I'm waiting for the next week!
  

• You are starting to read our minds. My only wish is that this was available 4-5 years ago. Great news though. I'm looking forward to trying it out!
  

• * +1 for asking about linq-to-sql support - nothing wrong with EF per se, but it would be good to know definitively if this will work since linq-to-sql worked fine with SqlCE AFAIK
  
  * +1 for listing which existing SQL Server tools (SSMS, SSRS, etc) you could use against this. Since it's in-memory, does that mean no external processes could access it (at least, without adding your own bridge)?
  
  * will there be 32-bit and 64-bit versions? Or is it straight MSIL and a class library targeting 'Any CPU'?
  
  * are the resulting database files platform/bitness/host agnostic?
  
  * will there be tools to convert to/from 'full' SQL databases (mdf/ldf)?
  
  * what happens with a 'rude' death of the host process (machine dies, for instance, something less orderly than a normal appdomain shutdown)? Is there some kind of journaling so the file can be / will be automatically recovered when the app starts again?
  
  * what's the story with multiple processes accessing the database? SQLite (by comparison) does a pretty good job of dealing with multiple processes reading and writing to it, so I'm assuming this is taken care of already - for instance, a normal asp.net app recycle could have multiple separate appdomains running the same app (as one is flushing out requests and the other spins up). How do the multiple processes coordinate? some shared memory or other IPC mechanism or through the sdf itself?
  
  * is backup just backing up the file? can it be backed up without stopping all processes accessing it?
  
  * does the file have to be in a recent NTFS filesystem (not sure if it uses the relatively new xaction stuff in there or not)?
  
  * can the file be hosted in something that does other syncing (for instance, syncing out the database to mesh or skydrive or whatever) without problems?
  
  Finally, if you can get someone on your team (or some MVP, or whoever) to do a blog post (or whatever) that runs down this new SQL CE v4 against SQLite, I'd appreciate it - 2 of my upcoming personal projects could benefit from it, so it'd be nice to have someone do a compare/contrast as SQLite
  

• Good news!!
  

• Has the issue with WinMobile where the application must open the database twice been resolved. I have several client only WinMobile apps with a custom data layer class that open the file twice.
  

• SQL CE 4 would be very very useful on a website if it supported ASP.NET Membership but no stored procedures probably kills that until somebody can rewrite the need to stored procedures to install and run Membership.
  

• Will it support Windows Phone 7? :)
  

• Awesome! I was waiting for this for long.
  
  Please ship a provider for ASP.net Membership and Roles for this, as you promised, so that It will be really usable for developing small web apps.
  

• So the fact it works in shared hosting is a feature of .Net 4, nothing to do with medium trust compliance? Because unless the rules have changed unmanaged C++ code can't run in medium trust.
  
  

• Suggest you ask any questions in the SQL Server Compact MSDN forum here: http://social.msdn.microsoft.com/forums/en-US/sqlce/threads/
  

• Scott,
  
  That's very exciting. What about performance? It should be faster than working with XML, however how much memory does it consume? I hope not much, so that it can work smoothly in a shared hosting environment.
  

• I don't get this... Why is Microsoft all the time trying to be better than open-source. In long term perspective it's very harmful for .net and stops creative users movement.
  
  Why not instead fork Sqlite or port it to .net or support authors of .net interfaces?
  
  .Net is already one of worst community supported technologies, looking at numbers of users and such Microsoft initiatives make it even worse. Compare to any other language/platform... What is happening?
  

• Regarding Azure - this is not viable for Azure at all - as soon as the role is cycled disk contents are gone. Someone mentioned using this to write to X:Drive - that seems like a really, really bad idea. For Azure, you'll either want to go with a non-relational solution (blobs, tables) or pay for SQL Azure - it's not that expensive. If price is that big of a consideration, shared hosting with SQL CE makes a lot of sense.
  

• What about linq-to-sql support: It worked fine with SqlCE 3.5, but does SqlCE 4.0 support it too ?
  

• Just want to echo other comments regarding a big "thumbs up" for any thoughts about getting SQL CE 4 on Windows Phone 7. Go on, you know it makes sense! ;)
  

• This is great! Now I can support my little non-profit educational websites without having to store data in XML files. The upgrade path should be easy when needed, but the startup time will be much faster. Thanks.
  

• 
  Good stuff but there are quite a few things that are missing and we have all been waiting for! Can I ask you to include some things on this new version?
  
  - Support for bulk insert/update, it takes forever to insert records if you have quite a few
  - Support for getting next ID, this would be fantastic, I had to use GUID for IDs on previous versions
  - Support for Stored Procedures that will compile while db engine run in-memory (very cool if you can do this)
  - Support for views (same as above)
  

• Great work scott!!!! Thx a lot.
  

• Another request for SQL CE for Silverlight 5. Or at least a bolt-on API for the WM7 Silverlight.
  

• This could be the best thing lince sliced bread!
  Ever since MSAccess somehow became a no-no for web deployment scenarios, I've been hoping for another option. Can't wait!
  

• "SQL Compact does not support opening database files on a network share"
  
  Will version 4 finally allow me to use this in a network environment?
  
  Regards
  

• Any news on VS2010 .dbml designer support for SQLCE?
  
  It'd sure be nice to have that--we L2S users are feeling a bit left out. Red-headed stepchildren and all ;-)
  
  

• My experience with SQL CE 3.5 in combination with EF was not that great. A lot of problems occured while working on it, esspecially the problem where the server was not able of sending back data on manipulations, e.g. when adding new records to a table with auto incremented id's, the new id's were not beeing send back which basicly corrupted your EF model. Is this fixed?
  

• Will 4.0 Sql Ce support encrypting the database with AES256?
  
  

• Would it be possible to use SQL CE in a sandboxed solution of sharepoint 2010? You cannot write directly on the disk, but writing to a file in a document library could bypass the security constraint. That means that the data has to be saved in a file reachable only by its url.
  

• This is great news!
  
  I was worried that I would have to start using SQLite since VistaDB is going out of business. I can't believe this VistaDB thing I bought is going to be useless now that their product activation servers are going down. This SQL Server CE 4.0 came just in time! Thank you for making SQL Server CE 4.0 possible!
  

• Is there a way to use 4.0 CTP1 without using WebMatrix? I downloaded and installed CE 4.0 but I am not sure how to access it. I checked in VS2010 and dont see it listed in the database explorer.
  
  

• wow lots of questions in here ;) glad to see sql ce growing up a bit more... I've been using it since the windows mobile only days, and recently had triedd using it in a winforms app using EF... and the experience was terrible.
  

• I would just like to add another vote for using this with Silverlight and WP7. Ideas are already starting to appear in my head.
  

• Is is possible to connect from two different PCs to the same database (UNC path)?
  
  This is possible with VistaDB, which is discontinued as of 1th August 2010, so I am looking for alternatives.
  
  Thanks
  Uwe
  

• I think this will be very popular solution for ASP.NET hosts. Questions: I have a MSAccess driven site (I know, I know...) Can I easily migrate this over to SQL CE? How does it compare to Access? Can use it as a back end for an Access application? Any limitations I should know about? I know it currently does NOT support stored procedures--is that going to change any time soon?
  

• any tutorials or walkthroughs on this subject would be great either in visual c++ or visual c#
  

• Yes a tutorial or test-project would be great, to see how you set this up in vs2010
  

• I was actually just working on an asp.net project and thought, "Hey, wouldn't it be nice if I could just use SQL Server Compact?". Wish granted. This is a very welcome update. Thanks.
  

• Hi Scott:
  
  All I can say is WOW and THANK YOU.
  
  I've been a fan of SQL Server "Everywhere" (the original name) and have been following it since. Was a bit concerned about its future ("beta product jitters" and creating production desktop apps based off of it) when Steve Lasker sort of, well, I don't really know...relieved when it became SQL CE (official product).
  
  IMHO, this is a great move to bring "small scale" (inclusive of cost/complexity) web apps to the MS/ASP.Net platform!
  
  It also makes things highly portable (just copy/transfer/move) a file and that's it!
  
  I've been using VS and/or SQL Server Management Studio for "IDE access" so I hope there's some work, or at least clarity, on what tools are available for non-developer (end user) types - sample use case: "how do I copy the data to Excel"? Currently am addressing this question by code (create an Excel XML file).
  
  Anyway, great work and thanks again!
  
  Ed
  

• The beta is already available?
  

• Can this be used as a back end in an Access application as SQL Express can through an ODBC connection?
  

• why my ide does not have it?
  i have installed vs2010 web developer for study
  

• This is a great idea, but without the support of stored procedures and user defined functions is useless for real programming. You should have a version that supports this functionality.
  
  

• I have posted a "sample" SQL Server Compact 4.0 Memebership provider on CodePlex: http://sqlcemembership.codeplex.com - please give it a try, and provide feedback and/or fixes at the CodePlex site.
  

• This appears not to be compatiable with Synchronization Framework 2. Any ideas if and when this might be? I am currently working on an app that requires Synch Framework and I have it working with 3.5, but when trying to upgrade to CE 4 it throws an incompatiable version error.