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
Comments
Anonymous
November 27, 2006
PingBack from http://www.julian-kuiters.id.au/article.php/reading-2006-11-28Anonymous
November 29, 2006
The comment has been removedAnonymous
November 29, 2006
The comment has been removedAnonymous
December 06, 2006
The comment has been removedAnonymous
December 09, 2006
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) Cheers, --rjAnonymous
January 12, 2007
The SQLce 3.1 Web release (RTM bits) is available at: http://www.microsoft.com/downloads/details.aspx?FamilyID=85e0c3ce-3fa1-453a-8ce9-af6ca20946c3&DisplayLang=en#QuickInfoContainer --rjAnonymous
January 15, 2007
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 ;-), -NateAnonymous
January 16, 2007
The comment has been removedAnonymous
January 16, 2007
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. SteveAnonymous
February 05, 2007
在我写了《在SQL Server Express版和Compact版之间做出选择》之后，园子里有些朋友问到SQL Server精简版(sqlce)支不支持ASP.NET？当时我也不确定，我自己测试过确实不行，但是觉得怪怪的，没有道理不支持啊！今天看了Steve Lasker的blog，终于搞清楚了！Anonymous
April 19, 2007
The settings was change to SQLServerCompactEditionUnderWebHosting in RTM. http://www.vidmar.net/weblog/archive/2007/04/19/SQL-Server-2005-Compact-Edition-and-ASP.NET.aspxAnonymous
June 29, 2007
SQL Server CE - Great for prototyping and testingAnonymous
November 25, 2008
Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"TableAnonymous
March 01, 2011
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.