0

Let's say you have an Amazon EC2 Server. What should use less resources:

200 Websites....

  1. each using an individual SQL Express DB (200 in total, 1 per site)
  2. or all sites using 1 SQL Standard Database?

This question is simply focused on those 2 factors. Nothing else applied.

thanks for your help

  • each database will not exceed 1 GB
Improve this question

5 Answers 5

Reset to default
2

Are these 200 sites being hosted by you for others individual parties? Can you give us some specifics about what your doing? Initially I'd worry less about the resources in this case and more about separation of concerns and administrative overhead.

One thing to think about is that SQL Express databases are capped at 10GB for data size so that might be your determining factor right there. If your single database grows beyond that you're pretty much hosed.

More on SQL Server edition specifics here.

EDIT:
With 200 potentially 1GB databases you'll be better off going with Standard, but I would also consider using individual databases there as well. Express has a 1GB limit on memory while Standard is limited only by the host OS which will give you the option of much much more memory to throw at SQL. As for which uses less memory, it's hard to say not knowing your data architecture. Within that single database would you have 200 copies of each table or keep the data for all 200 sites in one copy of the tables? You'd be looking at differences in index sizes there which can affect memory consumption. SQL will try to load as much of your database into memory as it can and is allowed to by configuration.

JohnThePro also makes good points about the other features available in Standard that Express doesn't support.

Improve this answer
4
  • Hi squillman, I'm aware of the size limit for SQL Express DBs. My question is really basic - not need to include the other factors.
    – aron
    Jan 21, 2011 at 17:49
  • @aron Based on your edit, though, you'll have a single 200 GB database if you go that way. Given the same table schema the data size is not going to change, with the exception of the index sizes.
    – squillman
    Jan 21, 2011 at 18:00
  • my question is: what eats more memory lots of little express db's or a bug sql standard. =
    – aron
    Jan 21, 2011 at 18:21
  • @aron: geez, I'm sorry.... I was totally stuck on the Express thing even after you were banging me on the head with Standard. Please see my edit.
    – squillman
    Jan 21, 2011 at 18:59
1

If you can, go with the Standard edition. Not only are there the size limits on DBs, but there are other factors as well, like transactional snapshotting.

Express is great for testing and developing, but the moment you're live, I recommend the Standard edition.

Check out a more comprehensive feature comparison here.

Improve this answer
2
  • SQL Express hasn't limited the number of concurrent connections since SQL 2000's MSDE.
    – mrdenny
    Jan 21, 2011 at 21:15
  • Edited to reflect the fact I was wrong. :)
    – JohnThePro
    Jan 21, 2011 at 21:32
1

I believe a single database would allow more efficient use of memory than 200 databases. Things like query plans would be per database - so if each site would access their own database using the exact same query, it would end up having to build that plan at least once for each of them. The plan cache is probably also limited, so the multiple database senario would cut down on useful space. For one database with tables that had the info all sites, the query plan would only be generated once, and many more would fit into cache.

If you knew that all site's databases wouldn't grow past 4gb together (or would be willing to update at that point), you could do a single Sql express instance with 1 db.

Improve this answer
0

The SQL Express team has released the following guidance with regard to multi-tenant hosting, see SQL Server Express and Hosting:

  • Shared hosting: SQL Server Express Edition is NOT RECOMMENDED for shared (multi-tenant) hosting as the scale limits and memory handling do not allow it to meet the requirements of such an environment. SQL Server Standard Edition or Enterprise Edition are the best choice for shared hosting.

  • Dedicated hosting: All versions of SQL Server including Express Edition are RECOMMENDED for dedicated (single-tenant) hosting environments. SQL Server Express Edition is a great way to provide a free copy of SQL Server with low cost Windows Server offerings

Your scenario would fall into the shared-hosting case, thus being not recommended for Express.

Improve this answer
0

Are you talking about running 200 instances of SQL Express , each with 1 database vs. 1 instance of SQL Server Standard with 200 databases?

SQL Server Express uses, at its' core, the same engine as the higher level versions of the product. You can run 200 databases with SQL Server Express. Then if you run into performance bottlenecks due to Express technical limits... upgrade to Standard.

Improve this answer

You must log in to answer this question.

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