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 https://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: https://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)

https://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.aspx
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.

Steve