Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 25, 2005

SQL Server 2005 - Setup and Deployment

By Marcin Policht

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 Server\Data\) 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 Server\90\Setup Bootstrap\LOG\Summary.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 Server\90\Setup Bootstrap\LOG\Files, 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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM