Menu
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?
451
Microsoft SQL Server Compact (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. It includes both 32-bit and 64-bit native support. SQL CE targets occasionally connected applications, and applications with an embedded database.
Sometimes, you gotta look at what queries that are currently running are waiting on. For that, go grab sp_WhoIsActive. If you see queries constantly waiting on stuff like this, it might be a sign you need more memory, because you have to keep going out to disk to get what queries need to use.
How much memory does the SQL server use? SQL Server uses about 87.5% of the RAM on my server. Many performance issues have arisen as a result of this, including slowness.
SQL will not be replaced for a long time, as it has a number of key benefits: It is very popular among data professionals. Leading tech companies rely on relational databases and SQL. Professionals working with data are used to SQL; it's quite challenging to re-train the workforce to use another tool.
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
156
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.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
