SQL Server CE in a multi user scenario - is this really, really stupid?
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
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