SQL Server 2005 – Setup and Deployment

So far, in our series of articles, we have presented the most significant
new and enhanced features available in Microsoft’s SQL Server 2005 Beta 2, but
neglected to provide you with information regarding its installation. While it
is more than likely that, by now, you already have accomplished this entirely
on your own, we suspect that you still should be able to benefit from a more
in-depth analysis of the setup process. Explaining improvements in its design and
implementation is the primary purpose of this article.

One of the most prominent differences between this and older versions of SQL
Server is the integration of all components and the core database engine into a
single installation unit. Besides Replication, Full-Text Search, and tools for
managing relational and XML data, this includes such (previously distinct)
features as Analysis Services, Notification Services, or Reporting Services.
Note, however, that by default, a number of features or components such as Data
Transformation Services, SQL Server Agent, SQL Browser, and Full-Text Search,
Service Broker, Replication, SQL Debugging, SQLiMail, SQLMail, Database
Mirroring are either not installed or disabled.

The structure of the setup program reflects its Windows Installer-based
character, which combines all of its components into a single product, with
individual items easily selectable as features and configurable depending on
your preferences (allowing, for example, specifying distinct destination paths
for each). The most straightforward method of launching setup is by executing
either the AUTORUN.EXE or SETUP.EXE program residing in the root directory of
the installation CD from Windows Explorer (depending on configuration,
AUTORUN.EXE might execute automatically as soon as the installation media is
detected in a removable drive). Both methods will trigger SQL Server
Installation Wizard (this can also be accomplished by running SPLASH.HTA and
selecting the "Run the SQL Server Installation Wizard" option from
the presented splash screen). However, you can also perform local (as well as
remote) multiple-instance installations via command line interface with a
variety of options defined through switches or settings stored in INI files (or
combination of both). Examples of most typical settings are provided in the
TEMPLATE.INI file residing in the root folder of the installation CD and
demonstrate such capabilities as:

  • Specifying the target computer (the same can be done using the TARGETCOMPUTER=
    command line parameter). Avoid including the ADMINPASSWORD parameter within the
    INI file, since this value is stored as clear text and can easily be
    compromised. Instead, consider assigning values for both ADMINACCOUNT and
    ADMINPASSWORD via the command line interface,

  • Setting the target directory for SQL Server engine binaries and
    Analysis Services data files with INSTALLSQLDIR (set by default to %ProgramFiles%Microsoft
    SQL Server folder) and INSTALLASDATADIR (set by default to %ProgramFiles%Microsoft
    SQL ServerData) parameters,

  • Modifying the existing setup by adding both instance aware (e.g.
    Analysis Server, Replication, etc.) and shared (e.g. Client Components)
    features, using ADDLOCAL (to install all components, you would specify ADDLOCAL=All)
    or REMOVE parameters. If you intend to add or remove individual components, you
    need to use the appropriate, case-sensitive name of each one (refer to the
    Books Online for their full listing),

  • Assigning properties of services for SQL Server, SQL Server
    Agent, Analysis Services, or Reporting Services, including their security
    context (which defaults to Local System account) and their startup mode,

  • Defining such instance- and server-wide installation options as
    authentication mode (with SECURITYMODE parameter), licensing, Product ID (25
    character product identification key), or default collation settings,

  • Performing clustered installation or uninstallation, as well as
    adding and removing individual nodes to it (indicated by inclusion of VS
    parameter),

  • Removing some or all of the installed components.

To take advantage of settings stored in an INI file, you need to specify its
location and name following the /settings switch when invoking SETUP.EXE from
the command line. This automatically eliminates the need for selecting these
settings via graphical interface during a standard, GUI-based installation.
The /qn command line switch not only makes the installation silent but is also
required for an unattended installation of a failover cluster). It results in
all messages being written to Setup log files (instead of displaying them
interactively). If you want to track the progress of installation on a
non-clustered system, you can attempt the /qb switch instead, which allows you
to view dialog boxes showing progress information, while still maintaining
unattended character of installation process. Note that modifications and
removal of individual components or the entire product can be performed with Add
or Remove Programs applet in Control Panel.

Logging has been greatly enhanced and modified to reflect the Windows
Installer-based character of the installation process. The main log file is
stored as the %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt
and is divided into sections corresponding to individual products (such as
Analysis Services, Microsoft XML Parser, Microsoft SQL Native Client, etc.)
informing about the final outcome of their installation. For each product, there
is also a separate log residing in the folder %ProgramFiles%Microsoft SQL
Server90Setup BootstrapLOGFiles, which is named according to the convention,
SQLSetup[XXXX][s]_[MACHINENAME]_[PRODUCTNAME]_[Y].log, where the XXXX
enumerates each subsequent installation attempt, MACHINENAME specifies target
computer name, PRODUCTNAME contains code designating the product (e.g. SQL for
Database Engine Setup log file, AS for Analysis Services, RS for Reporting
Services, NS for Notification Services, DTS for Data Transformation Services,
Tools for Client Components, SQL Server Management Studio, and Books Online,
etc.), and Y indicates the number of times that setup of a specific product has
been repeated during a single install (in rare cases when such even takes place).
In case of an error, the resulting exit code is examined by the Setup program,
which, in turn, automatically generates a notification explaining the cause of
the problem and providing suggestions for its resolution. As part of the setup,
you also have an option of enabling Error Reporting which delivers fatal SQL
Server errors to Microsoft ("Automatically send fatal error reports to
Microsoft" appears on the Error Reporting page of the Installation
Wizard).

Missing components that are required by SQL Server 2005 features are
detected by the SQL Server Component Update, which installs, for example, .NET
Framework 2.0, Microsoft SQL Native Client, and Setup support files. You should
also remember that the system on which the installation will be performed
should have enabled Task Scheduler and Windows Cryptographic Service Provider
(CSP) services. Misconfiguration issues that would prevent successful
completion of installation are detected by System Configuration Checker (SCC).
This utility, based on Windows Management Instrumentation technology, is invoked
by default at the initial stage of the setup (as soon as the Installation
Wizard is launched), analyzes status of the target system, and, in cases where
unsupported configuration is detected, provides recommendations regarding
necessary changes (or fixes it automatically, if possible). System
Configuration Check reports Error, Warning, or Success regarding the status of
such components or conditions as WMI Service, MSXML, Operating System Service
Pack Level, SQL Compatibility with Operating System, Minimum Hardware, Internet
Information Service, Pending Reboots, Performance Monitor Counter, Default
Installation Path Permissions, Internet Explorer, or COM + Catalog. Depending on
the severity of discovered issues or lack of required components and
configuration options, SCC might block setup from proceeding.

The availability of some options during the installation depends on meeting
certain prerequisites. For example, in order for the "Install as Virtual
Server" checkbox to be available on the "Components to Install"
page of the Installation Wizard, the target system needs to be set up as a
cluster. Similarly, inclusion of Reporting Services in the installation (another
option appearing on the same page) is possible, provided that Internet
Information Services is present (since IIS virtual directories are needed for
publishing reports and managing Reporting Services). Report Designer requires
Internet Explorer 6.0 SP1 or higher (the same requirement applies to SQL Server
Management Studio and Business Intelligence Development Studio). We will be
covering this and other topics related to Reporting Services in future articles
of our series.

The patching process is more streamlined as well as easier to implement and
maintain. While in SQL Server 2000, it was possible to incorporate hotfixes and
service packs into the setup program, this involved manual changes to the
content of original installation media. (Alternatively, you could apply missing
hotfixes following the setup; however, this created the potential for mistakes
resulting in partially unpatched systems and introduced a period of
vulnerability until all patching has been completed and the server restarted). With
SQL Server 2005, you can not only replace older versions of files in their
source but also download updates automatically at the time of the installation.

The outcome of the setup for each of the components is displayed on the
Installation Progress page of the Installation Wizard, (both in the graphical
form, as a green check mark or a red cross, and as a bit more descriptive
status message). As mentioned before, in case of problems, additional details
about their probable cause may be obtained by analyzing the appropriate log
files. You should also verify that all necessary services for each installed
instance are running (this might include not only SQL Server and SQL Server
Agent, but also Analysis Services and Reporting Services, provided that you
included them as part of your setup). Note that immediately following the
installation, SQL Server checks whether the computer name on which it has been
installed has changed, and if this is the case, it adjusts its parameters
accordingly. This simplifies image-based deployment, where replicas of a disk
containing SQL Server installation are copied across multiple systems.

SQL Server 2005 Setup can be also used for upgrading existing SQL Server 7.0
and SQL Server 2000 installations. Downtime required for this process has been
reduced compared with earlier versions. While the direct upgrade from SQL
Server 6.5 is not supported, you can resolve this issue by first performing an
upgrade to SQL Server 7.0 or 2000, or migrating databases between two editions
using Data Transformation Services (or an alternative method for transferring
data).

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles