Installing SQL Server 2008 Express Guidance

SQL Server Technical Article

Writer: Karl Dehmer

Technical Reviewer: Eric King and Peter Saddow

Published: June, 2009

Applies to: SQL Server 2008

Summary: This article is for end users to determine appropriate options for installing SQL Server 2008 Express and guidance on when customizing the installation may be necessary.

Introduction

There are two options for installing SQL Server 2008 Express, Basic and Advanced.

Basic: This option is recommended for most users. If you need a basic installation, visit the SQL Server Express download page (https://www.microsoft.com/express/sql/download/). The Microsoft Web Platform Installer runs a basic default installation. If you require advanced customization of your installation then the Microsoft Download Center is the appropriate location for installing SQL Server 2008 Express.

Advanced: If you need to customize the installation, you can obtain Express from the Microsoft Download Center. Here are the Express editions available for download.

Basic Installation Through Web Platform Installer

The Web Platform Installer provides a default installation appropriate for most customers. It is a hands-off install providing customers easy an installation of SQL Server 2008 Express. When you install Express through Web Platform Installer, setup uses default configuration values. The Web PI detection logic will not let you install Express if the installation will conflict with an existing install of SQL Server. If this happens, you will need to install from the download center.

Web PI installation uses the following default command line parameters. If you need to change these values, you will need to perform a custom install.

  • /q
  • /ACTION=Install
  • /FEATURES=SQL,RS,TOOLS
  • /INSTANCENAME=SQLEXPRESS
  • /SQLSVCACCOUNT="NT AUTHORITY\Network Service"
  • /SQLSVCStartupType=Automatic
  • /RSSVCACCOUNT="NT AUTHORITY\Network Service"
  • /RSSVCStartupType=Automatic
  • /AddCurrentUserAsSqlAdmin
  • /ENABLERANU=1

Advanced Installation Through the Microsoft Download Center

It is only necessary to use the Microsoft Download Center when you require non-default configuration values for an installation of SQL Server 2008 Express.

Installation Scenarios

An advanced installation of SQL Server 2008 Express through the Download Center may be necessary if any of the scenarios below apply to you.

Upgrading

If you are upgrading from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 a custom/advanced installation is required.

For more information, see “How to Identify Your SQL Server Version and Edition” (https://support.microsoft.com/default.aspx/kb/321185).

Specifying an Instance Name other than SQLEXPRESS

SQL Server 2008 Express uses the default instance name of “SQLEXPRESS”.  Changing the instance name or installing additional instances requires a custom installation. You cannot change instance names once the installation is complete. There are several scenarios of when you would to provide your own instance name.

  • The default instance name has already been used (installing multiple instances)
  • Your environment (application or standards) requires the use of an alternate instance name. For example, you may use instance names to segment groups or organizations within the company, i.e. Sales, Operation, Finance, etc.
  • You want to provide a higher level of abstraction from the underlying database software.

Embedding

If you develop software applications that embed SQL Server 2008 Express, custom installations are typically required. For more information, see Embedding SQL Server 2008 Express in an Application (https://go.microsoft.com/fwlink/?linkid=155160).

Further Setup Customization

Some SQL Server configuration settings can only be set during install. Once the installation is complete, you cannot change these settings. Any change to the configuration settings would require a new installation of the product. Some settings directly impact how data is stored and organized so changing the configuration setting would require significant data manipulation work in order to preserve the data.

Setting the non-Default Collation

Collations effect how characters (data) for a language or alphabet are recognized and sorted. A majority of SQL Server installations use the default collation. There are some situations where the use of a collation other than the default collation setting would be required.

  • Select a BINARY2 collation if binary code point based ordering is acceptable.
  • Select a Windows collation for consistent comparison across data types.
  • Use new 100 level collation for better linguistic sorting support. For more information, see Collation and Unicode Support (https://msdn.microsoft.com/en-us/library/ms143503.aspx).
  • If you plan to migrate a database to the upgraded instance of SQL Server Express, select the collation that matches your existing collation of the database.

For more information, see Collation Settings in Setup (https://msdn.microsoft.com/en-us/library/ms143508.aspx).

Enabling Filestream

The Filestream configuration setting specifies where to store unstructured data such as text documents, images or videos. You can store this unstructured data inside the database or outside the database in the NTFS files system. The default setting is to store the data inside the database. Storing outside the database can cause data management complexities. You should consider enabling FILESTREAM if:

  • Objects being stored are, on average, larger than 1 MB.
  • Fast read access is important.
  • You are developing applications that use a middle tier for application logic.

For smaller objects, storing unstructured data in the database often provides better streaming performance.

For more information, see FILESTREAM Overview (https://msdn.microsoft.com/en-us/library/bb933993.aspx) and FILESTREAM Storage in SQL Server 2008 (https://msdn.microsoft.com/en-us/library/cc949109.aspx).

Enabling Error Reporting

Enabling the Error Reporting setting allows you to report Windows and SQL Server errors to Microsoft via the internet. Microsoft uses these error reports to improve future releases of SQL Server. Error reports contain only technical data. All error reports are confidential and anonymous.

Customizing Installation Directories

Installation directories are unique folder locations used for placement of:

  • Program software
  • System database
  • User databases and logs
  • Temp DB and log
  • Backups

During installation, you have the option to changing the default directories for some or all of the folders. Business requirements or environmental factors may dictate specifying directories other than the default.

Configuring SQL Server Service Accounts

SQL Server includes many services ranging from tools such as SQL Server Browser service to major product components such as Analysis services and Reporting services. During installation, you can specify which services to install and the account in which they run under. By default, most SQL Server services run under the Network Service account during initial setup. For better manageability and a more secure environment, it is recommended you:

  • Use separate accounts for each SQL Server service.
  • Configure service accounts to use the lowest possible permissions. This can reduce surface area for attacks.
  • Associate SQL Server services with low privileged Windows local user accounts, or domain user accounts.

For more information, see Setting Up Windows Service Accounts (https://msdn.microsoft.com/en-us/library/ms143504.aspx).

Conclusion

For more information:

https://www.microsoft.com/sqlserver/: SQL Server Web site

https://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

https://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter 

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

  • Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
  • Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.