Thursday, October 27, 2011

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:\Windows\Microsoft.NET\Framework\v4.0.30319\Config or C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config, 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”.

Tuesday, October 11, 2011

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.