I'm planning to use SQL CE 4.0 in a small but production web site and I'd like to know how much load SQL CE 4.0 can handle:
- Number simultaneous connections,
- Number of rows per table, and
- The total DB size.
Answer
Something that might be useful to you is Scott Guthrie's blog post on Wednesday 12 Jan 2011 regarding the latest version of SQL CE and its VS2010 integration: VS 2010 SP1 and SQL CE
To cherry pick a couple of quotes:
- SQL CE can be used for development scenarios, testing scenarios, and light production usage scenarios.
- Starting with SQL CE 4 you can use it in a web-server as well.
- For high-volume sites and applications you’ll probably want to migrate your database to use SQL Server Express, SQL Server or SQL Azure.
Personally I would consider that a 4GB & 256 simultaneous connections in-memory database on a webserver is starting to get a bit big, even though that is the limit for SQL CE, I would look to migrate well before then. It is aimed more at certain scenarios, and if your app doesn't fit that scenario then you should consider SQL Express as Leppie mentioned; i.e. if you can install SQL Express either on your web server or on a machine accessible from your web server then you should do so.
Edit:
just as a follow-up, people should note that you must use VS2012 to work with SQL CE 4.0. SSMS 2008 can be used to work with SQL CE 3.5 (but not 4.0), and SQL CE capability has been removed from SSMS 2012. This means if you are developing with SQL CE 4.0, you must use Visual Studio or a third party tool to do your development work (table creation, etc.).