LuckyLindy - I'd encourage you to stop for just a second and verify that you don't need the SQL Agent. You wrote:
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.
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.
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?)
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.