Quick Guide: How to install SQL Server 2005 Express Edition

Although this document addresses freely distributed version of SQL Server 2005 Express Edition, the instructions are very similar or the same for other editions of SQL Server 2005. Even though the Express Edition is free, it includes many of the features found in the Standard and Enterprise editions.

SQL Server 2005 Express Edition does not have significant system overhead requirements, given the overpowered nature of many of today's newest servers. Although it is free, it is still important for you to install this application correctly.

To get SQL Server 2005 Express Edition install on your server, follow these steps.

Step 1: Download SQL Server 2005 Express Edition

The SQL Server 2005 Express Edition download page has three separate downloads for you to consider.

Table A -- Decide which features you need for your installation

Version Feature	SQL Server 2005 Express Edition SP1	SQL Server 2005 Express Edition with Advanced Services SP1	SQL Server 2005 Express Edition Toolkit SP1
Database engine	X	X
Client software	X	X	X
Full-text search		X
Reporting Services		X
Management Studio Express		X	X

Step 2: Identify system requirements

The minimum system requirements for the various SQL Server 2005 versions are:

Table B -- System and software requirements for SQL Server 2005 Express Edition

Version Feature	SQL Server 2005 Express Edition SP1	SQL Server 2005 Express Edition with Advanced Services SP1	SQL Server 2005 Express Edition Toolkit SP1
RAM (minimum)	192 MB	512 MB	512 MB
RAM (recommended)	512 MB	1 GB	1 GB
Drive space	600 MB
Processor (minimum)	600 MHz
Processor (recommended)	1 GHz
IIS 5 or higher	No	Yes	No
Operating systems supported	Windows Server 2003 SP1, Windows Server 2003 Enterprise Edition SP1, Windows Server 2003 Datacenter Edition SP1, Windows Server 2003 Web Edition SP1, Windows Small Business Server 2003 Standard Edition SP1, Windows Small Business Server 2003 Premium Edition SP1, Windows XP Professional SP2, Windows XP Home Edition SP2, Windows XP Tablet Edition SP2, Windows XP Media Edition SP2, Windows 2000 Professional Edition SP4, Windows 2000 Server Edition SP4, Windows 2000 Advanced Edition SP4, Windows 2000 Datacenter Server Edition SP4
Software prerequisites	.NET Framework 2.0 & MSXML 6
Other requirements	Server joined to your Active Directory domain.

Step 3: Install database software prerequisites

As mentioned above, there are a number of software prerequisites for SQL Server 2005 Express Edition. Before you can install the database software, you need to take the necessary steps to get these items installed.

Install the following items in order.

Internet Information Server 5 or higher

If your Windows server does not have IIS installed, go to Start | Control Panel | Add or Remove Programs | Add/Remove Windows Components.

.NET Framework 2.0

Download the .NET Framework 2.0 (x86) from here. After downloading, execute dotnetfx.exe and follow the instructions to complete the installation. I'd show a screenshot, but there's really not a whole lot to see!

MSXML6

Download MSXML6 from here. Execute msxml6.msi. This is a quick installation.

Step 4: Install SQL Server 2005 Express Edition

In this documentation, we are installing SQL Server 2005 Express Edition with Advanced Services SP1. The name of the downloaded file is SQLEXPR_ADV.EXE. Double-click this file to start the product installation. The contents of the file are extracted and the installation begins.

After you start the installation, you have to agree to the typical End User License Agreement after which you're presented with a screen that shows you what prerequisites you need to get the express edition on your system. The installer will handle the installation of these prerequisites for you. Click the Install button to install these items. Once the items are installed, click the Next button.

Figure A

The new Native Client and the Setup Support Files need to be installed.

Once the prerequisites are in place the SQL Server installation wizard will start. The first screen you're shown provides you with a list of items that may or may not result into problems if you continue the installation. In Figure B below, system configuration check has displayed a warning regarding the minimum hardware requirements. Remember that installation of SQL Server 2005 Express Edition requires roughly 1GB of free disk space. This warning is critical, since not all necessary files will be installed should we decide to continue installation. If you get any other serious warnings, resolve them before
you continue. Click Next to continue.

Figure B

After that, you have to provide your name and, optionally, your company's name.

Uncheck the box marked "Hide advanced configuration options" so you can get a look at the options during the installation.

Choose which features you want to install. For this documentation, we have decided to install everything except replication. Note that we have also opted to install the Management Studio Express. This is an outstanding utility provided by Microsoft. It is highly recommend you install this tool.

Decide the installation folder. Default location is C:\Program Files\Microsoft SQL Server. If you want to install to a different location, click the Browse button to choose this new location. As a general rule it is not necessary to change the default installation path, unless your organization has a standard which is different.

Figure C

Decide which features you want to install. The only one you absolutely need is the Database Services option, but the others are very useful.

Specify instance name. Instances were introduced in SQL Server 2000 and provide a way for you to more seamlessly serve multiple databases from a single SQL Server. SQL Server 2005 Express Edition supports up to 16 named instances while the Enterprise Edition supports up to 50. For simplicity, install SQL Server 2005 Express Edition as the default database instance on your server. Default Instance is always named SQLExpress. Take your pen and paper and jot this down somewhere, as you will it need later on to configure Josytal DataBase Applications SharePoint.

Figure D

Choose your instance. IMPORTANT NOTE
If the Default instance radio button is disabled, this probably means that there is another, default instance of SQL Server 2000 or SQL Server 2005 already installed on this machine. In this case, consult the DBA for the project as it may not be necessary or desirable to re-install the database components.

Specify Service Account. Service accounts are used in order to better protect your SQL Server -- and the rest of your network -- in the event of a security breach. For simplicity purpose, we shall use the option "Use the built-in System account"

Figure E

For simplicity, use the option "Use the built-in System account" The option will change the Windows account under which the SQL Server service executes to the Local System account. In a production environment or certain development environments, it may be desirable to change this to a domain user account, but this is not usually necessary for small businesses. Note that the service account settings can be changed through the Services applet in the Control Panel at any point after installation, however SQL Server will require re-starting in order for these changes to take effect.

Specify the Authentication Mode. SQL Server can authenticate users in two ways:
(1) directly against Active Directory (Windows Authentication Mode);
(2) against its own database (Mixed Mode, since Windows Authentication is also supported).

From a security perspective, Windows Authentication mode is highly preferred.
If you choose Mixed Mode you will need to provide a password for the SQL Server 'sa' user. Make sure that you assign a strong password if you take this route. Also take your pen and paper and jot the password down somewhere, as you will need it later on to configure Josytal DataBase Applications SharePoint.

Figure F

Choose the mode under which you want to authenticate SQL Server users.

Choose your collation method. Collation settings define the way your SQL Server will sort data. If you need to maintain backward compatibility with older versions of SQL Server, you should choose SQL Collations. If, however, you do not need to worry about backward compatibility, you should choose the Latin1_General collation method, unless, of course, you're using a different language.

Figure G

Choose your collation method.

Decide whether you want to enable support for user instances. SQL Server 2005 Express provides a new feature: The ability to run sub-instances of the product under a normal user account. User instances are useful in situations in which users are logged into Windows under a least-privileged user account. Using user instances, the user can still have SQL system administrator privileges to their SQL "sandbox", but the rest of the system is protected since the user's primary account does not have rights to make global changes. Databases housed under user instances support only a single connection and higher-end features such as replication are not supported.

If you want to support user instances in your installation, make sure the checkbox next to "Enable User Instances" is selected.

Figure H

Decide whether you want to enable support for user instances.

Specify How to Install a report server instance. An enhanced version of Reporting Services is included with SQL Server 2005 Express Edition installation. It is recommended that you install this feature, but don't worry about customizing the configuration. The default configuration places the tool at http://{your server name}/ReportServer. The Report Manager tool is placed at http://{your server name}/Reports. Please see Microsoft documentation for elaborated guides.

Figure I

Reporting Services is a welcome addition to SQL Server.

Error and Usage Report Settings. Microsoft has included an error reporting tool in other applications for quite some time. This feature is now optionally included in SQL Server 2005 Express. Further, Microsoft wants you to send them anonymous usage data about your installation. Neither option is enabled by default. While it's supposedly confidential, it doesn't directly benefit nor haut your installation in any way. The choice is left to you

Figure J

Decide what information you want to send to Microsoft.

Installation options and Settings Confirmation. Displays all of your installation options for SQL Server 2005 Express Edition. Continue on to install the product using these selections.

Figure K

The installation's progress.

In some cases, your installation may fail with errors reported for the SQL Native Client and the SQL Server Database Services. In addition, the Workstation Components selection may indicate a failure. If this happens to you, it may be because you formerly had a conflicting SQL Server service on the machine and the Native Client previously installed is creating a problem. If this is the case for you, follow these steps:

Change to the directory in which you saved the downloaded SQL Server Express 2005 installer.
Extract the contents of the installer to a new directory: SQLEXPR_ADV.EXE /x:c:\sqltmp. If you are using the download that does not contain the advanced services, this command is SQLEXPR.EXE /x:c:\sqltmp instead.
Change to C:\sqltmp\setup.
Execute "sqlncli.msi"
Choose the "Uninstall" option.
Reboot your server.
Run the SQL Server Express 2005 installer again. It should be successful this time around.

Installed and ready

At this point, SQL Server 2005 Express Edition is installed and can be managed using the SQL Server Management Studio Express tool that was installed along with the database server. To access this tool go to Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio Express. Please see Microsoft documentation for elaborated guides.

You can manage your new database installation using the Surface Area Configuration tool and the Management Studio Express. Please see Microsoft documentation for elaborated guides.

You can now install and configure Josytal DataBase Applications SharePoint if you want to use SQL Server Database