How to Embed SQL Server Express in an Application

Today's featured TechNet Wiki article can be found here. It was originally authored by Larry Franks.

===============

Which Edition of SQL Server Express to Use

There are several editions of SQL Server Express, from a database only install to database, advanced services, and manageability tools installation.

SQL Server 2008R2 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

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

SQL Server 2008R2 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 2008R2 Express (Runtime Only)

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

SQL Server 2008R2 Management Studio Express (SSMSE)

• Free graphical management tool for configuring, managing, and administering SQL Server 2008R2 Express applications
• Also use for managing multiple instances of the SQL Server Database Engine created by any edition of SQL Server 2008R2, including Workgroup, Web, Standard, and Enterprise editions

NOTE:

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

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

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

How to Register for SQL Server Express Redistribution Rights

Before embedding SQL Server 2008R2 Express in your application, you must obtain a license in order to redistribute the Express edition.  The license is free and can be obtained by going to the license registration page (https://www.microsoft.com/sqlserver/2008/en/us/express/redistregister.aspx).

How to Detect Previous Installations of SQL Server

Before performing an installation of SQL Server, you should first check to see if SQL Server is already installed on the target computer. The recommended way to check for an installation of SQL Server, and what instances are present is to use WMI.  The VB.NET and C# code demonstrates how to accomplish this can be found here.

NOTE:

When checking for previous installations of SQL Server, you must check for both the root\Microsoft\SqlServer\ComputerManagement and root\Microsoft\SqlServer\ComputerManagement10 namespaces;  SQL Server 2005 instances use the root\Microsoft\SqlServer\ComputerManagement namespace, while SQL Server 2008 use root\Microsoft\SqlServer\ComputerManagement10.

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

The actions to take if a previous installation of SQL Server is found depend on your application needs.  If your application uses a specific named instance and one already exists, this may indicate that the application was previously installed. In this case you may need to perform verification on the database, delete, or upgrade it. If an older version of SQL Server 2008R2 Express is found, you may wish to perform an upgrade installation of Express, or if your application supports multiple versions of Express you might continue installation by performing version specific installation steps.

How to Embed SQL Server Express

There are two recommended ways to include SQL Server Express as part of your application:

1. Install SQL Server Express by using Web Platform Installer (WPI)
2. Include the SQL Server Express package on your distribution media and invoke setup.exe directly

NOTE:

The Web Platform Installer does not currently provide a way to upgrade an existing installation of SQL Server.

While both methods provide a method of installing SQL Server 2008R2 Express as part of your application, there are different considerations for each. Before committing to one or the other, carefully review the following information.

Feature/Requirement	Windows Platform Installer	Setup.exe installation
User interaction during setup	Minimal	None to highly complex
Customizable installation options	None – default configuration options	Highly customizable
Complexity of integration with application setup	Minimal	Minimal to highly complex
Upgrade over previous versions	No	Yes
Requires SQL Server 2008 Express files on your application installation media	No – can install over an Internet connection	Yes
Exit codes that provide success or failure of installation	?	Yes

Directly invoking setup.exe requires either the application developer to specify the options to be used during setup, or the user performing the installation to select them.

Installation Using the Web Platform Installer

The Web Platform Installer can be used to install SQL Server Express with a minimum of user intervention, using the most common settings as the default. There are several ways to invoke the Web Platform Installer that will result in an installation of SQL Server Express:

• Using WPI executable (WebPlatformInstaller.exe)
• Using WPI handler (wpi://)
• Using Web App Gallery (https://www.microsoft.com/web/gallery/install.aspx)

Of the three methods, using the Web App Gallery is the recommended method, as this will not only install SQL Server Express, but will also install the Web Platform Installer if it is not already present on the computer.

NOTE:

While the Web Platform Installer normally downloads and installs SQL Server 2008R2 Express from the internet, there is a way to force it to install the files from a local file system. For more information see Is there a way to get WebPI to install Products in an offline way.

When installing Express with WPI, you can select the edition of SQL Server 2008R2 Express to be installed by specifying a product ID value as a parameter to WPI.  The available editions, their features, and the associated product ID values are listed in the following table.

SQL Server 2008R2 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
Product ID	SQLManagementStudio	SQLExpress	SQLExpressTools	SQLExpressAdv

Using the WPI Executable

Syntax: WebPlatformInstaller.exe /id <product id>[&<product id>…][?<file id>[&<file id>…]][?<language id>]

Examples:

Command	Result
WebPlatformInstaller.exe /id SQLExpress	Installs SQL Server Express
WebPlatformInstaller.exe /id SQLExpressAdv	Installs SQL Server Express with Advanced Services

C#

 System.Diagnostics.Process.Start(@"C:\Program Files\Microsoft\Web Platform Installer\webplatforminstaller.exe"," /id SQLExpress");

VB.NET

 System.Diagnostics.Process.Start("C:\Program Files\Microsoft\Web Platform Installer\webplatforminstaller.exe"," /id SQLExpress")

C++

 STARTUPINFO si;

 PROCESS_INFORMATION pi;

 ZeroMemory( &si, sizeof(si) );

 si.cb = sizeof(si);

 ZeroMemory( &pi, sizeof(pi) );

 CreateProcess(L"C:\\Program Files\\Microsoft\\Web Platform Installer\\webplatforminstaller.exe",

     L" /id SQLExpress", NULL, NULL, FALSE, 0, NULL, NULL, &si, &pi);

Using WPI Handler

Syntax: wpi://<product id>[&<product id>…][?<file id>[&<file id>…]][?<language id>]

Examples:

Command	Result
wpi://SQLExpress	Installs SQL Server Express
wpi://SQLExpressAdv	Installs SQL Server Express with Advanced Services

C#

 System.Diagnostics.Process.Start("wpi://SQLExpress/");

VB.NET

 System.Diagnostics.Process.Start("wpi://SQLExpress/")

Using the Web App Gallery

Syntax: https://www.microsoft.com/web/gallery/install.aspx?\[appsxml=\<file id>][&appsxml=<file id>…]appid=<product id>[%3b<product id>…][&applang=<language id>]

Examples:

Command	Result
https://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=SQLExpress	Installs SQL Server Express
https://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=SQLExpressAdv	Installs SQL Server Express with Advanced Services

C#

 System.Diagnostics.Process.Start("https://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=SQLExpress");

VB.NET

 System.Diagnostics.Process.Start("https://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=SQLExpress")

Advanced Installation (Setup.exe)

In cases where greater control over the SQL Server Express installation options is needed, you can provide the extracted Express setup files with your application and launch setup.exe directly.

To extract the SQL Server 2008R2 Express package downloaded from https://www.microsoft.com/express/sql/download, run the following command:

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

Example:

SQLEXPRWT_x86_ENU /X:c:\ExpressSetup

Installation Options

When installing SQL Server Express using setup.exe, you can specify configuration options either using parameters passed to setup.exe or by using a configuration file.  Even though both the setup.exe parameters and the configuration file installation methods provide similar functionality (that is, they have the same available options), 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.

Examples:

Command Prompt

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

C#

 System.Diagnostics.Process processObj = System.Diagnostics.Process.Start(@"c:\temp\sqlsetup\setup.exe",

 @"/q /Action=Install /Hideconsole /IAcceptSQLServerLicenseTerms=True /Features=SQL,Tools /InstanceName=SQLExpress /SQLSYSADMINACCOUNTS=""Builtin\Administrators"" /SQLSVCACCOUNT=""DomainName\UserName"" /SQLSVCPASSWORD=""StrongPassword""");

VB.NET

 System.Diagnostics.Process processObj = System.Diagnostics.Process.Start("c:\temp\sqlsetup\setup.exe",

 "/q /Action=Install /Hideconsole /IAcceptSQLServerLicenseTerms=True /Features=SQL,Tools /InstanceName=SQLExpress /SQLSYSADMINACCOUNTS=""Builtin\Administrators"" /SQLSVCACCOUNT=""DomainName\UserName"" /SQLSVCPASSWORD=""StrongPassword""")

C++

 STARTUPINFO si;

 PROCESS_INFORMATION pi;

 ZeroMemory( &si, sizeof(si) );

 si.cb = sizeof(si);

 ZeroMemory( &pi, sizeof(pi) );

 CreateProcess(L"c:\\temp\\sqlsetup\\setup.exe",

     L" /q /Action=Install /Hideconsole /IAcceptSQLServerLicenseTerms=True /Features=SQL,Tools /InstanceName=SQLExpress /SQLSYSADMINACCOUNTS=\"Builtin\\Administrators\" /SQLSVCACCOUNT=\"DomainName\\UserName\" /SQLSVCPASSWORD=\"StrongPassword\"",

     NULL, NULL, FALSE, 0, NULL, NULL, &si, &pi);

Command Prompt Parameters

In the preceding examples:

• /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.
• /IAcceptSQLServerLicenseTerms - indicates acceptance of the Microsoft SQL Server license terms.
• /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 2008R2 Express, which is a partial list of all of the parameters in SQL Server 2008R2. The parameters marked with an X are typical for SQL Server 2008R2 Express embedded installations. The parameters with no X are not typically used for common SQL Server Express installations. For a full list of all parameters available with SQL Server 2008R2, see https://msdn.microsoft.com/en-us/library/ms144259(v=SQL.105).aspx.

Upgrading to SQL Server Express 2008

You can also use setup.exe to upgrade from a previous version of SQL Server Express; however you should understand the process before proceeding with an upgrade plan. For more information about upgrading to SQL Server 2008R2 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”.

The following is an example of performing a basic upgrade:

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

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

Configuration File (ConfigurationFile.ini)

While specifying parameters at the command prompt allows you to control installation and configuration, using a configuration file allows you to accomplish the same thing but stores all the parameters in one file. By providing multiple configuration files with your application, you can address SQL Server 2008R2 Express configuration needs for a variety of deployment scenarios.

The following is an example configuration file for a basic installation of Express:

 ;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).

Handling Installation Errors

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.

In previous examples, System.Diagnostics.Process.Start has been used to invoke the setup process for SQL Server 2008 Express. The System.Diagnostics.Process namespace can also be used to monitor the exit code of the process when invoking setup.exe in order to determine whether the installation was successful or not.

Examples:

C#

 System.Diagnostics.Process processObj = System.Diagnostics.Process.Start(@"c:\temp\sqlsetup\setup.exe",

 @"/q /Action=Install /Hideconsole /IAcceptSQLServerLicenseTerms=True /Features=SQL,Tools /InstanceName=SQLExpress /SQLSYSADMINACCOUNTS=""Builtin\Administrators"" /SQLSVCACCOUNT=""DomainName\UserName"" /SQLSVCPASSWORD=""StrongPassword""");

 //Loop until the process has exited

 do

 {

      //refresh the process

      processObj.Refresh();

 } while (!processObj.WaitForExit(1000));

 Console.WriteLine("Process exited with {0}!", processObj.ExitCode);

VB.NET

 Dim processObj As Process = Process.Start("c:\temp\sqlsetup\setup.exe", "/q /Action=Install /Hideconsole /Features=SQL,Tools /InstanceName=SQLExpress /SQLSYSADMINACCOUNTS=""Builtin\Administrators"" /SQLSVCACCOUNT=""DomainName\UserName"" /SQLSVCPASSWORD=""StrongPassword""")

 'Loop until process exits

 Do While Not processObj.WaitForExit(1000)

     'refresh process informaton

     processObj.Refresh()

 Loop

 Console.WriteLine("Process exited with {0}!", processObj.ExitCode)

C++

 #include "stdafx.h"

 #include <Windows.h>

 #include <stdio.h>

 #include <tchar.h>

 int _tmain(int argc, _TCHAR* argv[])

 {

     STARTUPINFO si;

     PROCESS_INFORMATION pi;

     ZeroMemory( &si, sizeof(si) );

     si.cb = sizeof(si);

     ZeroMemory( &pi, sizeof(pi) );

     // Create the setup.exe process

     if(!CreateProcess(L"c:\\temp\\sqlsetup\\setup.exe",

         L" /q /Action=Install /Hideconsole /IAcceptSQLServerLicenseTerms=True /Features=SQL,Tools /InstanceName=SQLExpress /SQLSYSADMINACCOUNTS=\"Builtin\\Administrators\" /SQLSVCACCOUNT=\"DomainName\\UserName\" /SQLSVCPASSWORD=\"StrongPassword\"",

         NULL, NULL, FALSE, 0, NULL, NULL, &si, &pi))

     {

     printf( "CreateProcess failed (%d)\n", GetLastError() );

     return 1;

     }

     // Wait until setup exit.

     WaitForSingleObject( pi.hProcess, INFINITE );

     DWORD exitCode=0;

     // Get the exit code

     GetExitCodeProcess(pi.hProcess,&exitCode);

     printf("Exit code (%d)\n", exitCode);

     // Close process and thread handles.

     CloseHandle( pi.hProcess );

     CloseHandle( pi.hThread );

     return 0;

 }

When evaluating the exit code, zero is a success and nonzero indicates either that a prerequisite is missing or that an error occurred during installation. The following table contains a list of the most common exit codes, and represent the minimum basic checks that occur for an installation of SQL Server 2008 Express.

Exit Code	Meaning
0	Successful installation
0xBC2	Successful installation; however a system reboot is required.
0x8XXX0BC2(if the exit code ends with BC2)	A system reboot is required before the installation can continue.
0x84C408	.NET is required
0x84C40010	Microsoft Windows Installer 4.5 is required.

Rule Failures

During installation, the System Configuration Checker processes several rules to ensure that the necessary services, registry keys, OS version, etc. required for SQL Server 2008R2 are present. If a rule fails, an associated failure code is returned as the exit code.

The following table lists the rule failures you can check during an installation. For more information on rules used by the System Configuration Checker, as well as recommended user actions to correct a rule failure, see Check Parameters for the System Configuration Checker. For a list of the rules, go here.

Errors that occur during setup may produce additional detail in the setup log files. This information can be used to discover information about a rule failure or an unhandled failure that is not associated with a rule.  For more information, see How to: View and Read SQL Server Setup Log Files.

Deploy SQL Server Express as a prerequisite with ClickOnce

While you are developing your application using Microsoft Visual Studio, you can add a Setup Project to your solution then specify SQL Server Express as a prerequisite for your application. Then the installation package will install SQL Server Express on the target machine if needed. For a step-by-step example you can refer to How to deploy SQL Server 2008 Express as a prerequisite with ClickOnce.

How to Create, Deploy, or Upgrade your Database

After installing SQL Server 2008R2 Express, you must ensure that the database used by your application is available. Historically this has been accomplished by providing T-SQL scripts to create the database, or to update an older version of the database. While this approach is still valid, SQL Server 2008R2 introduced Data-tier Application (DAC) packages that allows you to treat your database (and associated objects,) as if it were an application, complete with versioning and upgrade capabilities.

Scripting Databases

For more information on creating T-SQL scripts to create or update a database, see Documenting and Scripting Databases and How to: Generate a Script (SQL Server Management Studio) .

Data-Tier Applications

A Data-tier Application (DAC) is an entity that contains all the database objects used by your application.  It provides a single unit for authoring, deploying, and managing these objects, and allows for tighter integration of data-tier and application development.

A DAC is typically authored using Visual Studio 2010 by using the Data-tier Application template, then built and deployed to the database. However you can also export a DAC package from an existing database.

For more information on creating and using Data-tier Applications, see:

• Understanding Data-tier Applications
• Implementing Data-tier Applications
• Creating and Managing Data-tier Applications

---

References

• WebPI Contextual Install
• Is there a way to get WebPI to install Products in an offline way
• Embedding SQL Server 2008 Express in an Application

---

 

=======================

Read the latest version of this Wiki article here, with more table lists and more code samples! There's also a Spanish version of this article that's available on the Wiki page (here).