Is SQL Server Express designed for production use ?

Per the subject line :

Is SQL Server Express designed for production use ?

As I know yes you can use it as databases for desktop application and is recomended to use as witness SQL server(because is free) in multiple SQL server environment.

see page below:

http://www.microsoft.com/sql/editions/express/default.mspx

Thanks

JPazgier

|||

Juan T. Llibre:

Per the subject line :

Is SQL Server Express designed for production use ?

Yes it is you can use it if you host your application Webform or Winform, the only thing not included with Express is the Agent so most SQL Server based automation you have to look for alternatives and there are many free solutions from SQL Server users. There is no Profiler but you can use the Developer edition profiler to profile your application. Try the link below for features compare in all editions. Hope this helps.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

Juan T. Llibre:

Per the subject line :

Is SQL Server Express designed for production use ?

For web applications in a shared hosting environment? No, not really.

|||

re:
>For web applications in a shared hosting environment? No, not really.

That's what I thought, but I was told by a 'softie that it is.
I'd like to have a definitive reason for this, whether it is, or not.

|||re:
> Yes it is you can use it if you host your application Webform or Winform,
> the only thing not included with Express is the Agent

To further the question :

Express will only bind to one CPU at a time, and it cannot run queries in parallel.

Express cannot use more than 1 GB of RAM at a time for queries and data pages.
SQL Server 2005 will handle many more concurrent queries than the Express version.

No one database in Express can be larger than 4 GB.

Data mining, Data Transformation Services (DTS)
and reporting functions are not available for SQL Express.

Other functions not available in Express include clustering/mirroring,
full-text indexing/searching, SQLMail, indexed views, partitioned views and SQL Agent.

I.O.W., although it will work fine for small traffic websites,
I would have thought it isn't designed for heavy-duty production websites.

Maybe we need to distinguish between
"light traffic" and "heavy traffic" regarding SQL Express ?

Maybe I just have the wrong impression, and the limitations listed don't amount to much ?|||

(Data mining, Data Transformation Services (DTS)
and reporting functions are not available for SQL Express.)

This is the calculus end it is not practical to expect it in a free edition and I would not run it even with the standard edition.

(Other functions not available in Express include clustering/mirroring,
full-text indexing/searching, SQLMail, indexed views, partitioned views and SQL Agent.)

Views are query rewrite edition is not relevant Partitioned Views are created with the UNION ALL SET operator and I don't think indexed views are affected either. Full text there is work around solution. Agent no and SQL mail is not there but the good SQL Server mail service is the Agent mail so you are not missing anything.

Parallel queries are seldom needed in OLTP(online transaction processing) and if you want all you have listed you need to host it in house with the Enterprise edition running on multi processor remember SQL Server license is per processor.

|||

Thanks, Caddre.

So, the next time I'm asked whether SQL Express is designed for production use, I should answer : yes ?

|||

Hi you are not exactly rights because in SQL Server Express SP 1 you have :

Microsoft SQL Server 2005 Express Edition Toolkit SP1
SQL Server Express Toolkit provides tools and resources to manage Microsoft SQL Server 2005 Express Edition, and to create reports by using SQL Server 2005 Reporting Services. The kit includes Connectivity Components, Business Intelligence Development Studio, Management Studio Express, and a Software Development Kit. To create reports by using Reporting Services, you must use Business Intelligence Development Studio.

Install Microsoft SQL Server 2005 Express Edition with Advanced Services(more...)

In addition to the features of SQL Server Express, certain additional capabilities are also available as part of Microsoft SQL Server 2005 Express Edition with Advanced Services (SQL Server Express). SQL Server Express with Advanced Services contains the following features:
SQL Server Management Studio Express, a graphical management tool based on SQL Server Management Studio that makes it easy to manage and administer SQL Server Express databases.
Reporting Services, an integrated report creation and design environment to create reports.
Full-Text Search, a powerful search engine for searching text-intensive data.

so you can do much more than in previous edition. check this page to see what you can get:

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

The only problem can be that Microsoft peoples say that SQL express is much slower than any other not free version of SQL Server, but based on my experience for small databases is not true, and I could not test for big because SQL express has a lot of size limits.

You can see all limitation of previous SQL Server express on page below, but SP1 remove some of this missing elements from the list:

http://msdn2.microsoft.com/en-us/library/ms165636.aspx

Thanks

JPazgier

|||

Juan T. Llibre:

Thanks, Caddre.

So, the next time I'm asked whether SQL Express is designed for production use, I should answer : yes ?

I am sorry I forgot to answer you yesterday, yes you can use Express to run small production site in house if you buy the Developer edition because you can use the Profiler and the database tuning advisor to improve performance your Express database.