SQL Server Compact Edition under ASP.net and IIS

We routinely get requests, complaints, or sometimes even threats, (If you don’t enable it, I’ll use product X), regarding usage of SQLce and ASP.net.  Here’s a little background on the positioning.

Technology, or specifically software, is one of those things that can have wide range of usage scenarios.  Often developers pick up a gadget and start using it, and only after they get really far down the path do they realize their using something for an unintended scenario.  Interesting, but not built into the design, so things start to get weird, and then the cool technology becomes “a buggy problem”.  This is where the frustration begins.  The developer is unhappy they’ve been “lead down” a dead end path, and the software vendor, in this case Microsoft, is frustrated the appropriate assistance can’t be provided, as the developer is using the product in an unintended scenario that may be fraught with problems. 

As developers we all recognize scope.  I recently received this quote from a developer reading my blog: “The client is under attack from AJAX enabled web apps….”  We recognize the limitations of the browser, and are working hard at enabling the client platform, including access from within the browser.  For SQLce, our scope is around enabling the client platform to enable end users to work anytime, anyplace, regardless of whether the “network” was actually available.  This includes a local store, and service oriented synchronization framework.

As a separate, but also important issue, we recognize hosting databases at web site hosters is still a huge problem.  We don’t consider these problems to be the same issue, although SQLce does provide for some interesting solutions for both.  Touching on the scope of the hosting problem is broader than I’d like to get into in this post.  Dan, from the SQL Server team has posted info on the Hosting Toolkit SQL Server http://blogs.msdn.com/sqlserver/archive/2006/10/16/sql-server-hosting-toolkit-launched.aspx

For many of you, this may sound like the Jet comparison, and it is.  Developers started with Jet, and on their box, everything was fine.  They ran their app, all the commands executed, but they didn’t really test with different concurrent users.  They were able to easily post the database to their hosting site, and all seemed good.  Only after their app went into production, did they realize they were using a product that just wasn’t intended for that type of usage.  Sure, Jet is fine for small sites, but did all developers know that when they started?  When they do hit the limitation, what do they do?  Re-write or “upgrade”. 

In Visual Studio 2005 and SQL Server 2005, we attempted to solve this problem by swinging the other direction and assume everyone would use SQL Server Express for their local client and entry level server databases.  As they hit the limits of the free database, they could easily upgrade to the upper SKU’s and gain additional features and scalability.  Again, we gave people what they asked for, but not what they really wanted.  SQL Server Express just doesn’t fit the local storage scenarios well.  Based on the feedback, the developers that appreciate SQLce most are those that have deployed SQL Server Express as their local store.  One size just doesn’t fit all.  This is why we’ve taken the divide and conquer approach.  SQL Server Compact Edition as the default local database and SQL Server Express Edition as the entry point for Data Services.  There’s appropriate overlap, but clear positioning on when to use each.  We recently published a white paper on comparing Express and Compact Edition here: http://blogs.msdn.com/stevelasker/archive/2006/11/08/comparing-sql-server-express-and-compact-editions-whitepaper.aspx

In line with this problem, we continue to hear “provide prescriptive guidance, but don’t lock us out”.  This is the approach we took with SQL Server Compact Edition.  By default, SQLce blocks when the connection is opened under the ASP.net working process.  You can unblock this behavior by simply adding this one line of code. 
AppDomain.CurrentDomain.SetData(“SQLServerCompactEditionUnderWebHosting”, true)

By setting this value in the AppDomain property bag, the blocking code will bypassed.  The supported, intended scenario for this is for developers that need to pre-build .sdf files for clients.  However, developers that are building small scale websites that understand the limitations of SQLce under ASP.net could also use this code.  However, as noted, this is not the currently intended purpose or focus for SQLce.  Our first priority is nailing the client scenarios.  In future releases we are exploring using SQLce for mid-tier caching and will be dealing with many of the issues that arise from multi-user concurrent access.  However, we’re just not there yet. 

Hope that helps with a little insight as to the scope the team managed to, and the method to communicate that intention to developers so you don’t have to read tons of docs just to get started.


Comments (14)

  1. Smartymobile says:

    Steve Lasker ha escrito un buen artículo sobre como usar SQL CE en ASP.NET . Joel Francia hace unas semanas

  2. CesarFong says:

    Steve Lasker ha escrito un buen artículo sobre como usar SQL CE en ASP.NET . Joel Francia hace unas semanas

  3. Ed says:

    I for one agree with the original intent of SQL Everywhere/Compact. It’s an extremely useful tool and one that does not require so much from the client side.

    I sincerely hope (and I’m confident) that your team will not be distracted by requests that ultimately harm the original intent, and therefore the product.

    It’s refereshing that you and your team do listen and do give workarounds (aka hacks), and hopefully they remain as such, hacks.

    SQL Compact, just by it’s moniker, should remain as such. No services, in proc. The Microsoft stack already provides the appropriate tools for alternative "server centric" use (ie. ASP.Net) as you have noted.

    Thanks for your great work on this! We’re close to completing our SQL Compact – hope to send you some "in the trenches" production use feedback soon!

    Happy holidays!

  4. Steve,

    Slightly off-topic, but do you intend to post some of the code you wrote in your December 4 Webcast, "Live From Redmond: Visual Studio: Developing Local and Mobile Data Solutions with SQL Server Compact Edition?"

    I think viewers would appreciate it.

    (See http://oakleafblog.blogspot.com/2006/11/sql-server-2005-compact-edition-rc1.html)



  5. Nathan Allan says:

    To be frank, comments like "In future releases we are exploring using SQLce for mid-tier caching…" make me nervous.  I’ve also seen references to the possibility that support for things like Stored Procedures may be added in the future.  SQLce’s single best feature is its compactness.  Why add features that the higher end products already have, and bloat a useful product into mediocracy?!  As a user of SQLce, my plea is this: please focus on enhancements to the core, like improving the collation support and optimizing the engine.

    Thanks for your ear (eyes ;-),


  6. Steve Lasker says:

    Hi Roger,

    Most of the sample code I’ve shown is either simple to reproduce, like the SqlCeResultset samples, or not something that can be actually done without the Sync Services runtime.  

    Now that SQLce 3.1 has shipped, and we’re about to ship the public CTP of the Sync Services, it will be a little more realistic to post samples.


  7. Steve Lasker says:

    Hi Nathan,

    We will be expanding the reach of SQLce, but it will be with a focus in each area.  We’re still working out exactly how to achieve the specific goals.  We will likely take advantage of the packaging model of SQLce.  Meaning, the engine was designed to be modular.  In some scenarios you may only want the storage engine, but not Merge Replication.  Or if you’re using the new Sync Services features, and don’t need Merge, you can consume a subset of the dll’s.  For the caching scenarios, we may utilize SQLce under the covers, but not directly expose the engine.  Not trying to set off any alarms here on black boxing things, just giving a view into how we might expand the features, yet still have focused scenarios.

    As for sprocs, while it comes up regularly, we still do not plan to add them to the product.  As outlined in the Comparing Express and SQLce whitepaper, and Anil’s multiple comments on the subject, it just doesn’t make sense to add them in our current and future scenario plans.


  8. Bob Li says:

    在我写了《在SQL Server Express版和Compact版之间做出选择》之后,园子里有些朋友问到SQL Server精简版(sqlce)支不支持ASP.NET?当时我也不确定,我自己测试过确实不行,但是觉得怪怪的,没有道理不支持啊!今天看了Steve Lasker的blog,终于搞清楚了!

  9. joeyDotNet says:

    SQL Server CE – Great for prototyping and testing

  10. Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table

  11. D says:

    It only took five years for the proper vision to be finalized in allowing SQL Compact to run ASP.NET websites.  That's progress I guess.  Too bad SQLite owns the space.