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:

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:

5 comments:

I'm just about to decide upon a first, SQL Compact 4 app for shared server space. I'm always looking to do new things which have some chance of surviving as a 'version next'. Is there any news confirming that there will be such a thing as SQL Compact 5 as part of 'Denali' in 2012?Thank you in advance.

Rod: SQL Server Compact is no longer part of the SQL Server releases, but tied to Visual Studio only. In VS 11 Preview, version 4.0 is included, I am guessing a service pack for 4.0 could apprear before VS 11 appears.

Hi Sebastian, assembly version 4.0.0.0 and 4.0.0.1 is the same code, but 4.0.0.1 is isolated for desktop deployment and does not Work with medium trust. Version 4.0 and 4.0 SP1 both support medium trust, as long as you use assembly version 4.0.0.0. Please feel free to contact me by mail if you need further info. See my blog post about 4.0 SP1 for a feature "overview".

Thank you so much for outlining the problems associated with SQL server compact 4.0.The usage of virtual memory for each connection of SQL Server Compact has been reduced in SQL Server Compact 4.0. The visible difference is that if an application tries to open 40 to 50 simultaneous connections in SQL Server Compact 3.5 SP2, an ‘out of virtual memory’ exception will be hit. But with SQL Server Compact 4.0, the application can easily open as many connections as needed to the database file, up to the full extent of 256 connections without running out of virtual memory.