We are about to roll out a dual web/internal transactional application where each client has their own database. Each database is very small - under 50MB each, so we were wondering if it would make sense to use SQL Express 2008 instead of the full SQL Server.
This seems to have the advantages of distributing disk I/O across servers while saving massive $$$ (since small 15K drives and used dual core servers are both inexpensive). If at some point we need too many servers, we can upgrade to SQL Server ... but with dozens of internal users this just seems too expensive right now (particularly since we'd need a failover box).
1GB memory and usage of 4 cores on a single processor doesn't sound too restrictive given our small database sizes. We'll never have more than ~200 concurrent users, and most operations will be more transactional (which seems to favor lots of high speed disks over heavy RAM/CPU, right?)
Am I missing any advantages of SQL Server Standard that could justify the extra $5-20K investment initially?
The other editions of SQL server get you things like the SQL Agent so you can schedule database maintenance and other jobs.
As long as your database can fit withing the limits of the Express edition you'll be just fine.
SQL server likes lots of RAM. The more the better. As the SQL Server can't load data into cache that'll put additional load on the disks. You should look at the Web Edition or Workstation edition of SQL Server. Those editions have higher limits than the Express edition, but cost less than Standard Edition.
If you do start with the Express edition, you can always upgrade later to the Standard Edition after you purchase the license.
A few production issues and workarounds that I've had with the Express edition:
Scheduled Backups
SSIS
Can I run SSIS packages with SQL Server 2008 Express/Web or Workgroup
SSIS with Sql Server 2005 express
Profiling
If you read SQL Server License, you dont need to buy additional license for passive server if its solely used for failover and it doesnt serve queries till your first server fails.
We have used SQL Server Express for quite long, and its good and much better then earlier MSDE, we have more then 200 simulatenous connections, but we only have one database of size 2GB, and everything is smooth. We never had any problems provided we avoid expensive joins and we do good indexing. Now we are using SQL Standard, but till your database size is more then 4GB and your number of users are less then 200-500, you can certainly live with SQL Express.
SQL Server Express uses little less memory footprint ~200MB where else Standard edition uses ~1.5GB, probably because standard edition will do lots of caching. Your queries will be slower in Express in few milliseconds compared to standard edition. Unfortunately Express edition doesnt use multi core cpus (thats limited feature) so it will not be of great help whether you have 2 core or 4 core.
LuckyLindy - I'd encourage you to stop for just a second and verify that you don't need the SQL Agent. You wrote:
What is your plan for backups? You don't have to use the SQL Agent but it sure makes a DBA's life easier. You could write T-SQL/SMO/PowerShell/whatever scripts that do your backups and then execute via sqlcmd or PowerShell using a Scheduled Task.
What is your plan for database maintenance? Over time, those databases will need to be defragged and checked for consistency. Standard Edition has all kinds of goodies to make this e-a-s-y whereas, in Express, you have to work (again with the scripting and scheduled tasks).
How will you be notified of problems on the server? The Agent helps out here with Alerts to notify you when a log is getting full, a disk is filling up, etc.
These are critical SQL Server DBA-type tasks. It's one thing to run Express for an in-house app but once you start telling us that you are hosting these for clients, I get worried :)
Part 2 of this is asking you how many clients you plan on supporting on this - both at launch and after one year? If you say, "100 clients", then 100 50MB databases will not suffice on Express - you just don't have enough memory. Heck - depending on how much delta you have, you might max out at 15 DBs, I don't know.
Transactional operations such as INSERTs are still written to memory so don't expect that you need less memory support. In fact, depending on how many INSERTs you do, you might have larger memory needs than most with that number of users. If you are loading lots of data that people won't really be using then it still occupies memory. You might run into contention issues between "data that users are querying frequently" and "data that users are loading up that no one will query for a while". SQL protects us by preserving the data people are querying more frequently in memory longer but you still will have contention.
At this point, I'm rambling lol. And 200 concurrent users doesn't jibe with me either for Express. Let's say 64k is average connection memory requirement, how many connections will your apps make? Will you use connection pooling?
All in all, my gut feeling from reading your description says, "No - Express Edition just isn't powerful enough." And I hate the Workgroup Edition - think it's a bad deal - so Standard seems right to me.
Have you considered using one of the free DBMS (MySQL, PostreSQL...)? That would alleviate your licensing concerns?
If that is not an option, SQL Server Express seems like a good solution.
It can certainly be used for significant production applications. We have used it at over 1500 healthcare clinics all with separate SQL Server Express instances installed to process millions of transactions each day. You can easily get around the SQL Server Agent disadvantage by using one of the following:
See Michael Otey's excellent presentation (google it) on "Using SQL Server Express in Production".