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:
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:
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).