How to install MSSQL Express 2005

by Scott Lowe MCSE
http://articles.techrepublic.com.com/5100-10878_11-6102265.html

With the release of SQL Server 2005 Express Edition and the SQL Server Management Studio Express, Microsoft has entered the small, free database market with a splash trumping even their own Microsoft SQL Desktop Edition. SQL Server Management Studio Express -- a full-featured management tool comparable to the legacy SQL Server Enterprise Manager -- makes this entry-level database software usable and formidable for running small businesses, small Web sites, and more.

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

Install SQL Server 2005 Express Edition

For this article, I am installing SQL Server 2005 Express Edition. Double-click the setup file to start the product installation. The contents of the file are extracted and the installation begins.

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 give you trouble if you continue the installation. In Figure B below, you can see that my test system has a warning regarding the minimum hardware requirements. Click Next to continue.

Figure B

If you have any serious problems, correct them before you continue.

After that, you have to provide your name and, optionally, your company's name. I have also unchecked the box marked "Hide advanced configuration options" so you can get a look at these options during the installation.

Figure C Provide registration details for your server installation.

Next, you get to choose which features you want to install. I'm installing into the default location, C:\Program Files\Microsoft SQL Server. If you want to install to a different location, click the Browse button to choose this new location.

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

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 my installation, I'm installing SQL Server 2005 Express Edition as the default database instance on my server.

Figure E Choose your instance.

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. If you run your SQL Server under an administrative account, you run the risk of additional damage should your server be compromised. Therefore, run SQL Server 2005 Express Edition under a user account with few, if any, rights.

Figure F

Figure F Use the account you created before as the service account for SQL Server 2005.

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

If you choose Mixed Mode (which you should) you will also need to provide a password for the SQL Server “sa” user. Make sure that you assign a strong password if you take this route.

Figure G

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

Click Next to go to next step. We recommend to leave the options to their default states and click Next.

Figure H Choose your collation method.

Go to next step:

Figure I

Figure I Decide whether you want to enable support for user instances.

Go to next step:

Figure J Reporting Services is a welcome addition to SQL Server.

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. Personally, I like the error reporting service and the only way it will improve is by adding data. I don't like sending usage data, however. While it's supposedly confidential, it doesn't directly benefit my installation.

Figure K

Figure K Decide what information you want to send to Microsoft.

These are all of your options for SQL Server 2005 Express Edition. Continue on to install the product using these selections.

Figure L 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, please uninstall SQL Native Client first, then reinstall MS SQL Express again.