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.