3

I'm running a SQL EXPRESS 2005 server currently hosting ~50 databases. The databases serve clients' CMS/eCommerce websites. The connections are to a single instance, no user attached instances are being used. Median DB size is 5MB, the largest 20MB. The website are mostly low traffic, the CPU utilization is < 10%, and the SQL process uses at most 350MB RAM.
For now I'm well within the SQL server express limits of 1CPU/1GB RAM. In the upcoming expansion the number of databases may double. If I assume linear growth in requirements, the 1GB limit still wont be reached. But I'm concerned the number (> 100) of databases may become an issue. I'm not sure if this usage scenario is what Microsoft had in mind for express.
Is there any information or preferably real-world experience regarding SQL server express capability to handle lots of small databases? Can I expect it to run 150 databases, or should I start working on migrating to other database servers/file-based databases?

felixg
  • 173
  • 1
  • 2
  • 7

4 Answers4

6

That answer is NOT true. SQL express will use one CPU as per socket. This means ALL cores are used. It clearly states this on the licensing schemes for SQL and on the SQL express page, here: http://support.microsoft.com/default.aspx/kb/914278 Therefore all cores will be used and if the chip uses hyper threading all those will be used also. It is limited to only one PHYSICAL CPU regardless of cores and multithreading.

3

Not real world but...

According to Beginning SQL server 2005 Express database applications By Rick Dobson

"The maximum number of databases per database server is 32767"

The 1GB memory restriction would be my worry. I am sure there is someone out there running express with over 100 db's, hopefully they will read this.

ITGuy24
  • 1,576
  • 1
  • 15
  • 29
2

There isn't a limit to the number of databases, however there is a 4GB per database limit. Although how you're getting by without the SQL Server Agent is beyond me. My guess is that you're going to run into the memory limit before you run into any SQL server limit.

Edited to add

The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, and so on is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1 GB in total, but it will never use more than 1 GB for the buffer pool. Address Windowing Extensions (AWE) or 3 GB data access is not supported or needed.

From here.

GregD
  • 8,713
  • 1
  • 23
  • 35
1

According to the SQL Server 2005 Express edition overview:

there are no limits to the number of databases that can be attached to the server.

So, the limit is how much performance you can utilise of the server. Consider that as the express edition will only use one CPU core, if you have a quad core processor it can not use more than 25%.

If you later on find that you need to utilise more of the server's performance, you can quite easily upgrade to a different version of SQL Server.

Guffa
  • 300
  • 1
  • 3
  • interesting, this KB article contradicts the link above, stating that it will use multiple cores of a single physical cpu. "On a computer that has a multiple-core CPU, SQL Server 2005 Express Edition starts a separate scheduler for each core." http://support.microsoft.com/kb/914278 – duckworth Feb 24 '10 at 00:16