SQL Server Express for ASP.NET Intranet?

Question

We are looking at creating a custom ASP.NET application for a client, however they are a nonprofit and thus budget is limited.

We typically develop ASP.NET web and desktop apps to connect to a central SQl Server 200X database, ie with a full version of SQL Server, running on networked Windows Server. In this case we won't have a full version available.

Are there any issues with using SQL Server Express in this sort of arrangement? IIS and SQL Server Express would be running on the same physical server, serving up pages over the local Intranet to users.

Any real differences to be aware of in regards to development of the app itself or deployment? This will be a fairly standard app, with SQL mainly being used for a datastore with tables and SPs, nothing really SQL Server specific beyond that.

Answer 1

You should not really run into anything, its actually a full featured product that MS SQL Express

Answer 2

SQL Server Express edition should be fine for this scenario. It has all the core features of the full product, but as you said you are only really using it for data storage and some SPs, then you will not need any of the additional functionality available in the other versions (ie. reporting and analysis services). There are some limitations to the express version (the biggest being that the maximum database size is 4GB), but they should not really affect you unless your are building a very busy ASP.Net application.

Some public-facing websites use SQL Server Express as the database server (DotNetKicks being the only one I can remember at the moment) without issue.

Answer 3

Here's a really basic comparison from Microsoft.

Answer 4

The exact list of unsuported features in Express is at SQL Server Express Features:

• Database mirroring
• SQL Mail
• Online restore
• Fail-over clustering
• Database snapshot
• Distributed partitioned views
• Parallel index operations
• VIA protocol support
• Mirrored media sets
• Log shipping
• Partitioning
• Parallel DBCC
• Address Windowing Extensions (AWE)
• Parallel Create Index
• Hot-add memory
• Enhanced Read Ahead and Scan
• Native HTTP SOAP access
• Indexed views (materialized views)
• SQL Mail and Database Mail
• Partitioned views
• Online Index Operations
• SQL Server Agent and SQL Server Agent Service
• SSIS, SSAS, OLAP/Data Mining

The SQL Server Express with Advanced Services Features supports a "subset of Reporting Services features".

In addtion there are the operational restrictions:

• Express will use onyl one CPU core
• Express will not grow the buffer pool over 1 GB no matter how much RAM you have
• Express will not allow any database to grow over 4GB and will not put online (restore, attach) databases that are already over 4 GB.

The key problems you may run into are the operational restrictions (one core, 1 GB ram, 4GB each database) and the lack of SQL Agent, preventing any sort of job scheduling.