Share via

  • Article

Embedding SQL Server 2008 Express in an Application

SQL Server Technical Article

Writers: KarlDehmer, Peter Saddow

Technical Reviewers: Peter Saddow, Russell Green, Devendra Tiwari and Arun Sundaram

Published: August 2009

Applies to: SQL Server 2008

Summary: This white paper is intended for developers who are responsible for designing and developing the setup and installation components for custom applications that embed Microsoft SQL Server 2008 Express. This document includes:

  • How to determine which edition of Express to install
  • How to verify existing SQL Server installations and instances
  • How to handle exit codes and rules
  • How to navigate and use the log files

<span id="_Toc237912056">

The use of the Microsoft® SQL Server® data management software for both business and personal applications continues to flourish. These applications are heavily ingrained in our daily lives and are providing increasingly more sophisticated information, which results in a greater need for supporting database technology. Microsoft SQL Server 2008 Express is a robust, freely distributable relational database management system. Express includes SQL Server Management Studio Basic. You can use SQL Server 2008 Express as a:

  • Local data store
  • Embedded database in an application
  • Lightweight database server

When integrated with Microsoft Visual Studio® 2008 SP1, SQL Server 2008 Express helps facilitate the design and development of database applications.

Which Edition of SQL Server 2008 Express to Use?

You have a choice of four different editions of SQL Server 2008 Express. Your application requirements will determine which edition you need. The following table lists each edition and the features it includes.

SQL Server 2008 Express Management Studio Basic Runtime Only with Tools with Advanced Services

SQL Server Database Engine

X

X

X

SQL Server Management Studio Basic*

X

X

X

Full-Text Search

X

Reporting Services

X

Download Size

38.5 MB

82.5 MB

230.4 MB

546.5 MB

Table 1: SQL Server 2008 Express editions

These four SQL Server 2008 editions include the following functionality and capabilities.

SQL Server 2008 Express with Tools

  • SQL Server Database Engine – for creating, storing, updating, and retrieving data
  • SQL Server Management Studio Basic – a visual database management tool for creating, editing and managing databases

SQL Server 2008 Express with Advanced Services

  • SQL Server Database Engine - for creating, storing, updating, and retrieving data
  • SQL Server Management Studio Basic – a visual database management tool for creating, editing and managing databases
  • Full-Text Search – a powerful, high-speed engine for searching text-intensive data
  • Reporting Services – an integrated design environment for creating reports

SQL Server 2008 Express (Runtime Only)

SQL Server Database Engine - for creating, storing, updating, and retrieving data

SQL Server 2008 Management Studio Express (SSMSE)

  • Free graphical management tool for configuring, managing, and administering SQL Server 2008 Express applications

  • Also use for managing multiple instances of the SQL Server Database Engine created by any edition of SQL Server 2008, including Workgroup, Web, Standard, and Enterprise editions

    Note: This separate download is for customers who have previously installed SQL Server 2008 Express (Runtime Only). For new installations of SQL Server 2008 Express and SQL Server Management Studio Express, download the SQL Server 2008 Express with Tools from the SQL Server Installation Wizard.

For more information about SQL Server 2008 Express, go to the SQL Server 2008 Express Web site (https://www.microsoft.com/sqlserver/2008/en/us/express.aspx).

To download SQL Server 2008 Express, go to the SQL Server 2008 Express download page (https://www.microsoft.com/express/sql/download/).

<span id="_Toc237912062">

Before installing SQL Server 2008 Express on a computer, you need to determine whether prior versions of SQL Server or instances of SQL Server already exist. If prior versions exist, you need to decide whether to upgrade the existing instance or install a new instance.

The recommended and officially supported way to detect other versions of SQL Server, or SQL server instances on a computer, is by using the SQL Server Windows® Management Instrumentation (WMI) provider. Although other detection methods exist, this white paper uses the SQL Server WMI provider for SQL Server 2008 and SQL Server 2005. Other methods, such as using the registry for detecting instances, are not recommended.

Note: The SQL Server WMI provider can only detect instances of SQL Server 2005 and later.

The following code sample uses the WMI provider for SQL Server 2008 to detect all SQL Server 2008 and SQL Server 2005 instances. For every instance detected, the code will list the edition and version of each instance in the results. The sample illustrates how to obtain the list of instance names.

C# Example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Management;
namespace ExpressDetection
{
    class Program
    {
        static void Main(string[] args)
        {
            if (!EnumerateSQLInstances())
            {
                Console.WriteLine("There are no instances of SQL Server 2005 or SQL Server 2008 installed");
            }
        }
        /// <summary>
        /// Enumerates all SQL Server instances on the machine.
        /// </summary>
        /// <returns></returns>
        public static bool EnumerateSQLInstances()
        {
            string correctNamespace = GetCorrectWmiNameSpace();
            if (string.Equals(correctNamespace, string.Empty))
            {
                return false;
            }
            string query = string.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = 'instanceID'");
            ManagementObjectSearcher getSqlEngine = new ManagementObjectSearcher(correctNamespace, query);
            if (getSqlEngine.Get().Count == 0)
            {
                return false;
            }
            Console.WriteLine("SQL Server database instances discovered :");
            string instanceName = string.Empty;
            string serviceName = string.Empty;
            string version = string.Empty;
            string edition = string.Empty;
            Console.WriteLine("Instance Name \t ServiceName \t Edition \t Version \t");
            foreach (ManagementObject sqlEngine in getSqlEngine.Get())
            {
                serviceName = sqlEngine["ServiceName"].ToString();
                instanceName = GetInstanceNameFromServiceName(serviceName);
                version = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "Version");
                edition = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "SKUNAME");
                Console.Write("{0} \t", instanceName);
                Console.Write("{0} \t", serviceName);
                Console.Write("{0} \t", edition);
                Console.WriteLine("{0} \t", version);
            }
            return true;
        }
        /// <summary>
        /// Method returns the correct SQL namespace to use to detect SQL Server instances.
        /// </summary>
        /// <returns>namespace to use to detect SQL Server instances</returns>
        public static string GetCorrectWmiNameSpace()
        {
            String wmiNamespaceToUse = "root\\Microsoft\\sqlserver";
            List<string> namespaces = new List<string>();
            try
            {
                // Enumerate all WMI instances of
                // __namespace WMI class.
                ManagementClass nsClass =
                    new ManagementClass(
                    new ManagementScope(wmiNamespaceToUse),
                    new ManagementPath("__namespace"),
                    null);
                foreach (ManagementObject ns in
                    nsClass.GetInstances())
                {
                    namespaces.Add(ns["Name"].ToString());
                }
            }
            catch (ManagementException e)
            {
                Console.WriteLine("Exception = " + e.Message);
            }
            if (namespaces.Count > 0)
            {
                if (namespaces.Contains("ComputerManagement10"))
                {
                    //use katmai+ namespace
                    wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement10";
                }
                else if (namespaces.Contains("ComputerManagement"))
                {
                    //use yukon namespace
                    wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement";
                }
                else
                {
                    wmiNamespaceToUse = string.Empty;
                }
            }
            else
            {
                wmiNamespaceToUse = string.Empty;
            }
            return wmiNamespaceToUse;
        }
        /// <summary>
        /// method extracts the instance name from the service name
        /// </summary>
        /// <param name="serviceName"></param>
        /// <returns></returns>
        public static string GetInstanceNameFromServiceName(string serviceName)
        {
            if (!string.IsNullOrEmpty(serviceName))
            {
                if (string.Equals(serviceName, "MSSQLSERVER", StringComparison.OrdinalIgnoreCase))
                {
                    return serviceName;
                }
                else
                {
                    return serviceName.Substring(serviceName.IndexOf('$') + 1, serviceName.Length - serviceName.IndexOf('$')-1);
                }
            }
            else
            {
                return string.Empty;
            }
        }
        /// <summary>
        /// Returns the WMI property value for a given property name for a particular SQL Server service Name
        /// </summary>
        /// <param name="serviceName">The service name for the SQL Server engine serivce to query for</param>
        /// <param name="wmiNamespace">The wmi namespace to connect to </param>
        /// <param name="propertyName">The property name whose value is required</param>
        /// <returns></returns>
        public static string GetWmiPropertyValueForEngineService(string serviceName, string wmiNamespace, string propertyName)
        {
            string propertyValue = string.Empty;
            string query = String.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = '{0}' and ServiceName = '{1}'", propertyName, serviceName);
            ManagementObjectSearcher propertySearcher = new ManagementObjectSearcher(wmiNamespace, query);
            foreach (ManagementObject sqlEdition in propertySearcher.Get())
            {
                propertyValue = sqlEdition["PropertyStrValue"].ToString();
            }
            return propertyValue;
        }
    }
}

The screen shot in Figure 1 reflects the output from the sample code above. The results list all of the instances detected in addition to the instance edition and instance version.

Figure 1: Results of detected instances

To detect SQL Server 2005 instances, use the following namespace: root\Microsoft\SqlServer\ComputerManagement

For more information about troubleshooting problems with WMI, see Windows Management Instrumentation (https://msdn.microsoft.com/en-us/library/aa394582(VS.85).aspx).

<span id="_Toc237912063">

The two SQL Server 2008 Express installation modes are:

  • New SQL Server 2008 Express installations
  • Upgrade installations

Each installation mode has its own set of parameters that provide information and instructions on how to perform the new installation or upgrade. Parameters are either required or optional.

To accommodate installing or upgrading large numbers of systems, the SQL Server 2008 Express setup process is fully scriptable. You can run Express with associated parameters from:

  • The command prompt
  • Part of a command shell script
  • Another program

Regardless of where you execute a new installation or upgrade, you can run the statement from one of two places:

  • At the command prompt
  • In a configuration file (ConfigurationFile.ini)

Packaging SQL Server 2008 Express with Your Application

It is recommended that you ship the Express package extracted on your media and then launch Setup.exe directly. To extract the Express package, run the following command.

{Express package} /X:{Directory to extract to}

Command Prompt Vs. Configuration File

Even though both the SQL Server 2008 Express command prompt and the configuration file installation methods provide similar functionality (that is, they have the same parameters), their implementation differs. The ConfigurationFile.ini stores the user input settings for the specific installation (public settings applicable to the current installation).

You can use the configuration file to restart the installation using the user settings from an earlier setup. The only settings not saved in the configuration file are the passwords for the accounts and the product ID (PID). When necessary, you can add these parameters through the configuration file, at a command prompt, or through a user interface prompt.

Installing SQL Server Express from the Command Prompt

When installing new instances or upgrading existing instances of SQL Server at the command prompt, you can specify the features to install and configure. You can also specify silent, basic, or full interaction with the setup user interface. Your installation requirements will determine setup interaction type.

For more information, see How to: Install SQL Server 2008 from the Command Prompt (https://msdn.microsoft.com/en-us/library/ms144259.aspx).

Performing a New Installation of SQL Server 2008 Express

An example of performing a new installation from the command prompt:

Setup.exe /q /Action=Install /Hideconsole /Features=SQL,Tools
/InstanceName=SQLExpress /SQLSYSADMINACCOUNTS="Builtin\Administrators"
/SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>

In the preceding example:

  • /q – specifies that Setup run in a quiet mode without any user interface.
  • /Action – specifies which action to perform. In this example, the action is Install.
  • /Hideconsole – specifies that the console window is hidden or closed during the install.
  • /Features – specifies which parent features and features to install. In this example, the parent feature SQL is installed, which includes SQLEngine, Replication, and Fulltext components. The Tools feature installs all of the tools components.
  • /InstanceName – specifies a SQL Server instance name.
  • /SQLSYSADMINACCOUNTS –provisions logins to be members of the system administrators role.
  • /SQLSVCACCOUNT – specifies the startup account for the SQL Server service.
  • /SQLSVCPASSWORD – specifies the password for SQLSVCACCOUNT.

The following table contains the list of parameters that are available to SQL Server 2008 Express, which is a partial list of all of the parameters in SQL Server 2008. The parameters marked with an X are typical for SQL Server 2008 Express embedded installations. The parameters with no X are not typically used for common SQL Server Express installations.

Parameter Description Typical Parameter

/AddCurrentUserAsSQLAdmin

Required

This will provision the database engine with the user running setup. Use this parameter if you want to provision the user running setup. If you want to provision other users, use the /SQLSYSADMINACCOUNTS.

 

This parameter is not required if /SQLSYSADMINACCOUNTS is specified.

X

/ACTION

Required

Required to indicate the installation workflow.

Supported values:

  • Install

X

/CONFIGURATIONFILE

Optional

Specifies the configuration file to use.

/ERRORREPORTING

Optional

Specifies the error reporting for SQL Server.

For more information, see Privacy Statement for the Microsoft Error Reporting Service (http://oca.microsoft.com/en/dcp20.asp).

Supported values:

1=enabled

0=disabled (Default)

/FEATURES

Required

Specifies components to install:

  • SQL installs SQLEngine , Replication, and FullText components
  • RS installs Reporting Services
  • Tools installs all tool components

X

/INSTALLSHAREDDIR

Optional

Specifies a nondefault installation directory for 64-bit shared components.

/INSTALLSHAREDWOWDIR

Optional

Specifies a nondefault installation directory for 32-bit shared components. Supported only on a 64-bit system.

/INSTANCEDIR

Optional

Specifies a nondefault installation directory for instance-specific components.

/INSTANCENAME

Required

Specifies a SQL Server instance name.

For more information, see Instance Configuration(https://msdn.microsoft.com/en-us/library/ms143531.aspx).

X

/Q

Optional

Specifies that Setup run in a quiet mode without displaying user interface messages or requiring user input. Used for unattended installations.

X

/QS

Optional

Specifies that Setup runs and shows progress through the UI, but does not accept any user input or display any error messages.

/SQMREPORTING

Optional

Specifies feature usage reporting for SQL Server. For more information, see Privacy Statement for the Microsoft Error Reporting Service.

Supported values:

  • 1=enabled
  • 0=disabled (default)

/HIDECONSOLE

Optional

Specifies that the console window is hidden or closed. If not specified, the console stays open while Setup runs, which is usually not preferred.

X

/ENABLERANU

Optional

Enables run-as credentials for SQL Server Express installations. This option is disabled by default.

X

/INSTALLSQLDATADIR

Optional

Specifies the data directory for SQL Server data files. Default values are:

  • For WOW mode on 64-bit: %Program Files(x86)%\Microsoft SQL Server\
  • For all other installations: %Program Files%\Microsoft SQL Server\

/SAPWD

Required when /SECURITYMODE=SQL

Specifies the password for the SQL Server system administrator account.

X

/SECURITYMODE

Optional

Specifies the security mode for SQL Server.

If this parameter is not supplied, the default of Windows-only authentication mode is applied. The supported value is SQL

X

/SQLBACKUPDIR

Optional

Specifies the directory for backup files.

The default value is

<InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Backup

/SQLCOLLATION

Optional

Specifies the collation settings for SQL Server.

The default value is

SQL_Latin1_General_CP1_CS_AS

/SQLSVCACCOUNT

Required

Specifies the startup account for the SQL Server service.

X

/SQLSVCPASSWORD

Required

Specifies the password for SQLSVCACCOUNT. (This is required only if a local account or domain account is used.)

X

/SQLSVCSTARTUPTYPE

Optional

Specifies the startup mode for the SQL Server service. Supported values are:

  • Automatic
  • Disabled
  • Manual (default)

X

/RSSVCACCOUNT

Required

Specifies the startup account for the Reporting Server service. This is available only in the Express Advanced Package.

/RSSVCPASSWORD

Required

Specifies the password for Reporting Server Service. This is available only in the Express Advanced Package. (It is required only if a local account or domain account is used.)

/RSSVCSTARTUPTYPE

Optional

Specifies the startup mode for the Reporting Server Service. Supported values are:

  • Automatic
  • Disabled
  • Manual (default)

This is available only in the Express Advanced Package.

/SQLSYSADMINACCOUNTS

Required

Provisions logins to be members of the sysadmin role.

This parameter is not required if /AddCurrentUserAsSQLAdmin is specified.

/SQLTEMPDBDIR

Optional

Specifies the directory for the data files for tempdb. The default value is <InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data

/SQLTEMPDBLOGDIR

Optional

Specifies the directory for the log files for tempdb. The default value is

<InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data

/SQLUSERDBDIR

Optional

Specifies the directory for the data files for user databases. The default value is <InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data

/SQLUSERDBLOGDIR

Optional

Specifies the directory for the log files for user databases. The default value is <InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data

/USESYSDB

Optional

Specifies the location of the SQL Server system databases to use for this installation.

Do not include the \Data suffix in the specified path.

/FILESTREAMLEVEL

Optional

Specifies the access level for the FILESTREAM feature.

Supported values are:

  • 0 =Disable FILESTREAM support for this instance. (This is the default value.)
  • 1=Enable FILESTREAM for Transact-SQL access.
  • 2=Enable FILESTREAM for Transact-SQL and file I/O streaming access. (This is not valid for cluster scenarios.)
  • 3=Allow remote clients to have streaming access to FILESTREAM data.

/FILESTREAMSHARENAME

Optional

Required when FILESTREAMLEVEL is greater than 1.

Specifies the name of the Windows share on which the FILESTREAM data will be stored.

/FTSVCACCOUNT

Optional

Specifies the account for Full-Text filter launcher service. The default value is Local Service Account.

This parameter is ignored in Windows Server® 2008 and Windows Vista® operating systems. ServiceSID is used to help secure the communication between SQL Server and the full-text filter daemon. If the values are not provided, the FDHOST Launcher service, which is used to the filter daemon host process, is disabled. Use SQL Server Control Manager to change the service account and enable full-text functionality.

/FTSVCPASSWORD

Optional

Specifies the password for the Full-Text filter launcher service.

This parameter is ignored in the Windows Server 2008 and Windows Vista operating systems.

/NPENABLED

Optional

Specifies the state of the Named Pipes protocol for the SQL Server service. Supported values are:

  • 0=disable the Named Pipes protocol.
  • 1=enable the Named Pipes protocol.

Note: To enable remote connections, you need to enable the NPENABLED or TCPENABLED parameters.

/TCPENABLED

Optional

Specifies the state of the TCP protocol for the SQL Server service. Supported values are:

  • 0=disable the TCP protocol.
  • 1=enable the TCP protocol.

Note: To enable remote connections, you need to enable the NPENABLED or TCPENABLED parameters.

Table 2: Parameters for performing a new installation of SQL Server 2008 Express

Performing a Basic Upgrade to SQL Server 2008 Express

Although an upgrade from SQL Server 2005 Express is straightforward, there are important differences between MSDE and SQL Server Express that you must understand before creating an upgrade plan. For more information about upgrading to SQL Server 2008 Express, see the Ultimate guide for upgrading to SQL Server 2008 (https://blogs.technet.com/dataplatforminsider/archive/2008/12/04/ultimate-guide-for-upgrading-to-sql-server-2008.aspx) and refer to Chapter 10, “Upgrading to SQL Server 2008 Express”.

Here’s an example of performing a basic upgrade:

Setup.exe /q /Hideconsole /ACTION=upgrade /INSTANCENAME=SQLExpress

The following table contains a list of the input parameters used for upgrading to SQL Server 2008 Express.

Parameter Description Typical Parameter

/ACTION

Required

Required to indicate the installation workflow.

The supported value is Upgrade.

X

/CONFIGURATIONFILE

Optional

Specifies the configuration file to use.

/ERRORREPORTING

Optional

Specifies the error reporting for SQL Server. For more information, see Privacy Statement for the Microsoft Error Reporting Service.

 Supported values are:

  • 1=enabled
  • 0=disabled (default)

X

/ INSTANCEDIR

Optional

Specifies a nondefault installation directory for shared components

/INSTANCENAME

Required

Specifies a SQL Server instance name. For more information, see Instance Configuration.

X

/Q

Optional

Specifies that Setup run in a quiet mode without any user interface. Use this parameter for unattended installations.

X

/SQMREPORTING

Optional

Specifies feature usage reporting for SQL Server. For more information, see Privacy Statement for the Microsoft Error Reporting Service. Supported values are:

  • 1=enabled
  • 0=disabled (default)

/HIDECONSOLE

Optional

Specifies the console window is hidden or closed. If a value is not specified, the console stays open while the setup process is running, which usually is not the preferred option.

X

/BROWSERSVCSTARTUPTYPE

Optional

Specifies the startup mode for the SQL Server Browser service. Supported values are:

  • Automatic
  • Disabled
  • Manual

/FTUPGRADEOPTION

Optional

Specifies the full-text catalog upgrade option. Supported values are:

  • REBUILD
  • RESET
  • IMPORT

Table 3: All parameters for performing an upgrade

Installing SQL Server Express with a Configuration File (ConfigurationFile.ini)

When installing new instances or upgrading existing instances of SQL Server at the command prompt, you can specify the features to install and configure. You can also specify silent, basic, or full interaction with the setup user interface. Your installation requirements will determine setup interaction type.

Using a configuration file allows you to store all of the installation or upgrade parameters in a single, more organized location. This option also provides a level of encapsulation allowing the standardized use of a single configuration file or multiple configuration files for more diverse deployment requirements.

The following configuration file example performs the same functionality as the preceding command-line installation example in order to better illustrate the differences between the methods.

Configuration file example:

;SQLSERVER2008 Configuration File [SQLSERVER2008]
; Setup will not display any user interface.
QUIET="True"

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="Install"

; Specifies that the console window is hidden or closed.
HIDECONSOLE="True"

; Specifies features to install, uninstall, or upgrade. The list of top-level
features include SQL, AS, RS, IS, and Tools. The SQL feature will install the
Database Engine, replication, and full-text. The Tools feature will install
Management Tools, SQL Server Books Online, Business Intelligence Development Studio, and other
shared components.
FEATURES=SQL, TOOLS

; Specify a default or named instance. MSSQLSERVER is the default instance for non-
Express editions, and SQLExpress is the default instance for Express editions. This parameter is required when
installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting
Services (RS).
INSTANCENAME="SQLEXPRESS"

; Windows account(s) to provision as SQL Server system administrators.
SQLSYSADMINACCOUNTS="Builtin\Administrators"

; Account for SQL Server service: Domain\User or system account.
/SQLSVCACCOUNT="<DomainName\UserName>"

; Specifies the password for SQLSVCACCOUNT
/SQLSVCPASSWORD="<StrongPassword>

Another example of a configuration file can be found on any computer with SQL Server 2008 Express installed at the default location of C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<timestamp folder>\ConfigurationFile.ini.

For more information about using the configuration file, see How to: Install SQL Server 2008 Using a Configuration File (https://msdn.microsoft.com/en-us/library/dd239405.aspx).

<span id="_Toc237912070">

To ensure a successful and user-friendly installation, your installation application must trap and process SQL Server 2008 Express exit codes. These codes allow you to take corrective action for managing a new installation or upgrade of SQL Server 2008 Express.

Except for the "Success but Reboot Required" error, if setup returns a nonzero error code:

  • A rule failed during setup.
  • Setup was unable to handle an installation failure.

If a rule failed, address the cause for rule failure and restart the setup program. If there is an unhandled failure, view the log files to understand the cause. For more information about the log files, see “Understanding the SQL Server Log Files” later in this paper.

Refer to the following table for a list of exit codes for common installation failure points. Consider the list as a core set of codes for the minimum basic checks for an installation. For a larger list of exit codes in the form of rules, see “Rule Failures.”

Exit Code Meaning

0

Successful installation.

0xBC2

Successful installation; however, a system reboot is required.

It means if hex exit code ends with BC2:

0x8XXX0BC2

A system reboot required before the installation can continue.

0x84c408

.NET is required.

84c40010

Microsoft Windows Installer 4.5 is required.

Table 4: Exit Codes Returned by Setup

Rule Failures

Rules provide a greater degree of functionality and control, which allows for a more managed and polished installation. In addition, rule failures populate the error logs, which provide detailed information for investigating any installation issues.

Three categories of rules exist. Each category represents a processing or execution phase for a typical setup request:

  1. Global rules check
  2. Component update
  3. User-requested action

Each of these phases generates both a detail and a summary log, which may generate additional log files when required. User-requested Setup actions call the Setup application at least three times during a product installation. For more information, see “Understanding the SQL Server Log Files” later in this paper.

The following table lists the rule failures you can check during an installation.

Rule Description Failure code

OsVersionCheck

Checks if the computer meets minimum operating system version requirements.

0x84BE0001

ThreadHasAdminPrivilegeCheck

Checks if the account running SQL Server Setup has administrator rights on the computer.

0x84BE0007

RebootRequiredCheck

Checks if a pending computer restart is required; a pending restart can cause setup to fail.

0x84BE0BC2

WmiServiceStateCheck

Checks if the WMI service has started and is running on the computer.

0x84BE0003

AclPermissionsFacet

Checks if the SQL Server registry keys are consistent.

0x84BE01FF

MediaPathLength

Checks if the SQL Server installation media is not too long.

0x84BE0009

FusionRebootCheck

Checks if a computer restart is required because of broken fusion ATL; a pending restart can cause setup to fail.

0x84BE0BC2

SqlUnsupportedProductBlocker

Checks if SQL Server 7.0 or SQL Server 7.0 OLAP Services is installed; SQL Server 2008 is not supported with SQL Server 7.0.

0x84BE020D

PerfMonCounterNotCorruptedCheck

Checks if the existing performance counter registry hive is consistent.

0x84BE0004

Bids2005InstalledCheck

Checks for previous releases of SQL Server 2008 Business Intelligence Development Studio.

0x84BE0005

BlockInstallSxS

Checks if there is an existing SQL Server 2008 Community Technology Preview (CTP) installation.

0x84BE01FA

FacetDomainControllerCheck

Checks if the computer is a domain controller; installing SQL Server 2008 on a domain controller is not recommended.

0x84BE0201

SSMS_IsInternetConnected

Verifies that the computer is connected to the Internet. If a Microsoft .NET application such as Microsoft Management Studio starts, a delay may occur while the .NET security check validates a certificate.

0x84BE0BD1

FacetWOW64PlatformCheck

Determines whether SQL Server Setup is supported on this operating system platform.

0x84BE0213

FacetPowerShellCheck

Checks if Windows PowerShell® is installed; Windows PowerShell is a prerequisite of Microsoft SQL Server 2008 Express with Advanced Services.

0x84BE0214

IsFirewallEnabled

Checks if the Windows Firewall is enabled.

0x84BE0BD2

BlockMixedArchitectureInstall

Checks if the installing features are the same CPU architecture as the specified instance.

0x84BE0202

BlockCrossLanguageInstall

Checks if the setup language is the same as the language of existing SQL Server features.

0x84BE0205

StandaloneInstall_HasClusteredOr
PreparedInstanceCheck

Checks if the selected instance name is already used by an existing cluster-prepared or clustered instance on any cluster node.

0x84BE0207

RS_DoesCatalogExist

Checks if the Reporting Services catalog database file exists.

0x84BE03F4

RS_DoesCatalogTempDBExist

Checks if the Reporting Services catalog temporary database file exists.

0x84BE03F5

Sql2005SsmsExpressFacet

Checks if SQL Server 2005 Express tools are installed.

0x84BE0218

EditionRequirementCheck

Checks if the SQL Server edition is supported on the existing operating system

0x84BE0219

FAT32FileSystemCheck

Checks if the specified drive is a FAT32 file system volume; installing on a FAT32 file system is supported but not recommended because it is less secure than the NTFS file system

0x84BE0249

LibertyASInstallRule

Checks if SQL Server 2000 Analysis Services is installed; SQL Server 2000 Analysis Services cannot be installed if the default instance name for SQL Server 2008 is used.

0x84BE024A

InstanceClashRule

Checks if the specified instance name is already used by an existing SQL Server instance.

0x84BE024B

VSShellInstalledRule

Checks for previous releases of Visual Studio 2008.

0x84BE024C

BlockMixedArchitectureUpgrade

Checks if the CPU architecture of feature upgrades is different from the CPU architecture of installed program.

0x84BE0203

ShilohUpgradeRule

Checks if the selected instance of SQL Server 2000 meets minimum upgrade requirements.

0x84BE01F4

LibertyASUpgradeRule

Checks if you must upgrade SQL Server 2000 Analysis Services before you upgrade SQL Server Database Services. SQL Server 2000 Analysis Services must be upgraded before any Database Services named instance.

0x84BE0258

YukonUpgradeSidRule

Checks if the SIDs that are associated with selected features for an upgrade are valid.

0x84BE0217

BlockCrossLanguageUpgrade

Checks if the setup language is the same as the language of the SQL Server feature upgrades.

0x84BE0200

KatmaiBuildToBuildUpgradeRule

Checks if the selected instance of SQL Server 2008 meets the minimum requirement for a build-to-build upgrade.

0x84BE01F8

RS_ValidDSN

Checks if the Report Server has a valid DSN.

0x84BE03E9

RS_ValidDatabaseVersion

Checks if the Report Server database version can be used by the SQL Server 2008 Report Server.

0x84BE03EA

RS_NoCustomRenderingExtensions

Checks if Report Server has any custom rendering extensions configured.

0x84BE03EB

RS_NoCustomSecurityExtensions

Checks if Report Server has any custom security extensions configured.

0x84BE03EC

RS_NoCustomAuthExtensions

Checks if Report Server has any custom authentication extensions configured.

0x84BE03ED

RS_ReportServerUnsupportedSecurityMode

Checks if Report Server is using any unsupported Microsoft Internet Information Services (IIS) security modes.

0x84BE03EE

RS_ReportManagerUnsupported
SecurityMode

Checks if Report Manager is using any unsupported IIS security modes.

0x84BE03EF

RS_ReportServerClientCertificate
Required

Checks if Report Server is required to use client certificates.

0x84BE03F0

RS_ReportManagerClientCertificate
Required

Checks if Report Server is required to use client certificates.

0x84BE03F1

RS_RS2000SP2Required

Checks if SQL Server 2000 Reporting Services Service Pack 2 (SP2) is installed.

0x84BE03F2

RS_RSServiceRunning

Checks if the Reporting Services service is running when the clustered instance is being upgraded.

0x84BE03F3

Engine_SqlServerServiceDisabled_Id

Checks if the SQL Server service is not set as Disabled.

0x84BE07D1

Engine_SqlEngineHealthCheck

Checks if the SQL Server service can be restarted; or for a clustered instance, whether the SQL Server resource is online.

0x84BE07D5

Engine_AllSystemDatabases
AccessibleCheck

Checks if all system databases are accessible.

0x84BE07D4

Engine_UserHasNotDefinedSchema
SysCheck

Checks if the user has defined a schema named 'sys'.

0x84BE07D6

Engine_FilestreamAndRcsiDatabasesCheck

Checks for databases with FILESTREAM file groups and READ_COMMITTED_SNAP
SHOT or ALLOW_SNAPSHOT_
ISOLATION enabled.

0x84BE07DC

Engine_ResourceDLLUpdateRestart
Check

Checks for shared resource DLL updates, which cause restarts for clustered SQL Server instances active on this node.

0x84BE07E1

ShilohServiceAccountUpgradeRule

Checks if SQL Server 2000 service accounts meet upgrade requirements.

0x84BE0204

Engine_ServiceAccountOnDomain
Check

Checks if the SQL Server service account when running on a domain controller is suitable for upgrade.

0x84BE07D3

Cluster_MultipleGroupsUpgradeRule

Checks if the selected clustered instance of SQL Server 2005 is installed into multiple groups.

0x84BE0BC9

Cluster_BlockLibertyUpgrade

Checks if the instance selected for upgrade is a clustered SQL Server 2000 64-bit instance.

0x84BE0BCB

FeatureUpgradeMatrixCheck

Checks if the specified feature meets SQL Server 2008 upgrade requirements.

0x84BE0212

IncompleteUpgradeCheck

Checks if the upgrade operation completed successfully.

0x84BE020E

FailedUpgradeCheck

Checks if a previous upgrade failed.

0x84BE020F

LocalOnly_SqlFeatureStateCheck

Checks if the SQL Server Database Services feature upgrade was successfully configured.

0x84BE0215

LocalOnly_AsFeatureStateCheck

Checks if the SQL Server Analysis Services feature upgrade was successfully configured.

0x84BE0216

RsFeatureStateCheck

Checks if the SQL Server Reporting Services feature upgrade was successfully configured.

0x84BE0217

Table 5: Rule failures

<span id="_Toc237912072">

Many of the same security considerations for SQL Server 2005 also apply to SQL Server 2008 Express. For more information about these considerations, see Security Considerations for a SQL Server Installation (https://msdn.microsoft.com/en-us/library/ms144228.aspx).

Consider the following security guidelines when embedding and deploying SQL Server 2008 Express:

  • Service account – It is recommended that you configure the service account under a domain account or a local account. If Windows Vista is used, the recommended setup is to run SQL Server services under the Network Service account. For more information, see (https://msdn.microsoft.com/en-us/library/ms143504.aspx). If installing SQL Server Express on a domain controller, you cannot use the default Network Service account.

  • SQL Server Authentication Mode – During installation, the recommended configuration is to set up SQL Server to enable Windows Authentication mode, which is the default. If the application requirements do not allow this, you need to enable mixed mode authentication.

    If you select mixed mode authentication during setup, you must provide and then confirm a strong password for the built-in SQL Server system administrator account named “sa.” Renaming the sa account is also recommended.

    For more information, see (https://msdn.microsoft.com/en-us/library/ms144284.aspx).

  • Firewall – Consider the following:

    • Firewall systems help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, attempts to connect to SQL Server may be blocked.
    • To access an instance of SQL Server through a firewall, you must configure the firewall on the computer running SQL Server to allow access. You can use the firewall that is included with Windows, or you can install a different firewall.

For more information, see Configuring the Windows Firewall to Allow SQL Server Access (https://msdn.microsoft.com/en-us/library/cc646023.aspx).

<span id="_Toc237912073">

Understanding the SQL Server log files is important when troubleshooting installation issues. If an error occurs during installation, the first step is to review the error log. While error logs of older versions of SQL Server may appear similar, the error logs for SQL Server Setup have changed for this release.

For more information, see How to Troubleshoot SQL Server 2008 Setup Issues (https://support.microsoft.com/kb/955396).

To review the SQL Server error logs, follow these steps:

  1. If an installation error occurs, first find and review the main log located in %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\LOG\Summary.txt
  2. If the main log in step 1 does not exist, find and review: SqlSetup.log in %temp%
  3. If no errors exist in the Summary.txt log or SqlSetup.log, find and review the Detail.log file located in %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\LOG\<session folder> and  search for "error:" You should see the error near this tag.

Log File Details

A SQL Server 2008 Express installation generates many log files in both a new installation and an upgrade. Each set of log files provides varying levels of detail and scope of content.

The log files for an installation are created in %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>. Every time setup runs, it creates a new timestamp directory.

The log file for the unmanaged Setup.exe is located in%temp%\sqlsetup*.log. This log contains the information regarding the execution and steps taken by Setup.exe. The Setup100.exe log is located in the SetupBootstrap\log folder.

All files in the logs folder, with the exception of the unmanaged log, are archived into the Log*.cab file in their respective log folder (a cab generated for each session folder). The following section describes the four categories of log files.

Summary.txt Log File

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\

Purpose: This log is the “clean” and “user-friendly” log file that directs the user to the problem. This file lists all detected SQL Server components, the operating system environment, the specified command-line parameters and values, and the overall status of the Windows Installer (.msi file) and the Windows Installer update package (.msp file) that runs. The Summary.txt log file in this location is the latest one. The following table describes the sections of the log file.

Section Description

Overall Summary:

Brief summary of the execution

Machine properties:

Machine configuration

Discovered product features:

Product features already installed on the machine

Package properties:

Brief description of the product version installed, such as SQL Server 2008

User Input Settings:

Overview of the input settings provided for the scenario

Configuration file:

Configuration file location

Detailed results:

Execution results

Rules with failures:

Failed rules

Global rules:

Global rules

Scenario specific rules:

Scenario specific rules

Rules report file:

Location of rules report file

Table 6: Log file sections

If there are errors, the errors are listed in the overall summary section in the beginning of the file. The feature specific failures are listed in the detailed results.

Summary_{MachineName}_<timestamp>.txt log file

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Summary_ {MachineName}_<_20080503_040551.txt

Purpose: This is the Summary.txt log file for this session.

Summary_{MachineName}_<timestamp>_ComponentUpdate.txt log file

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\ Summary {MachineName}_20080503_040551_ComponentUpdate.txt

Purpose: This is similar to the summary file that is generated during the component update work flow.

Summary_{MachineName}_<timestamp>_GlobalRules.txt log file

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Summary_{MachineName}_20080503_040551_GlobalRules.txt

Purpose: This is similar to the summary file generated during the global rules workflow.

Detail.txt Log File

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Detail.txt

Purpose: This log file contains the results from processing the global rules and component update, and it provides a detailed log of the execution. The Detail.txt log file organizes the line entries in the order of the timestamps and the file extensions (module optional) that produce the log. This log file is important for identifying any failures, because it generates the logs based on the time the actions are invoked. You can use this file to determine the step-by-step execution process, the order in which actions execute, and the dependencies between actions. This file is generated for the main workflow (similar to an installation or upgrade).

If an error occurs in the setup process, the installation program logs exceptions or errors when they occur. To find errors in this file, you can find the exception or error at the end of the file. Then search the file for “error” or “exception” keywords, which will help you determine when and where the error occurred.

Example of a failure in the Detail.txt log:

2009-05-12 10:26:26 Slp: Error: Action "RunFeatureSpecificRules" threw an exception during execution.
2009-05-12 10:26:27 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008. ---> Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngineRuleFailureException: A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008.
2009-05-12 10:26:27 Slp:    at Microsoft.SqlServer.Configuration.RulesEngineExtension.RunRulesAction.ExecuteAction(String actionId)
2009-05-12 10:26:27 Slp:    at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
2009-05-12 10:26:27 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
2009-05-12 10:26:27 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
2009-05-12 10:26:27 Slp:    --- End of inner exception stack trace ---
2009-05-12 10:26:27 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
2009-05-12 10:26:32 Slp: Received request to add the following file to Watson reporting: C:\Users\petersad\AppData\Local\Temp\tmpDEE9.tmp
2009-05-12 10:26:32 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2009-05-12 10:26:32 Slp: Inner exceptions are being indented
2009-05-12 10:26:32 Slp:
2009-05-12 10:26:32 Slp: Exception type: Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngineRuleFailureException
2009-05-12 10:26:32 Slp:     Message:
2009-05-12 10:26:32 Slp:         A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008.
2009-05-12 10:26:32 Slp:     Data:
2009-05-12 10:26:32 Slp:       DisableWatson = true
2009-05-12 10:26:32 Slp:     Stack:
2009-05-12 10:26:32 Slp:         at Microsoft.SqlServer.Configuration.RulesEngineExtension.RunRulesAction.ExecuteAction(String actionId)
2009-05-12 10:26:32 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
2009-05-12 10:26:32 Slp:         at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
2009-05-12 10:26:32 Slp:         at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
2009-05-12 10:26:34 Slp:
2009-05-12 10:26:34 Slp: ----------------------------------------------------------------------
2009-05-12 10:26:34 Slp:
2009-05-12 10:26:34 Slp: Error result: -2067922356
2009-05-12 10:26:34 Slp: Result facility code: 1214
2009-05-12 10:26:34 Slp: Result error code: 588

Detail_ComponentUpdate.txt Log File

Location:  %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Detail_ComponentUpdate.txt

Purpose: Similar to the Detail.txt log file, this file is generated for the component update workflow.

Detail_GlobalRules.txt Log File

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Detail_GlobalRules.txt

Purpose: Similar to the Detail.txt log file, this file is generated for the global rules execution.

MSI Log Files

  • <Feature>_<Architecture>_< Iteration >.log
  • <Feature>_<Architecture>_<Language>_< Iteration >.log
  • <Feature>_<Architecture>_<Iteration>_<workflow>.log

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\<Name>.log

Purpose: The MSIEXEC package installation process generates details in the MSI log files. To find errors in the MSI log files, search for “value 3” (English MSI version only); the error is usually near this string. At the end of the file, is a summary of the execution (pass or fail status and other properties). This does not apply for initialization failures, which are logged in a different way.

ConfigurationFile.ini

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Configurationfile.ini

Purpose: This file contains the input settings specific to an installation (public settings that apply to the current setup). The configuration file stores and uses settings provided by the user to restart the installation. This eliminates the need to enter the same parameter values again. Passwords for the accounts and the PID are the only settings not saved in the configuration file. You can add these parameter values later to the configuration file, or you can provide them at a command prompt or in the UI.

SystemConfigurationCheck_Report.htm log file

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\SystemConfigurationCheck_Report.htm

Purpose: Contains a user-friendly version of the rules execution status. It also provides a short description of each executed rule.

<span id="_Toc237912085">

SQL Server 2008 Express is designed to provide a lightweight and easy-to-use way to include powerful SQL Server functionality in your applications. a successful, seamless installation or upgrade in your SQL Server 2008 Express application. When embedding SQL Server 2008 Express in an application, it is important for the new installation or upgrade to be successful and seamless. This paper provides examples, tips, and troubleshooting information to help you achieve that goal.

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.

To send feedback on this white paper, visit the Microsoft Connect Site.