SQL Server CE in a multi user scenario - is this really, really stupid?

Question

I am trying to use SQL Server Compact Edition 3.5 as a database backend for a sales application. There would be 3-4 installations of a Windows Forms application which would all (via LINQ2SQL) read from and write to one .sdf file located on a network share.

My (pretty inextensive) testing has shown that this could be viable. Documentation states that SQL Server CE supports up to 256 connections. Does SQL Server CE even lock its rows? Are there other concurrency issues that I should worry about?

Bottom line is, has anyone used SQL Server CE successfully with multiple applications connecting to same database?

Should I spare myself future headaches and go with SQL Server Express?

Answer 1

MS recommends (from Darian Miller's comparison guide):

When you require data service functionality, such as the ability to support multiple, remote users, you should start with SQL Server Express Edition and work up the data service family tree.

the runtime supports concurrency on the same machine, not sure about multiple machines accessing the file. in the case the file is stored over a network share, that may not fit the optimal performance profile for ce. i would say go with EE, it's a snap to install with a simple msi.

SSCE Concurrency:

SSCE allows multiple connections to the same database (.sdf file) from the same application or even multiple applications on the same computer. This gives you more freedom to structure your application as needed, such as allowing the user to continue to interact with data while performing synchronization with a back-end database, or to have multiple applications on the same machine share an SSCE data store. Transactional concurrency locks are made by the database engine to prevent concurrent connections from accessing the same records at the same time. The technical limit on concurrent connections for a single database is 256, but 70-80 is a better practical limit from a performance perspective.

http://technet.microsoft.com/en-us/library/bb380177%28SQL.90%29.aspx

Answer 2

Micrsoft has published a white paper on this that you should read over and apply to your particular situation.

http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact_Express_Comparison.doc

The network share apparently precludes Express from being an option.

Answer 3

I'd say you will have less headaches with sql ce as long as you are running everything on one machine. Installing the Express edition on every computer that your app is running on is not good, cumbersome and probably overkill.

Answer 4

Having developed client applications, SQL CE has been my choice. While playing with it, I was not able to successfully use the concurrency or transactional nature of SQL CE in a multiple thread scenario, i.e. multiple threads in a single application trying to access the database. I had to use a lock to get this right. My question is : does the concurrency scenario for SQL CE only apply to multiple processes in the same application?

Please advise.

Subbu