SQL Express 2008 - what's a reasonable aplication size for it's 'limitations'?

Question

Without wanting to sound too stupid, SQL Express is free and the free resources make it a good choice for a beginner like me. I know it has limitations (read other posts) but the figures mean little to me I'm afraid.

• Can anyone give me ball park figures on the number of users it can handle? I'm looking at possibly 40-50 users maximum, carrying out CRUD operations to a simple 8 table database.

I imagine I'll be querying my DB a lot so my users can generate reports, but again I wouldn't think (hope) they are to complicated or demanding - simple select and filter on user name queries. Obviously I'll run these past StackOverflow to see if they can be written more effectively (and to see what an effective query looks like).

Your answers are always appreciated.

Mike.

Answer 1

It should handle the load just fine. 40-50 users is nothing.

Where I'd be most worried is just how much data those users can generate. Express Edition does have a relatively small size restriction, and so if your application is using the database to handle something like document storage you could be in trouble, and might want to consider moving those documents to the file system (or even a separate instance).

Additionally, one if the nice things about express edition is that it is the same underlying engine as the for-pay editions. If you find you grow beyond what express can handle, the upgrade is relatively painless (considering that if you grow that much you've hopefully grown revenue to match and can now afford the for-pay edition).

Answer 2

From your question, it sounds as though SQL Server Express would be fine for your needs. The real, practical limitations of SQL Server Express are in it's maximum database size (4gb per database), it's maximum size of usable memory (1GB IIRC) and it's inability to scale to use multiple processors for improved processing speed and power.

As far as handling 40-50 users, there is really no (practical) difference in using SQL Server Express than a "full-blown" version of SQL Server, for the size of your application (from what you imply in your question).

So long as you use good coding practice when creating SQL Server connections (i.e. opening your connection, performing a query, and closing the connection again ASAP) as well as taking advantage of the built-in connection pooling (which will get used automatically if you use the same connection string each time you connect) you should be fine.

The biggest issue you're likely to run into is hitting the data limit (4GB per db) rather than any performance issues, but this obviously depends heavily on the nature of your application and the quantity of data your users will generate.

Answer 3

See: http://sqlblogcasts.com/blogs/ssqanet/archive/2008/03/28/sql-server-2008-express-features-and-variation-from-previous-version.aspx

Answer 4

I believe the limitations are that it doesnt use multiple processors and that it has ram limitations - only 1 gig.

there is no SQL Server Agent and the max database size is 4gig.

there is also no sql profiler - which will make work harder when you want to figure out why your db is getting thrashed.

I have no idea how anyone can say 30-40 users will be fine without looking at the data and queries alongside the application.

the big way that a lot of people speed sql up is to give it enough ram so that it never has to write to disk - this will probably be your bottleneck in the end.

All you need is one bad query and you can bring any database to its knees.

http://en.wikipedia.org/wiki/SQL_Server_Express

Answer 5

Thanks for all the comments. I've got a better understanding of what things I need to consider to make sure that the free version is enough - good coding practices, efficient queries and closing connections down after use.

I believe my users will it hit moderately, and they shouldn't generate much data as I'm going to limit what we capture to the basics. Later on should I develop my solution I'll mention the fact of limitations to my bosses and it'll be their decision whether to pay or keep nit limited.

Apologies for the limited information in the question, and the 'answering my own question' option but rather than reply to you all I thought this was most effective.

Answer 6

u can use free sql express profiler from AnjLab to profile sql express