14

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?

6 Answers 6

17

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.

5
  • +1 can't add anything else to that response Aug 31, 2009 at 5:47
  • I dont think workgroup and web edition license can be used for external hosted business apps.
    – Akash Kava
    Aug 31, 2009 at 8:25
  • 1
    CPU Licenses are available for all the editions (except Express which doesn't need a CPU license) which means you can use it for a public facing web site. Here's the link to the Workgroup CPU License bit.ly/KDLDR. I couldn't find the Web Edition easily, but any VAR should be able to provide it without much looking around.
    – mrdenny
    Aug 31, 2009 at 8:31
  • I also suggest starting with something above Express, and moving from express to any other edition can be difficult sometimes.
    – DanBig
    Aug 31, 2009 at 14:35
  • 3
    SQL Express is the exact same engine as the other editions, just with size limits in place. Moving from Express to another edition is simply a matter of detaching the database from the Express edition and attaching it to another instance which is running another edition.
    – mrdenny
    Jan 5, 2010 at 21:51
15

A few production issues and workarounds that I've had with the Express edition:

Scheduled Backups

SSIS

Profiling

2
  1. 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.

  2. 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.

  3. 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.

3
  • I thought SSE 2008 used multi-cores (up to 4)?
    – Beep beep
    Aug 31, 2009 at 21:52
  • @LuckyLindy Standard will use up to 4 sockets, with an many cores per socket as you have.
    – mrdenny
    Sep 1, 2009 at 2:48
  • "but till your database size is more then 4GB" 4GB is the restriction per one instance. I cannot recall that there is restriction on the number of used instances Aug 26, 2010 at 9:46
2

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.

2
  • 2
    Scott - Great answer. To your point ... SQL Server Express actually has no storage size limit across databases, you just cannot have a single database with more than 4GB of data. As a test I created 10,000 100MB databases (1TB) on a single server, and SQL Server Express ran fine. Also, memory related to each connection is not counted towards the 1GB total. I do agree that managing all these databases might be a bit of a pain ... but since we'll actually have 1000s of databases, we'd likely need scripts anyway (there's no way I want to manually touch a database unless it becomes corrupt).
    – Beep beep
    Sep 3, 2009 at 21:43
  • +1, I agree that it was gr8 answer but I also upvoted it up Aug 26, 2010 at 12:18
1

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.

1
  • 2
    We actually ran performance tests against all the major databases last year - MySQL, PostgreSQL, Firebird, DB2, Oracle, SQL Server, SQL Server Express, and SQL Desktop. We found that DB2, Oracle, and SQL Server (and Express, but not Desktop) blew away MySQL and PostgreSQL on Windows for our transactional operations - by a factor of 2. 1/2 the performance means twice the # of servers. When hosted on Linux, MySQL and PostgreSQL did a little better, but still not on par.
    – Beep beep
    Aug 31, 2009 at 21:48
1

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:

  1. Third party product such as SQLAutomate
  2. Windows Task Scheduler
  3. A Standard or Enterprise Edition of SQL Server with the master server capability installed 1 and 2 are very low cost or free. 3 is expensive unless you already have it available in your environment.

See Michael Otey's excellent presentation (google it) on "Using SQL Server Express in Production".

1
  • It was a 7 years old post actually :) Jun 28, 2017 at 7:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .