Installing MSSQL 2008 Express

For an overview of the issues to do with installing and updating MSSQL in general, see Installing MSSQL Express Versions. This post covers downloading and installing MSSSL 2008 Express.

MSSQL 2008 is currently at R2 Service Pack 2 (SP2).
Just to keep things nice and confusing, R2 is a new release of SQL Server, not quite a new version, but a major upgrade (MSSQL 2005 is version 9.0, MSSQL 2008 is version 10.0, MSSQL 2008 R2 is version 10.5), and the service packs start over for it as a release.

The release history is:

• MSSQL 2008
• MSSQL 2008 SP1
• MSSQL 2008 SP2
• MSSQL 2008 R2
• MSSQL 2008 R2 SP1
• MSSQL 2008 R2 SP2

Use the R2 SP2 files here.

Note that if you are installing extra features like ReportBuilder (there are a whole list), you'll need to go the R2 SP2 Feature Pack downloads page here rather than download from the product specific R2 pages (again, there is no indication on these product pages that they are outdated).

Use the x64 files if you have a 64 bit Operating System, or the x86 files otherwise.
The top two 'SQLEXPR' files are the bare bones server. The last two, starting in 'SQLEXPRWT', are 'with tools' meaning the server plus Management Studio and a few other development tools.  The two starting with 'SQLEXPRADV' are the whole kit and kaboodle - the server, management studio, business intelligence and reporting services tools.
The SQLEXPR32 file is the 32-bit bare bones server only.

We will follow the installation of the client tools and server version.

(1) Prerequisites - required before commencing installation
The installer will provide you with a download link to the appropriate update.
If you won't be on the internet, then you need to download the files and take them with you.

Installer 4.5 here
  There are Windows 6 (Vista), Windows XP and Server 2003 Versions, x86/x64

PowerShell 2.0 here
This download is not clear - the links are about halfway down the page: 'Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0)'
There are Server 2003, Server 2008, Vista , XP, x86/x64 versions

(2) Task Selection
Choose 'New Installation ...' unless you want to upgrade an existing server

(3) Setup files are installed, the welcome screen is shown, acceptance of Licensing Terms

(4) Feature Selection
Unless you are doing development or specifically using replication, you can leave out these options as shown.

For the core server only install, the page looks like this:

(5) Instance Configuration
I usually install as a named instance.
Usually when I upgrade major versions (2005, 2008, 2008 R2) I install the new server as a separate instance, to allow concurrent running of the systems for a time - hence I name the server to identify the version as shown here.
The old server instance can be removed when no longer needed.
However, you may want to keep the server name consistent and generic (eg. 'SQLExpress') so you don't need to reconfigure the server name on any client machines as it is upgraded through various versions.

(6) Server Configuration
The selection of local service or network service is not crucial.

(7) Windows Authentication Mode is regarded as more secure, but it requires a domain controller and proper network administration.  For legacy Applications which use SQL Authentication (username and password) or for small sites which don't have a dedicated server, Mixed Mode is appropriate.  The password being entered is automatically for the 'sa' (System Administrator) SQL user - you don't have to input the 'sa' username anywhere.

The current windows user should automatically be added to the Administrative users list.

(8) Installation Confirmation and Completion

SQL Server Express 2008 is now installed, but by default it can't be accessed from anywhere but the local computer. To enable remote access:

(1) Go to Start > Program Files > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager

(2) Expand 'SQL Server Network Configuration' and click on 'Protocols for ...' your server instance.  Check 'Shared Memory', 'Named Pipes' and 'TCP/IP'.

(3) You can go to 'SQL Server Services' and restart the SQL Server service, but I'd recommend rebooting the machine at this stage instead.

(4) Any other problems connecting from remote machines can be attributed to firewalls.  Check for firewalls on both the client and server (the Server is critical), and if need be the port to open is port 1433.
Note that the slammer worm uses this port, so what we want to do is enable access to the server machine via the port from the local network only; NOT from the internet.
Firewalls can cause some very strange intermittent behaviour - not just outright blocking - so disabling them completely is the first step in troubleshooting, then they can be progressively re-enabled with appropriate settings, checking for correct operation at each step.

Installing MSSQL 2005 Express

For an overview of the issues to do with installing and updating MSSQL in general, see Installing MSSQL Express Versions. This post covers downloading and installing MSSSL 2005 Express.

The MSSQL 2005 release history is:

• MSSQL 2005
• MSSQL 2005 SP1
• MSSQL 2005 SP2
• MSSQL 2005 SP3
• MSSQL 2005 SP4

It is currently at Service Pack 4 (SP4), downloadable here.

The top file is the core server only, the second file is server + client tools + BI/reporting, and the third is server + client tools.
MSSQL 2005 does not have a native 64 bit installation; but it can be run on 64 bit (it uses the Windows-on-Windows-64 emulation subsystem). The fourth file can be used for native 32-bit-only installs (it leaves out 64 bit compatibility and won't run on 64 bit systems at all).

We will follow the installation of the client tools and server version.

(1) Installing Setup files and welcome screen

 (2) System Configuration Check and Registration Information

(3) Selection of Features

Unless you are doing development or specifically using replication, you can leave out these options as shown.

(4) Windows Authentication Mode is regarded as more secure, but it requires a domain controller and proper network administration.  For legacy Applications which use SQL Authentication (username and password) or for small sites which don't have a dedicated server, Mixed Mode is appropriate.  The password being entered is automatically for the 'sa' (System Administrator) SQL user - you don't have to input the 'sa' username anywhere.

(5) Error Reporting - tick these if you feel inclined.

(6) Installation and Finished Screens

SQL Server Express 2005 is now installed, but by default it can't be accessed from anywhere but the local computer. We need to use the Surface Area Configuration Tool.

(1) Start the tool

(2) Choose 'Surface Area Configuration for Services and Connections'

(3) Make the shown changes and click 'Apply'

(4) You will then need to go to 'Database Engine' > 'Service', and click Restart, then go to 'SQL Server Browser' > 'Service' and click Restart.
This should be all that is needed, but experience shows that it is sometimes necessary to restart the machine, so to save time it's probably a good idea to do that rather than bother restarting the services as just mentioned.

(5) Any other problems connecting from remote machines can be attributed to firewalls.  Check for firewalls on both the client and server (the Server is critical), and if need be the port to open is port 1433.
Note that the slammer worm uses this port, so what we want to do is enable access to the server machine via the port from the local network only; NOT from the internet.

Installing MSSQL Express Versions

You would expect installing the Express version of MS SQL Server to be a straightforward task, but in that you'd be wrong.  Firstly, these are the different released versions of SQL Server Express (as of Oct 2011):

• MSSQL 2005
• MSSQL 2005 SP1
• MSSQL 2005 SP2
• MSSQL 2005 SP3
• MSSQL 2005 SP4

• MSSQL 2008
• MSSQL 2008 SP1
• MSSQL 2008 SP2
• MSSQL 2008 R2
• MSSQL 2008 R2 SP1
• MSSQL 2008 R2 SP2

These are all still available for download, with no warnings on the download page that versions are obsolete.

On top of this, there are three or four different bundles you can download, depending on the features you want - in both 32 (x86) and 64 bit (x64) versions.
The names of features change regularly between versions of MSSQL and sometime even between service packs.

What are your chances of getting the correct latest download ? Not good !

This post will provide links to the combinations you want and guide you through the installation process.

The Ins and Outs of MSSQL Updates

MSSQL service packs have the following behaviour

• updating from an older to newer service pack level essentially reinstalls MSSQL from scratch, so you really don't want to install an old version and update it. However, the advantage of this way of doing things is that you don't have to apply the service packs in order - you can skip straight to the latest
• as soon as you attach your database (mdf) files to the new server, they will be updated to the new server's version format and from that point on the files will not work with an earlier service pack or version of SQL
• for this reason you MUST get the correct server release (2000, 2005, 2008) AND SP level (0-4).  In addition, ALWAYS make a copy of important databases (backups or copies of properly detached mdf files) before installing a Service Pack.

MSSQL Features

There are essentially three features of MSSQL.

• the core server
• client/developer tools (MSSQL Management Studio or MSSMS and a command line query tool) -  these are used during development and/or troubleshooting
• Business Intelligentce (BI)/Anaysis Services and Reporting Services

There will be downloads for (a) core only (b) core + developer tools, (c) core + dev tools + BI/reporting

Checklist

So in summary, you need to decide these points before proceeding:

1. which version of SQL Server Express do you want (2005 or 2008) ?
2. are you installing on a 64 bit or 32 bit Operating System ? (note: this is not necessarily the same as the hardware, as you can run a 32 bit OS on a 64 bit processor)
3. what features do you require from the above list ?

The proceed to the post specific to your version