Installing SQL Server 2008 R2 Express

In this article I wanted to throught the dialogs you have to complete to try and clarify some of the questions you’re asked.

I am using the basic install of SQL Server Express and the first step is to download it from here. In my example I am installing to a virtual machine running windows 7 and will be using the 32bit (x86) version.

Right Click on the download (SQLEXPR32_x86_ENU.exe) and select run as administrator. You’ll get a standard user account control popup asking to confirm you wish to run the program. The EXE will then unpack itself to a temp directory and then launch the installer ..

 

image

 

 

This screen is common to all editions of SQL Server 2008 R2, and most of it doesn’t apply to Express edition.  It’s the installation option I want so I select installation on the left and then the top option New installation or add features to a new installation.

Now I get the usual license agreement screen..

image

I click on I accept the the license terms and click next. This starts the installation of the setup support files and then takes you the feature selection screen..

image

where I can choose what I want installed and where it will be put.  For Express edition it’s limited..

  • the database engine itself
  • replication ( the ability to publish and or subscribe to changes to selected tables in another database)
  • Client SDK

I’m going to grab everything and click next, which brings up the Instance Configuration screen..

image

If you don’t now what an instance is I can see why this is confusing.  An instance is an installation of SQL Server; and this concept exists because it’s possible to install SQL Server multiple times on the same machine and each of these can be different versions of SQL Server (e.g. 2005, 200, 2008 and so on).  Each instance has an ID ( for internal use) and a name.  to connect to particular instance you would specify MachineName\InstanceName.   In the screen above an instance name of SQL Express is set. I could select default instance and this would simply be refeenceced by the MachineName

However I am going to leave the instance name as SQL Express and click next.   Now I have to specify what account will be used to run the two SQL Sever services.  BTW the browser service is there to browse the instances of SQL Server on the machine and is only enabled where there is more than one instance installed.

If this was a production environment and/or sitting behind an internet facing application then a low privilege account is recommended (i.e. not a local admin account).  the default is perfectly acceptable and I would only change if need to adhere to corporate policy which would typically be to use a domain account.

Note there is a collation tab at the top of the screen which displays the collation options..

image

which describes how terms will be matched in queries e.g. case sensitvity etc.I am happy with this and I click Next  to bring up the Account Provisioning  tab of the Server Configuration screen..

image

Firstly you need to decide on the authentication mode.  There are two ways users can log into SQL Server and be granted appropriate rights:

  • using windows authentication i.e. you are logged onto the local machine (as I am here) or more usually you are logging on to a domain.
  • using mixed mode allows users to be authenticated by SQL Server itself  in addition to windows authentication which is always available. SQL Server authentication is useful if you are accessing SQL Server from a non domain joined machine. It requires you to have a SQL Server administrator account and to give this a password (BTW you can change the name of this account to make your system harder to hack). I am going with mixed mode, and so I am using a strong password.

 

BTW if your a local admin on your machine and you can’t get into your SQL Server express system then there is a script on the SQL Server express blog to do that

There are other tabs here are:

  • Data Directories which is where the database you create and the system databases are stored
  • User instances allows child instances of SQL Server to be run by users in their own security context. For more on this refer to this post from the SQL Express team
  • Filestream enables SQL Server to handle large unstructured files.  These reside in special directories rather than in the database, and in Express edition this means that these files don’t affect the 10Gb limit on database size. more on filestream can be found here.

 

I am leaving everything as is before clicking next to bring up the error reporting  screen..

image

which simply asks if you are happy to report errors to Microsoft. I click next  the installation proper begins.  After about 4 minutes the install completed successfully

image

 

 

click close to finish the installation. Before you close the installation program you might want to check out some of the links on the resources tab.

Hopefully that wasn’t to painful and will encourage you to install SQL Server express and try it out as the database platform for your applications