Limitations of SQL Server Express

sql-server sql-server-2008-express

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?

Is there a limitation on the number of connections, anyone?

@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: blogs.msdn.com/b/euanga/archive/2006/03/09/545576.aspx

theyetiman

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

Hi, As mentioned above, I'm ok with those limitations - I'm only worried about the actual no. of databases - any idea about this?

@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: stackoverflow.com/questions/955926/sql-server-express-4gb-limit

This answer could be updated, SQL Server 2008 R2 allows up to 10GB per database.

@RudolfDvoracek According to 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."

The CPU and RAM limits are per instance while Express supports 50 instances per server.

Community

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?

Remus Rusanu

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.

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?

Each individual database must be under 4GB.

Good idea, as with separate instances, each instance should be able to use its own 1 GByte of memory and 1 CPU.

If two user instances are connected to the same .mdf file, will that not cause problems?

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?

Jason Cumberland

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.

opensas

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

Community

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.

Limitations of SQL Server Express

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US