SQL Server Compact 4.0 under ASP.NET Hosting– common issues

This blog post covers some of the issues that can be encountered when using SQL Server Compact under ASP.NET, in particular in a hosted environment, where the environment can be restricted in unexpected ways.

In order to be able to troubleshoot issues with ASP.NET hosting of web sites using SQL Server Compact 4.0, it can be useful to understand how Medium Trust works.

Medium Trust only works under .NET 4.0 (ASP.NET 4.0), as it depends on a configuration in the global web.config.

The global web.config is located at C:WindowsMicrosoft.NETFrameworkv4.0.30319Config or C:WindowsMicrosoft.NETFramework64v4.0.30319Config, and contains the following setting, that was configured when .NET 4.0 was installed:

<?xml version="1.0" encoding="utf-8"?>
<
configuration>
<
location allowOverride="true">
<
system.web>
<
fullTrustAssemblies>
<
add
assemblyName="System.Data.SqlServerCe"
version="4.0.0.0"
publicKey="0024000004800000940000000602000000240000525341310004000001000100272736ad6e5f9586bac2d531eabc3acc666c2f8ec879fa94f8f7b0327d2ff2ed523448f83c3d5c5dd2dfc7bc99c5286b2c125117bf5cbe242b9d41750732b2bdffe649c6efb8e5526d526fdd130095ecdb7bf210809c6cdad8824faa9ac0310ac3cba2aa0523567b2dfa7fe250b30facbd62d4ec99b94ac47c7d3b28f1f6e4c8"
/>
</
fullTrustAssemblies>
<
partialTrustVisibleAssemblies />
</
system.web>
</
location>


So this was added during installation of .NET 4.0, long before SQL Server Compact 4.0 was released in January 2011. The fullTrustAssemblies element is new in .NET 4.0: http://weblogs.asp.net/asptest/archive/2010/04/23/what-is-new-in-asp-net-4-0-code-access-security.aspx

If this section has been removed from the global web.config file, SQL Server Compact 4.0 will not run under medium trust.

It is possible to configure similar functionality under .NET 3.5 SP1, as described here: http://msdn.microsoft.com/en-us/library/ms174612(v=SQL.110).aspx

Notice that the SQL Server Compact binaries are delivered with two Assembly version numbers, 4.0.0.0 and 4.0.0.1. The 4.0.0.1 Assembly is for Private deployment, but on desktop only, as described here: http://msdn.microsoft.com/en-us/library/gg213826.aspx – it will not work with Medium Trust, as the assembly version registered in the machine.config file is 4.0.0.0, as shown above.

Other issues you may encounter when running ASP.NET and SQL Server Compact 4.0 can be:

– The process identity running your web application must have write access to the App_Data folder or the folder where your database file resides:

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/6c1c8798-85d7-4d8f-9908-e9f7dc1c17e4

– SQL Server Compact database files must be located on a local drive, not shared folders:

http://stackoverflow.com/questions/7791345/sqlce-4-ef4-1-internal-error-cannot-open-the-shared-memory-region/7804049#7804049

– If SQL Server Compact is not installed locally, a number of files must be included in the bin folder of your web site:

http://stackoverflow.com/questions/3223359/cant-get-sql-server-compact-3-5-4-to-work-with-asp-net-mvc-2/3223450#3223450

http://msdn.microsoft.com/en-us/library/gg286946.aspx

– If only .NET 4.0 is present on the server, you may be missing the Visual C++ 2008 SP1 Redistributable Package (which is installed only with .NET 3.5 SP1)

I will update this blog post if I notice more “common issues”.

Column-store indexes

Column-store indexes (Code Named Project Apollo)
Normally SQL Server stores all of the data for a particular row together, Column store indexes store each column’s data together.

The important thing to notice is that the columns are stored individually. If your queries are just doing SELECT FirstName, LastName then you don’t need to read the long, drawn-out musical tastes. You read less off disk. This is fantastic for data warehouses, where column store indexes have been all the rage for the last few years.

BUT

It’s not perfect for everything, though: for starters, when you add a column store index to a table, that table instantly becomes read-only.

The Best Thing About the Next Version of SQL Server 2012

AlwaysOn Availability Groups   (Clustering + Mirroring + Replication)

Us data plumbers have a killer new HA and scaling tool: AlwaysOn Availability Groups. It’s so important that some of my clients are already planning their 2011/2012 schedule around the deployment. In the past, we’ve struggled with combinations of clustering, log shipping, database mirroring, and replication to get the right mix of performance and availability. These tools work great, but using them all correctly requires a lot of training and experience. Microsoft heard our complaints about our lack of talent, and AlwaysOn aims to replace most of the functionality of all those different tools.

SQL Server Compact books

A number of SQL Compact (or a least SQL Compact related) books have appeared on the market.
I will attempt to keep this list of books up to date as new material appears.

Building Standalone Data-Driven Applications using SQL Server Compact 4.0 and Visual Studio 2010”
“Microsoft SQL Server Compact 4.0 (SQL Compact onwards) is a free SQL Server embedded database ideal for building standalone desktop, web, and mobile applications that need local data storage.”
 
“Microsoft SQL Server Compact 4.0 Books Online”
Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. Download the documentation to learn about SQL Server Compact 4.0 and to build applications for it.

“La réplication du fusion avec SQL Server Compact: Implémentation and conseils”
“Free eBook in French about implementation and advise regarding Merge Replication – in many ways similar to the Rob Tiffany book.”

“.NET Compact Framework 3.5 Data Driven Applications”
“Develop data-driven mobile applications from the ground up on top of the Oracle Lite and SQL Server Compact”
 
“Microsoft SQL Server Compact 3.5 Service Pack 2 Books Online”
This download contains the Books Online for the SP2 version of SQL Server Compact 3.5.
“SQL Server MVP Deep Dives”
This book with contribution from 53 SQL Server MVPs includes 2 chapters relating to SQL Compact:
Placing SQL Server in your pocket by Christopher Fairbairn and Mobile data strategies by John Baird
http://www.manning.com/nielsen/
 
"Enterprise Data Synchronization with SQL Server 2008 and SQL Server Compact 3.5"
An update of the previous book, equally excellent and hands-on.
Available from Amazon
 
"Microsoft Mobile Development Handbook"
This book covers development using .NET Compact Framework 2.0 in broad terms, and has two chapters relating to SQL Compact:
Chapter 3 describes how to persist data using SQL Compact and how to bind SQL Compact data to UI elements and chapter 7 briefly covers using RDA and merge replication with SQL Compact.
 
"Windows Mobile Data Synchronization with SQL Server 2005 and SQL Server Compact 3.1"
I have already recommended the excellent Merge Replication book by Rob Tiffany here:
Available from amazon

"Microsoft® SQL Server 2005 Compact Edition"
This book covers the product in full, including a chapter on Native development, and several chapters on replication. The main perspective of this this book is a DBA approach, with only a single chapter covering development per se. There are also useful chapters on security and performance tuning.
 
"Hitchhiker’s Guide to Microsoft SQL Server 2005 Compact Edition"
For desktop developers diving into SQL Compact, the e-book by Bill Vaughn is an excellent introduction, which covers many aspects and pitfalls of developing with SQL Compact edition in a desktop application.
 

SQL Server codename “Denali” CTP3

SQL Server codename “Denali” CTP3 has been released to the public after much anticipation. You may ask yourself, “What happened to CTP2?” The answer is simple. It was a private build for some Microsoft partners. By private, I mean it was not released to the general public. The good news is that you did not miss much in CTP2. A lot of areas were incomplete or not working. But that is to be expected as it is a work in progress.

This blog post will serve as a means to gather resources such as links and blog posts regarding SQL Server “Denali” CTP3. Check back soon as I will be adding new resources. If you have a blog post about SQL Server Denali CTP3 please pingback or email me to add it to the list.

Downloads:

SQL Server DENALI CTP3 Demo VHD
 http://www.microsoft.com/download/en/details.aspx?id=27253

A HyperV image of SQL Server Denali CTP3 in action, including fully configured services and integration with SharePoint 2010 and Office 2010
 The following software is configured on the virtual machine:
 •SQL Server “Denali” CTP3
 •SharePoint 2010
 •Office 2010

SQL Server codename “Denali” Community Technology Preview 3 CTP3
 https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

SQL Server code name “Denali” Express Core Community Technology Preview 3 (CTP 3)
 http://www.microsoft.com/download/en/details.aspx?id=26784

SQL Server code name ‘Denali’ Community Technology Preview 3 (CTP 3) Feature Pack
 http://www.microsoft.com/download/en/details.aspx?id=26726

The SQL Server  code name “Denali” CTP 3 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® code name ‘Denali’ CTP 3. It includes the latest versions of  tool and components an add-on providers.

Includes:
 •SQL Servercode name “Denali” Master Data Service Add-in for Excel CTP 3
 •SQL Servercode name “Denali” Semantic Language Statistics CTP 3
 •SQL ServerReport Builder for SQL Servercode name “Denali” CTP 3
 •SQL Servercode name “Denali” PowerPivot for  Excel CTP 3
 •SQL Servercode name “Denali” Reporting Services Add-in for  SharePoin Technologies
 •SQL Servercode name “Denali” Data-Tier Application Framework CTP 3
 •SQL Servercode name “Denali” Transact-SQL Language Service CTP 3
 •SQL Servercode name “Denali” Transact-SQL ScriptDom CTP 3
 •SQL Servercode name “Denali” Transact-SQL Compiler Service CTP 3
 •SQL ServerCompact 4.0
 •SQL ServerCompact 4.0 Books On-line
 •SQL ServerJDBC Driver 4.0 Community Technology 2 (CTP 2)
 •Connector 1.1 for SAP BW for SQL Server code name “Denali” CTP 3
 •System CLR Types for SQL Server code name “Denali” CTP 3
 •SQL Servercode name “Denali” Remote Blob Store CTP 3
 •SQL Servercode name “Denali” Books On-line CTP 3
 •SQL Servercode name “Denali” Upgrade Advisor CTP 3
 •SQL Servercode name “Denali” Native Client CTP 3
 •OLEDB Provider for DB2 v4.0 for SQL Server code name “Denali” CTP 3
 •SQL Servercode name “Denali” Command Line Utilities CTP 3
 •SQL ServerService Broker External Activator for SQL Server code name “Denali” CTP 3
 •Windows PowerShell Extensions for SQL Server code name “Denali” CTP 3
 •SQL Servercode name “Denali” Shared Management Objects CTP 3
 •SQL Servercode name “Denali” ADOMD.NET CTP 3
 •Analysis Services OLE DB Provider for SQL Servercode name “Denali” CTP 3
 •SQL Servercode name “Denali” Analysis Management Objects CTP 3
 •SQL ServerDriver for PHP 2.0
 •SQL ServerMigration Assistant
 1.Microsoft SQL Server Migration Assistant for Access
 2.Microsoft SQL Server Migration Assistant for MySQL
 3.Microsoft SQL Server Migration Assistant for Oracle
 4.Microsoft SQL Server Migration Assistant for Sybase
 5. Microsoft SQL Server Migration Assistant 2008 for Sybase PowerBuilder Applications
 •SQL ServerStreamInsight v1.2

Adventure Works sample databases for SQL Server codename Denali CTP3
 http://msftdbprodsamples.codeplex.com/releases/view/55330

Denali CTP3 Adventure Works Sample Databases Readme
 http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Includes:
 •AdventureWorks2008R2 Data File
 •AdventureWorksDWDenali Data File
 •SSAS Multidimensional Model Projects Denali CTP3
 •SSAS Tabular Model Projects Denali CTP3
 •SSAS AMO2Tabular Denali CTP3

Don’t forget to read the sample databases readme file:
 http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Tutorials

Tutorials for SQL Server “Denali”
 http://msdn.microsoft.com/en-us/library/hh231699(v=sql.110).aspx

Includes:
 •Multidimensional Modeling (Adventure Works Tutorial)
 •Tabular Modeling (Adventure Works Tutorial)
 •Tutorial for Project Crescent