In the first two installments of our series dedicated to SQL Server 2005 Express Edition, we have presented an overview of its most basic characteristics, focusing on those that distinguish it from its predecessor, Microsoft Desktop Engine (MSDE) 2000 as well its contemporaries (other flavors of SQL Server 2005, in particular, its Compact Edition). As we have indicated, limited functionality implemented in the initial version has been extended, yielding several downloadable software packages. In this article, we will describe in more detail the differences between them and their basic installation process.
There are two main locations from which you can obtain SQL Server 2005 Express Edition. The first one is reachable via the Visual Studio Express section of the MSDN Web site, where you will find the following 32-bit versions of the product, integrated with Service Pack 2:
- SQL Server 2005 Express Edition SP2 – containing SQL Server Express database engine and client components, grouped together into a compact, single executable (SQLEXPR32.EXE sized at 36.5 MB), intended exclusively for x86 systems.
- SQL Server Management Studio Express – graphical interface utility (feature-limited version of SQL Server Management Studio), constituting one of the most readily apparent improvements over MSDE 2000 (which offered only command-line management tools), in the form of a single, Windows-installer based file (SQLServer2005_SSMSEE.msi sized at 43.1 MB).
- SQL Server 2005 Express Edition with Advanced Services SP2 – includes, in addition to database engine, client components, and Management Studio Express, support for such features as Full-Text Search and Reporting Services. This added functionality contributes to a considerably larger size (SQLEXPR_ADV.EXE measures about 234 MB)
- SQL Server 2005 Express Edition Toolkit – consists of client components, SQL Server Management Studio Express, and Business Intelligence Management Studio, making it suitable for installations on management workstations, separately from a systems hosting database engine. This combination is served as SQLEXPR_TOOLKIT.EXE sized at 224 MB.
The same web page provides links to 32-bit versions of SQL Server 2005 Express Edition SP2 and Management Studio Express that can run on a 64-bit platform (such as x64 or EMT64 – note, however, that SQL Server 2005 Express Edition is not supported on IA64 systems) using Windows on windows (WoW) mechanism. These two files – SQLEXPR.EXE and SQLServer2005_SSMSEE_x64.msi – are sized at 55.3 MB and 43.6 MB, respectively (increased size is the result of additional binaries required to support WoW operations). You can also find there links to the .NET Framework 2.0 download (which is the prerequisite for the installation) as well as to the product registration area. Registration (optional for the purpose of installation, but mandatory if you are planning on distributing your database-bound applications), gives you additional benefits (such as documentation containing instructions on developing applications using SQL Server Express or collection of images that you can incorporate in your projects).
Another rendition of SQL Server 2005 Express Edition (although without the Advanced Services and slipstreamed SP2) is available from the download section of the Microsoft web site, in the form of SQLEXPR.EXE file, 53.5 MB in size, which is at the RTM level (to bring it up to SP2 level, you can simply install the updated version of SQLEXPR.EXE that should detect the existing instance and upgrade it accordingly). Just like the previously described SP2 version, this software is capable of operating on x64 systems by leveraging the WoW mechanism. On the same page you will also find a link to the download of the original release of SQL Server 2005 Management Studio Express (with dedicated 32-bit and 64-bit capable versions at 38.5 MB and 39 MB, respectively).
Regardless of which source location and packaging you choose, in order to install SQL Server 2005 Express Edition, you will need to ensure first that your system is running the Microsoft .NET Framework 2.0 (or later), which can be obtained from the Microsoft Download Center in both x86 and x64 versions (ensure that you patch it with the latest security updates). Once the .NET Framework is present on the target system, launch the executable (i.e. SQLEXPR32.EXE or SQLEXPR.EXE, depending on your hardware and operating system platform) to initiate Windows Installer-based SQL Server 2005 Setup wizard. As usual, you will be prompted first to accept the licensing terms and conditions, which will segue into installation of prerequisites (such as SQL Native Client and setup support files) followed by the Welcome to the Microsoft SQL Server Installation Wizard page. The next step will initiate System Configuration Check, which, in turn, will perform a number of checks (such as verifying sufficient hardware, permissions, or operating system and Internet Explorer levels, as well as presence and proper configuration of WMI, MSXML, COM+, MDAC, or ASP.NET), represented by actions listed in the Details section of the wizard’s page. Make sure to review the status of each and act according to instructions listed in the Message column before you proceed. At that point, you will be asked to provide standard registration information (name and company info). On the same page, you will also have a chance to specify whether you want to customize the setup process by clearing the “Hide advanced configuration options” checkbox (which affects availability of pages controlling Service Account, Collation, and Instance Name settings). Feature Selection page follows, giving you an opportunity to pick non-default installation options, such as replication (in addition to data files and shared tools) or client components (including network libraries for OLE DB or ODBC and Software Development Kit). Instance Name page lets you choose between installing the default or a named instance (the latter, with the name SQLExpress is the default option). The Service Account page gives you the ability to define accounts in which security context SQL Server and SQL Browser services will operate. This can be either one of three built-in accounts (Local System, Local Service, and Network Service) or an arbitrary (domain or local) user account.
In general, it is recommended to apply configuration that grants the lowest required level of privileges in order to minimize exposure to malicious attacks. This implies that you should avoid Local System, since this is the most powerful account on your computer. While this was frequently the choice on legacy stand-alone installations, the high level of access it provides constitutes unnecessary risk that could potentially lead to a compromise of the entire system. In addition, this option is not appropriate for scenarios that require the local SQL Server instance to interact with remote ones in the security context of its service account (since the scope of the Local System account is limited to the operating system on which it is defined). Use of Local Service mitigates vulnerability to some extent (since its level of access is equivalent to that of a standard user), however, it is a subject to a similar limitation as the Local System as far as network access is concerned (it operates via the null sessions mechanism without applying any specific credentials). Network Service eliminates this shortcoming, since it is capable of operating over the network using the computer account for authentication purposes. In general, however, it is recommended to create a dedicated domain or local user account. Keep in mind that such an account does not need to be a member of the local Administrators group since the setup process will automatically assign to it all necessary privileges (for more information on this subject, refer to the MSDN article Setting Up Windows Service Accounts).
Note that the settings defined on this page can be either shared between SQL Server and SQL Browser services or assigned independently to each (according to the state of “Customize for each service account” checkbox). In addition, for each service you can specify its own startup type (manual or automatic) via checkboxes in the “Start services at the end of the setup” section. Since SQL Browser service facilitates connections from remote clients browsing for a SQL Server instance in order to establish either user or dedicated administrator connection (DAC) endpoints, make sure it is set to Automatic startup if you want to make this functionality available.
Using options displayed on the Authentication Mode page, you determine whether you want to limit logons to Windows accounts only (Windows Authentication Mode) or also allow logins defined within SQL Server (Mixed Mode). In case you decide on using the latter, you will also need to set password for sa logon.
Collation settings on the next page of the wizard designate rules that dictate data comparison and sorting behavior. Details of this configuration involve such caveats as case-sensitivity, accent-sensitivity, Kana-sensitivity (when dealing with the Japanese character set), or width-sensitivity (relevant in case of single- and double-byte characters). You assign your preferences by either selecting collection designator (which corresponds roughly to your Windows system locale) and sort order (binary, case-sensitive, accent-sensitive, binary-code point, Kana-sensitive, or width-sensitive), or by choosing one of collations listed in the format similar to the one used in legacy versions of SQL Server (where each entry combines specific character set and sort order). The default option (dictionary order, case-insensitive, for use with 1252 character set) is suitable for the majority of US-based installs.
The Configuration Options page contains two settings. The first one controls availability of user instances. This refers to the unique feature of SQL Server 2005 Express Edition, which, as we briefly mentioned in our previous article, makes it possible to dynamically initiate new instances in the security context of standard Windows user accounts. The second one, labeled “Add user to the SQL Server Administrator role”, allows you to remediate a configuration change introduced on Windows Vista, which precludes the local Administrators group from being automatically assigned the sysadmin fixed SQL Server role. Note that this setting does not appear during setup based on the RTM version of SQLEXPR.EXE (you will, however, have a chance to configure it in the same manner as part of Service Pack 2 upgrade).
If you have performed installation of any recently released Microsoft software, you are most likely already familiar with the Error and Usage Report Settings page that appears next. By allowing error reports to be sent automatically to the vendor, you provide data that can be used to analyze and fix any software issues you experience. Feature usage data is supposed to deliver information about your system, its configuration, and usage patterns, but without violating your privacy.
Once you click on Next, you will reach the Ready to Install page, which provides a summary of the options you selected (which in this case includes simply SQL Server Database Services). Your confirmation will trigger the display of the Setup Progress window that allows you to track each of the installation steps. In our next article of this series, we will review the results of installation and describe initial configuration tasks.