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.

Sean Moubry
  • 293
  • 2
  • 3
  • 9

5 Answers5

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

Moose
  • 1,641
  • 1
  • 9
  • 7
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

JGurtz
  • 523
  • 5
  • 13
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
user215805
  • 121
  • 3
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.

djangofan
  • 4,182
  • 10
  • 46
  • 59
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.

DanBig
  • 11,423
  • 1
  • 29
  • 53