Limitations of SQL Server Express

Question

My hosting provider (Rackspace) is offering a fully managed dedicated server with SQL Server Web version () installed. My company handles web development, and has about 20+ clients using ASP.Net + SQL Server 2005.

I am thinking of cutting down costs by installing the free SQL Server 2008 Express instead. I am aware of the 1GB RAM and 4GB/database (is that correct?) limitations. What I would like to know is:

Is there any limit to the NUMBER of databases I can install with the express edition?
Are there any other limitations I should be wary of? I am a bit concerned about having to set up the database backup - with the express edition it might a lot more difficult.
Any other advise?

@codeulike: no, there was never a limit on the number of connections in MSDE/SQL Express, and the "Workload Governor" that limited the number of ACTIVE connections to 5 in MSDE has been removed in SQL Express: http://blogs.msdn.com/b/euanga/archive/2006/03/09/545576.aspx — Tao, Aug 05 '11 at 11:47

score 148 · Accepted Answer · edited Jun 27 '14 at 10:46

148

There are a number of limitations, notably:

Constrained to a single CPU (in 2012, this limitation has been changed to "The lesser of one socket or four cores", so multi-threading is possible)
1GB RAM (Same in 2008/2012)
4GB database size (raised to 10GB in SQL 2008 R2 and SQL 2012) per database

http://www.dotnetspider.com/tutorials/SqlServer-Tutorial-158.aspx http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

With regards to the number of databases, this MSDN article says there's no limit:

The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server.

However, as mentioned in the comments and above, the database size limit was raised to 10GB in 2008 R2 and 2012. Also, this 10GB limit only applies to relational data, and Filestream data does not count towards this limit (http://msdn.microsoft.com/en-us/library/bb895334.aspx).

edited Jun 27 '14 at 10:46

theyetiman

8,514
2
32
41

answered Jul 23 '09 at 04:41

SqlRyan

33,116
33
114
199

Hi, As mentioned above, I'm ok with those limitations - I'm only worried about the actual no. of databases - any idea about this? – Jul 23 '09 at 04:48
There are none, but more database means more data and more data means more chances of getting past the 4Gig limitations. – David Brunelle Dec 01 '09 at 22:23
4

@David Brunelle: The 4 GB limitation is *per* database, as far as I understand it. In the MSDN article it says: "Databases have a 4 GB maximum size". See also this question: http://stackoverflow.com/questions/955926/sql-server-express-4gb-limit – Sub-Star Nov 09 '10 at 10:16
Oh. > 4GB databases are not that uncommon though. – David Brunelle Nov 09 '10 at 13:14
6

This answer could be updated, SQL Server 2008 R2 allows up to 10GB per database. – Tao Aug 05 '11 at 11:43
@Tao - I've updated it to account for the new, increased database size limit – SqlRyan Aug 16 '11 at 07:39
@jcolebrand Thanks for making the correction - that's what I meant. – SqlRyan Dec 03 '12 at 16:24
Is 10GB size limitation addressed to both relational and filestream data? – Rudolf Dvoracek Dec 27 '12 at 23:07
3

@RudolfDvoracek According to http://msdn.microsoft.com/en-us/library/bb895334.aspx, Filestream doesn't count towards that limit: "SQL Server Express supports FILESTREAM. The 10-GB database size limit does not include the FILESTREAM data container." – SqlRyan Dec 28 '12 at 02:23
@SqlRyan Respect for maintaining the answer and keeping it current. :) – Apr 07 '13 at 16:48
1

The CPU and RAM limits are per instance while Express supports 50 instances per server. – Hans Malherbe Aug 01 '13 at 10:08
If I were to swap a mssql with sqlexpress, would my db administrator have much learning to do ? – guiomie Oct 12 '13 at 15:45
@guiomie None at all - the server is the same (except with some limitations and missing some features), and is managed/interacted with in the exact same way. Really, the users would never be aware of the edition change. That said, in addition to the limitations above, it's missing things like the SQL Agent (scheduled jobs, backups, etc.), replication, and many other features. Also, you can't do an in-place change, you have to uninstall your existing service and re-install SQL Express. While it would technically work, I wouldn't recommend it because of the functionality you lose as a result. – SqlRyan Oct 14 '13 at 22:42
i use filestream (as a trick to bypass db size constraint) and i have users who perform succesfully with the express edition (2008r2) and have a backup of 50GB (98% made by FILESTREAM blobs). – UnDiUdin Apr 16 '14 at 13:07
What about amount of processors on virtual server? – Santiago May 13 '14 at 13:51
@Santiago I checked the license guide and I don't see any specific mention of virtualization rights on Express Edition. I'd presume that, as with physical boxes, actual CPU count is irrelevant, but that SQL will only bind to four virtual cores. I haven't confirmed this, though. – SqlRyan May 13 '14 at 16:31
Is it possible to have multiple File Groups within a database in Express in the same way as in Standard Edition? – Caltor Mar 22 '17 at 10:48

score 15 · Answer 2 · edited May 23 '17 at 12:26

Another limitation to consider is that SQL Server Express editions go into an idle mode after a period of disuse.

Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances:

When SQL Express is idle it aggressively trims back the working memory set by writing the cached data back to disk and releasing the memory.

But this is easily worked around: Is there a way to stop SQL Express 2008 from Idling?

score 13 · Answer 3 · answered Jul 23 '09 at 04:51

13

You can create user instances and have each app talk to its very own SQL Express.

There is no limit on the number of databases.

answered Jul 23 '09 at 04:51

Remus Rusanu

288,378
40
442
569

As in we can create 100 databases as long as the total size does not exceed 4 GB, or we can create 100 databases, each up to 4 GB? – PRINCESS FLUFF Sep 01 '09 at 14:41
7

Each individual database must be under 4GB. – Remus Rusanu Sep 01 '09 at 16:23
Good idea, as with separate instances, each instance should be able to use its own 1 GByte of memory and 1 CPU. – treaschf Jul 27 '10 at 10:01
If two user instances are connected to the same .mdf file, will that not cause problems? – ingredient_15939 Dec 18 '12 at 08:27
Will sql express allow for multiple instances to be installed? Will it not be hit by the resource limits? Ie all 5 sql instances together will still only be allowed to use 1gb ram? – Zapnologica Apr 14 '15 at 13:49

score 4 · Answer 4 · edited May 23 '17 at 12:02

4

You can't install Integration Services with it. Express does not support Integration Services. So if you want build say SSIS-packages you'll need at least Standard Edition.

See more here.

edited May 23 '17 at 12:02

Community

1
1

answered Mar 20 '14 at 10:15

niklasolsn

303
5
10

score 4 · Answer 5 · answered Nov 04 '10 at 18:51

4

If you switch from Web to Express you will no longer be able to use the SQL Server Agent service so you need to set up a different scheduler for maintenance and backups.

answered Nov 04 '10 at 18:51

Jason Cumberland

992
7
11

score 4 · Answer 6 · answered Sep 26 '11 at 14:10

4

It seems like the database size limitation has been increased to 10GB.. good new

http://blogs.msdn.com/b/sqlexpress/archive/2010/04/21/database-size-limit-increased-to-10gb-in-sql-server-2008-r2-express.aspx

answered Sep 26 '11 at 14:10

opensas

60,462
79
252
386

Limitations of SQL Server Express

6 Answers6

Linked

Related