3

What differences exist between the pay and free version of MSSQL2008R2?

Microsoft SQL Server 2008 R2
http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx

and Microsoft SQL Server 2008 R2 Express
http://www.microsoft.com/express/Database/

A complete answer might explain why someone would choose one over the other, what the biggest annoyances are with going Express, non-intuitive things to look out for, etc.

5 Answers 5

5

Executive summary (Commonly missed features for the person running a small database):

SQL 2008 R2 Express edition is lacking:

  • SQL Server Agent (for scheduling jobs, including SQL replication)

  • support for DBs >10GB

  • Integration services

There are many other differences, detailed at the URL Moose posted.

SQL Express CAN be accessed from the network, but it is not enabled by default. A quick google will show how

0
5

This page has a good comparison... The Express version is free, but it's limited in areas.

http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

2

We often see shortened list of differences between Express and Standard. In my opinion, when you are to the point of considering this for a "production" environment deployment, you need to fully be aware of differences. Here's the full detailed list of differences between 2008R2 Standard and 2008R2 Express from msdn:

Scalability and Performance

  • Number of CPUs ( 4 v.s. 1 )
  • Maximum memory utilized ( 64GB v.s. 1GB )
  • Maximum database size ( 524PB v.s. 10 GB )
  • Direct query of index views using NOEXPAND hint
  • Automatic indexed view maintenance

High Availability

  • Log shipping
  • Database mirroring
  • Automatic corruption recovery from mirror
  • Log stream compression
  • Number of Failover clustering nodes ( 2 v.s. 0 )
  • Backup compression

Virtualization Support

  • Guest failover clustering support for virtualization
  • Hyper-V live migration

Replication

  • Publishing data from SQL Server to non SQL Server subscribers

Single Instance RDBMS Management

  • Dedicated admin connection ( yes v.s. under trace flag )
  • Policy automation (check on schedule and change)
  • Maintenance plans
  • Database mail
  • Performance data collector
  • System Center Operations Manager Management Pack
  • Plan Guides
  • User instances ( only for Express )

Application and Multi-Instance Management

  • Able to enroll for multi-instance management.

Management Tools

  • SQL Server Management Studio ( Standard v.s. Express)
  • SQL Server Agent
  • Database Engine Tuning Advisor
  • SQL Server Profiler

Development Tools

  • IntelliSense (Transact-SQL and MDX)
  • Business Intelligence Development Studio
  • SQL query, edit and design tools
  • Version control support
  • MDX edit, debug, and design tools

Programmability

  • Full-text search
  • Specification of language in query
  • Service Broker (messaging) (client only for Express)
  • XML/A support
  • Web services (HTTP/SOAP endpoints)
  • T-SQL endpoints

Spatial and Location Services

  • Spatial results tab within SQL Server Management Studio

Complex Event Processing (StreamInsight)

  • StreamInsight

Integration Services

  • Integration Services designer and runtime
  • Basic tasks and transformations in addition to those used by the Import and Export Wizard
  • Log providers and logging
  • Basic data profiling tools
  • Programmable object model for extensibility

Data Warehouse

  • Auto-generate staging and data warehouse schema

Analysis Services

  • SQL Server Analysis Services backup
  • Dimension, attribute relationship, aggregate, and cube design
  • Translations
  • Personalization extensions

Analysis Services-Advanced Analytic Functions

  • Binary and compressed XML transport

Data Mining

  • Comprehensive set of data mining algorithms
  • Integrated data mining tools: wizards, editors, model viewers, query builder

Reporting

  • Reporting Services memory limits ( 4Gb for Express )
  • Model support
  • Model Item Security
  • Infinite click-through
  • E-mail and file share subscriptions and scheduling
  • Report history, executing snapshots, and caching
  • SharePoint integration
  • Shared component library
  • Remote and non-relational data source support
  • Data source, delivery, and rendering extensibility
  • Report definition customization extension (RDCE)

Business Intelligence Clients

  • Report Builder 3.0
  • Excel 2007 and Visio 2007 Add-in support
0

The most prominant difference is that SQL Express can only run on 1 of your systems processors rather than multi-proc. Personally, in my DEV environments I really can't tell the difference in performance.

1
  • On the subject of hardware limitations, express is also limited to 1GB of RAM.
    – Shane
    Sep 1, 2010 at 19:25
0

A few big things come to mind, you don't have an SQL Agent with express, and being a Publisher for replication isn't possible. (Because of not having an agent) And you are limited to the size of your DB.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .