Asp.Net app using SQL Server CE 4.0 causes sharing exception with multiple clients

Question

I have an ASP.Net 4.0 website which uses SQL Server CE and EF code-first for its data store. When 2 or more users are on the site I intermittently get the following exception about every 5th or 6th time.

There is a file sharing violation. A different process might be using the file. [D:\directory...\file.sdf].

Throughout the code I create, use, and promptly dispose of the DbContext each time one is needed. Even though it is the default, I have added File Mode = Read Write to the connection string.

Any ideas what I might be doing wrong.

Are you 100% sure that all apps accessing the file uses the same connection string? — ErikEJ, Jan 26 '12 at 07:37
Yes I am, the connection string exists only in the web.config file and there is only one connection string there. — John Melville, Jan 26 '12 at 12:59

score 1 · Accepted Answer · answered Jan 26 '12 at 12:57

I think I figure out what happened. I found a link (unfortunately I do not recall where), that mentions that Go Daddy shared hosting uses a load balancer and multiple servers to serve the site. Even though the database file shows up on a drive mounted with a drive letter, clearly it must be located on a file server that all the webservers share.

Using SQL CE by multiple clients over a network is a nonsupported configuration. When I upgraded from SQL Compact to a SQL Server Instance provided by Go Daddy, the problem (and another data consistency problem I did not mention above) went away.

I am a little surprised that I did not find this problem when I googled it. Microsoft is pmarketing SQL Compact as a good back end for small website. If I am correct, this seems to be a significant liability in that regard.

Small websites typically run on a single server! – ErikEJ Jan 26 '12 at 14:33 — ErikEJ, Jan 26 '12 at 14:33

Asp.Net app using SQL Server CE 4.0 causes sharing exception with multiple clients

1 Answers1