New Embedded Database Support with ASP.NET

Earlier this week I blogged about IIS Express, and discussed some of the work we are doing to make ASP.NET development easier from a Web Server perspective.

In today’s blog post I’m going to continue the simplicity theme, and discuss some of the work we are also doing to enable developers to quickly get going with database development.  In particular, I’m pleased to announce that we’ve just completed the engineering work that enables Microsoft’s free SQL Server Compact Edition (SQL CE) database to work within ASP.NET applications.  This enables a light-weight, easy to use, database option that now works great for ASP.NET web development.

Introducing SQL Server Compact Edition 4

SQL CE is a free, embedded, database engine that enables easy database storage.  We will be releasing the first public beta of SQL CE Version 4 very shortly. Version 4 has been designed and tested to work within ASP.NET Web applications.

Works with Existing Data APIs

SQL CE works with existing .NET-based data APIs, and supports a SQL Server compatible query syntax.  This means you can use existing data APIs like ADO.NET, as well as use higher-level ORMs like Entity Framework and NHibernate with SQL CE.  Pretty much any existing data API that supports the ADO.NET provider model will work with it.

This enables you to use the same data programming skills and data APIs you know today.

No Database Installation Required

SQL CE does not require you to run a setup or install a database server in order to use it.  You can now simply copy the SQL CE binaries into the \bin directory of your ASP.NET application, and then your web application can run and use it as a database engine.  No setup or extra security permissions are required for it to run.  You do not need to have an administrator account on the machine.  It just works.

Applications you build can redistribute SQL CE as part of them.  Just copy your web application onto any server and it will work.

Database Files are Stored on Disk

SQL CE stores databases as files on disk (within files with a .sdf file extension). You can store SQL CE database files within the \App_Data folder of your ASP.NET Web application - they do not need to be registered in order to use them within your application. 

The SQL CE database engine then runs in-memory within your application.  When your application shuts down the database is automatically unloaded.

Shared Web Hosting Scenarios Are Now Supported with SQL CE 4

SQL CE 4 can now run in “medium trust” ASP.NET 4 web hosting scenarios – without a hoster having to install anything. Hosters do not need to install SQL CE or do anything to their servers to enable it.

This means you can build an ASP.NET Web application that contains your code, content, and now also a SQL CE database engine and database files – all contained underneath your application directory.  You can now deploy an application like this simply by using FTP to copy it up to an inexpensive shared web hosting account – no extra database deployment step or hoster installation required.

SQL CE will then run within your application at the remote host.  Because it runs in-memory and saves its files to disk you do not need to pay extra for a SQL Server database.

Visual Studio 2010 and Visual Web Developer 2010 Express Support

VS 2010 and Visual Web Developer 2010 Express will add SQL CE 4 tooling support for ASP.NET scenarios in an update we’ll be rolling out in the future.  This will enable you to add SQL CE database files to your ASP.NET projects, use the Visual Studio Server Explorer to create and edit tables in them, and use higher-level designers like Entity Framework (see below) to model and map the database to classes that you can then query and program against using LINQ.

image

This means that in addition to using the same data APIs you know today, you will also be able to easily use the same development tools you already know with SQL CE.

Supports Both Development and Production

SQL CE can be used for both development scenarios and light-usage production usage scenarios.  With the SQL CE 4 release we’ve done the engineering work to ensure that SQL CE won’t crash or deadlock when used in a multi-threaded server scenario (like ASP.NET).  This is a big change from previous releases of SQL CE – which were designed for client-only scenarios and which explicitly blocked running in web-server environments.  Starting with SQL CE 4 you can use it in a web-server as well. 

There are no license restrictions with SQL CE.

Easy Migration to SQL Server 

SQL CE is an embedded database – which makes it ideal for development and light-usage scenarios.  For high-volume sites and applications you’ll probably want to migrate it to use SQL Server Express (which is free), SQL Server or SQL Azure.  These servers enable much better scalability, more development features (including features like Stored Procedures – which aren’t supported with SQL CE), as well as more advanced data management capabilities.

We’ll ship migration tools that enable you to optionally take SQL CE databases and easily upgrade them to use SQL Server Express, SQL Server, or SQL Azure.  You will not need to change your code when upgrading a SQL CE database to SQL Server or SQL Azure.  Our goal is to enable you to be able to simply change the database connection string in your web.config file and have your application just work.

Summary

SQL CE 4 provides an easy, lightweight database option that you’ll now be able to use with ASP.NET applications.  It will enable you to get started on projects quickly – without having to install a full database on your local development box.  Because it is a compatible subset of the full SQL Server, you write code against it using the same data APIs (ADO.NET, Entity Framework, NHibernate, etc).

You will be able to easily deploy SQL CE based databases to a remote hosting account and use it to run light-usage sites and applications. As your site traffic grows you can then optionally upgrade the database to use SQL Server Express (which is free), SQL Server or SQL Azure – without having to change your code. 

We’ll be shipping the first public beta of SQL CE 4 (along with IIS Express and several more cool things I’ll be blogging about shortly) next week.

Hope this helps,

Scott

P.S. In addition to blogging, I am also now using Twitter for quick updates and to share links. Follow me at: twitter.com/scottgu

Published Wednesday, June 30, 2010 6:33 PM by ScottGu

Comments

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:45 PM by rajbk

>SQL CE 4 can now run in “medium trust” ASP.NET 4 web hosting scenarios

Very nice! Thanks.

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:50 PM by Michael Stum

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?

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:52 PM by liquidboy

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)!

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:55 PM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:56 PM by bill gates ii

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!

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:57 PM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:58 PM by Ben Hayat

Scott, how about SQL CE for Silverlight client side?

..Ben

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 9:59 PM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:14 PM by Tag

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.

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:18 PM by Michael J. Ryan

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.

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:23 PM by Aaron

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

+1 for SQL4Silverlight

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:30 PM by Troy Goode

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?

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:45 PM by Paul Q

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?

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:47 PM by Shloma Baum

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

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:51 PM by Eric Hexter

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?

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:52 PM by shiju

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

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 10:56 PM by scott cate

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

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 11:14 PM by vik20000in

will it work with asp.net 2.0?

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 11:25 PM by Snahider

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

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 11:25 PM by csokun

What is the storage capacity of SQL CE 4?

# re: New Embedded Database Support with ASP.NET

Wednesday, June 30, 2010 11:29 PM by Lee Dumond

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:07 AM by Jirapong Nanta

Will it support Silverlight ?

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:24 AM by nishit_123

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???

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:29 AM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:38 AM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:39 AM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:41 AM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:43 AM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:43 AM by ScottGu

@csokun

>>>>>>>>What is the storage capacity of SQL CE 4?

Currently 4GB - but that might increase before it ships.

Hope this helps,

Scott

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:44 AM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:45 AM by ScottGu

@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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:56 AM by Aaron

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:56 AM by Portman

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 1:33 AM by rstrahl

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...

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 1:39 AM by Arun Mahendrakar

Scott, great news. But read that sprocs are not supported <sad>. What about views?

Arun

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 2:14 AM by Murat YILMAZ

Nice work.

Can we use it for small windows applications?

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 2:53 AM by loginitin

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 2:59 AM by RichB

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 3:00 AM by Brian Henderson

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 3:00 AM by Enes TAYLAN

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 3:16 AM by Esben

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 3:27 AM by Parag

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 3:44 AM by Eric

Hi Scott, sounds excellent!!

Can you talk a little bit about LinqToSQL support?

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 4:36 AM by erikej

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 4:46 AM by Arun

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 4:47 AM by Panagiotis Kefalidis

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 6:07 AM by Fox

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 6:39 AM by Soe Moe

Now only migration api is needed. :)

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 6:43 AM by johan

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 8:44 AM by Steve

Please add designer support for NHibernate in Visual Studio 2010.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 9:23 AM by Steve

> There are no license restrictions with SQL CE.

So it's released as public domain then Scott?

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 10:20 AM by Anthony

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 10:27 AM by ebonnett

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

Thanks.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 10:45 AM by Aleah

This is VERY exciting!

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 11:06 AM by ayo

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 12:34 PM by rodo

Please support views!

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 1:17 PM by Ncage

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 3:46 PM by John Aghadiuno

All I can say is WOW!

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 4:05 PM by Olivier

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 4:32 PM by Guruparan

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

Great work again!

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 6:34 PM by Stefan Olson

Will it support fulltext search?

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 7:30 PM by RD

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.

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 8:19 PM by Gareth

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."

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 9:21 PM by Jim

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?

# re: New Embedded Database Support with ASP.NET

Thursday, July 1, 2010 10:55 PM by Jaans

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).

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 1:02 AM by Jonathan Allen

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.

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 6:07 AM by Shrike

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.

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 6:08 AM by Karl

Are auto-generated keys supported when SQLCE 4 is used via EF ?

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 7:44 AM by MisterFantastic

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

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 8:22 AM by dotnetfunda

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.

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 12:23 PM by Miguel

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

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 1:46 PM by SQLite

This is really SQLite with a new Microsoft 'name'

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 4:45 PM by ignatandrei

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

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 8:54 PM by Brent

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!  

# re: New Embedded Database Support with ASP.NET

Friday, July 2, 2010 10:28 PM by InigoMontoya

* +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

# re: New Embedded Database Support with ASP.NET

Saturday, July 3, 2010 1:34 AM by chandanx

Good news!!

# re: New Embedded Database Support with ASP.NET

Saturday, July 3, 2010 8:10 AM by Clifton Collins

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.

# re: New Embedded Database Support with ASP.NET

Saturday, July 3, 2010 2:30 PM by Clinton Gallagher; METROmilwaukee.com

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.

# re: New Embedded Database Support with ASP.NET

Saturday, July 3, 2010 10:43 PM by Mr.Brooks

Will it support Windows Phone 7? :)

# re: New Embedded Database Support with ASP.NET

Sunday, July 4, 2010 1:07 AM by Sumit Pranav

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.

# re: New Embedded Database Support with ASP.NET

Sunday, July 4, 2010 8:49 PM by Jason Short

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.

# re: New Embedded Database Support with ASP.NET

Monday, July 5, 2010 3:02 AM by erikej

Suggest you ask any questions in the SQL Server Compact MSDN forum here: social.msdn.microsoft.com/.../threads

# re: New Embedded Database Support with ASP.NET

Monday, July 5, 2010 3:32 AM by Mike Borozdin

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.

# re: New Embedded Database Support with ASP.NET

Monday, July 5, 2010 4:47 AM by jakubmal

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?

# re: New Embedded Database Support with ASP.NET

Monday, July 5, 2010 10:40 PM by orangefield

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.

# re: New Embedded Database Support with ASP.NET

Tuesday, July 6, 2010 10:18 AM by Mike Miller

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

# re: New Embedded Database Support with ASP.NET

Tuesday, July 6, 2010 5:33 PM by Simon

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! ;)

# re: New Embedded Database Support with ASP.NET

Tuesday, July 6, 2010 6:39 PM by Brian Pope

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.

# re: New Embedded Database Support with ASP.NET

Tuesday, July 6, 2010 7:34 PM by George

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)

# re: New Embedded Database Support with ASP.NET

Wednesday, July 7, 2010 2:53 AM by Beetle Labs

Great work scott!!!! Thx a lot.

# re: New Embedded Database Support with ASP.NET

Wednesday, July 7, 2010 4:36 AM by Chris

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

# re: New Embedded Database Support with ASP.NET

Wednesday, July 7, 2010 5:32 AM by culme

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!

# re: New Embedded Database Support with ASP.NET

Wednesday, July 7, 2010 4:04 PM by Cliff

"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

# re: New Embedded Database Support with ASP.NET

Wednesday, July 7, 2010 6:13 PM by InteXX

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  ;-)

# re: New Embedded Database Support with ASP.NET

Thursday, July 8, 2010 8:00 AM by Koen Bekkenutte

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?

# re: New Embedded Database Support with ASP.NET

Friday, July 9, 2010 12:35 AM by SqlCeQues

Will 4.0 Sql Ce support encrypting the database with AES256?

# re: New Embedded Database Support with ASP.NET

Friday, July 9, 2010 3:46 AM by RolfG

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.

# re: New Embedded Database Support with ASP.NET

Friday, July 9, 2010 10:46 AM by PhantomTypist

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!

# re: New Embedded Database Support with ASP.NET

Saturday, July 10, 2010 2:52 PM by SqlCeQues

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.

# re: New Embedded Database Support with ASP.NET

Wednesday, July 14, 2010 11:43 AM by setiri

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.

# re: New Embedded Database Support with ASP.NET

Wednesday, July 14, 2010 2:27 PM by Bret Ferrier(runxc1)

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

# re: New Embedded Database Support with ASP.NET

Thursday, July 15, 2010 12:46 AM by Uwe

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

# re: New Embedded Database Support with ASP.NET

Monday, July 19, 2010 9:24 PM by deko

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?

# re: New Embedded Database Support with ASP.NET

Monday, July 19, 2010 11:18 PM by svsaripalli

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

# re: New Embedded Database Support with ASP.NET

Wednesday, July 21, 2010 10:12 AM by robin

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

# re: New Embedded Database Support with ASP.NET

Monday, July 26, 2010 10:59 AM by Dallas Web Design Guy

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.

# re: New Embedded Database Support with ASP.NET

Monday, July 26, 2010 11:11 AM by EdSF

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

# re: New Embedded Database Support with ASP.NET

Monday, July 26, 2010 1:35 PM by webguy

The beta is already available?

# re: New Embedded Database Support with ASP.NET

Wednesday, August 4, 2010 1:07 PM by dwmurphy

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

# re: New Embedded Database Support with ASP.NET

Monday, August 9, 2010 5:52 AM by liming

why my ide does not have it?

i have installed vs2010 web developer for study

# re: New Embedded Database Support with ASP.NET

Monday, August 9, 2010 9:40 AM by XC

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.

# re: New Embedded Database Support with ASP.NET

Wednesday, August 11, 2010 2:11 PM by erikej

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

# re: New Embedded Database Support with ASP.NET

Thursday, August 12, 2010 9:12 PM by Garry Clark

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.