THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

What does this Express Edition look like, anyhow?

Chances are that you rarely get to see the Express Edition of SQL Server if you mainly work with servers. This blog post is inspired from a forum discussion where some functionality is missing in SQL Server Management Studio (SSMS) and I suspected that the tools were installed as a Basic installation instead of Complete installation. So how do I check that? Difficult if I don’t have such an install to look at. I’m training this week and have a few spare machines to play with so I went ahead and installed a couple of SQL Server 2008 R2 Express Editions.

Installation
When you install SQL Server, you are asked what to install. Depending on your installation media, the install is pre-selected for the edition of your SQL Server (the product key is already entered for you), or one of the free editions is selected. You can select any of the free editions even if you have a pre-pidded installation. The free editions are:

  • Enterprise Evaluation Edition
  • Express Edition
  • Express Edition with Advanced Services

I decided to install the two Express Editions, on two different machines, to see what they look like.

Express Edition
This is pretty much only the database engine. Here is what options the Feature Selection gave for installation:

Express bare install

I selected all three. Setup then suggested the instance name SQLEXPRESS. One option I haven’t seen before was the ability to install support for User Instances. The installation took 6 minutes. Setup created a program group:

  • Microsoft SQL Server 2008 R2
    • Configuration Tools
      • SQL Server Configuration Manager
      • SQL Server Error and Usage Reporting
      • SQL Server Installation Center (64 bit)
    • Import and Export Data (64 bit)

Below services were installed

  • SQL Active Directory Helper Service, Disabled
  • SQL Server (SQLEXPRESS), Automatic
  • SQL Server Agent (SQLEXPRESS), Disabled
  • SQL Server Browser, Disabled
  • SQL Server VSS Writer, Automatic

So, this is a pretty much “bare” SQL Server with close to no tools. Or more to the point, no SSMS.

Express Edition with Advanced Services
This edition includes some bells and whistles, especially SSMS. The feature Selection dialog looks like:

Express bare install

Again, I selected all options. Setup then suggested the instance name SQLEXPRESS. The installation took 20 minutes. Setup created a program group:

  • Microsoft SQL Server 2008 R2
    • Configuration Tools
      • Reporting Services Configuration Manager
      • SQL Server Configuration Manager
      • SQL Server Error and Usage Reporting
      • SQL Server Installation Center (64 bit)
    • Integration Services
      • Data Profile Viewer
      • Execute Package Utility
    • Import and Export Data (32 bit)
    • Import and Export Data (64 bit)
    • SQL Server Business Intelligence Development Studio
    • SQL Server Management Studio

Below services were installed

  • SQL Active Directory Helper Service, Disabled
  • SQL Full-text Filter Daemon Launcher (SQLEXPRESS), Automatic
  • SQL Server (SQLEXPRESS), Automatic
  • SQL Server Agent (SQL Express), Disabled
  • SQL Server Browser, Disabled
  • SQL Server Reporting Services (SQLEXPRESS), Automatic
  • SQL Server VSS Writer, Automatic

So you get some goodies with Advanced Services, what probably attract most is SSMS.

What about SQL Server Agent?
We usually say that Express doesn’t come with Agent, but the Agent service is installed. But disabled and you won't be able to start it even if you enable the service. There’s logic behind this. You might want to upgrade your Express to a higher edition. Thanks to all bits already on the disk, the upgrade essentially just adjusts to your product key, no installation of files is necessary. See Upgrading from Express Edition to Standard Edition
for info on how to upgrade Express to a higher edition.

Express Edition of SSMS?
There used to be such a beast. But nowadays, you instead have below options:

  • Management Tools Basic
  • Management Tools Complete

If you install Express Edition with Advanced Services, your only option is Management Tools Basic. This corresponds to the old SSMS Express. Your SSMS is limited and lacks a lot of functionality. The interesting aspect is that you can also select Management Tools Basic from a “real” SQL Server installation media (Standard Edition, for instance). I doubt anyone wants to do this, but it can happen by mistake. One way to see if your SSMS is Basic is to look for SQL Server Profiler in the Tools menu (thanks Mladen Prajdić). If it isn’t there, you have the Basic installation of SSMS. Read on for another way to check this.

Update 2011-08-06: I just learned that 1) there are some minor differences between Basic from Express install and Basic from non-express install and 2) Basic does have a node (GUI) for Agent, if connected to a non-express instance. I learned it in this thread and you can find out more in this blog post.

How do I find out what is installed?
There’s a great discovery tool built in the setup program, and this is already on your machine:

  • Start menu
  • Microsoft SQL Server 2008 R2
  • Configuration Tools
  • SQL Server Installation Center.

Here you select:

  • Tools
  • Installed SQL Server features discovery report

This will lead you to a HTML page which clearly states what SQL server stuff is installed on your machine. It will tell you the edition of the server components, and it will tell you whether your tools are installed as Basic or also Complete (if you installed the “full” SSMS, you see both Basic and Complete).

Published Thursday, February 10, 2011 7:40 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Tibor, FYI I've tried to install "Basic" Management Tools from a normal install.  I don't think it's possible - if you check "Basic" it auto-checks "Complete" for you as well. Perhaps it is edition-dependent but I've tried a few different times.

February 10, 2011 2:26 PM
 

TiborKaraszi said:

Interestng Aaron. I know setup will selec Complete if you only chose Basic, but I thought you could explicitly uncheck Complete. I have one more clean machine at work, will give it a spin tomorrow.

February 10, 2011 4:02 PM
 

TiborKaraszi said:

I have now tested this. I have a Dev Edition install media. I selected Developer Edition, when I click the "Management Tools Basic", it will also pre-select "Management Tools Complete". However, I can UNselect "Management Toole Complete", which leaves me with only Basic selected.

I then did the installation and verified that I have Basic tools installed (no Agent node, for instance).

So, yes, it is possible to install Basic tools from a full SQL Server, but it is not likely you do it by mistake... :-)

February 11, 2011 6:16 AM
 

AaronBertrand said:

Interesting, I could swear when I've tried that I was never able to un-check "Complete" ... again I wonder if it's version-dependent. Also I wonder why anyone would ever want a subset of the tools at their disposal...

February 11, 2011 9:41 AM
 

Tibor Karaszi said:

I have decided to revisit the topic of whats included in Express Edition, with focus on the tools. I

January 30, 2013 1:38 PM
 

jay said:

sql agent not display in sql server management studio 2008r2

please give me solution

May 19, 2014 6:45 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement