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.

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

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

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

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

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

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.

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.

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

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.

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.

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.

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.

Using the WPI Executable

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

Examples:

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:

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:

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")

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

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.

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.

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.

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.

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

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

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

• Anonymous
  September 18, 2013
  Quite informative Ed..thanks

• Anonymous
  November 14, 2014
  Great source and great flow of information...Thanks for sharing Ed.

• Anonymous
  June 07, 2015
  You're welcome everyone! Glad this can be helpful!

• Anonymous
  July 06, 2015
  Very Useful information...

• Anonymous
  December 21, 2015
  Larry and I thank you, Dinesh!

• Anonymous
  January 31, 2016
  Computers Today (part 1 of 6) blogs.msdn.com/.../computers-today.aspx ..... CS SPOTLIGHT: Girls in computer programming... why it matters!!! blogs.msdn.com/.../cs-spotlight-girls-in-computer-programming-why-it-matters.aspx ... Computational Thinking - Videos & Papers by Jeannette Wing blogs.msdn.com/.../computational-thinking-videos-amp-papers-by-jeannette-wing.aspx

• Anonymous
  October 03, 2016
  ignore compatibility message sql server 2008 with registry in case silent installWindows Registry Editor Version 5.00[HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags]"{22fbe76c-ce43-4588-9a1e-f09b342d6179}"=dword:00000004

  • Anonymous
    January 06, 2017
    If you're running into an error, did you ask in the SQL Server forum?Thanks!

• Anonymous
  November 06, 2017
  SQL Express should be integrated in windows. And we shouldn't have to do anything special if we need to communicate in Microsoft OS with Microsoft DB.

  • Anonymous
    January 18, 2018
    Great insights, Hossein. I believe you can send feedback to the team on their Connect site.