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.
»
See All Articles by Columnist Marcin Policht
» 
Comment and Contribute
