Intended audience: Application developers who need to distribute SQL Server Express with an application in order to provide data storage using a SQL Server database.
If your application uses SQL Server Express to host its database, you can freely redistribute the SQL Server Express product with your application. This article contains information and links that will enable you to successfully embed SQL Server Express as part of your application installation.
There are several editions of SQL Server Express, from a database only install to database, advanced services, and manageability tools installation. Note that the current SQL Server Express available ( Jan 2015 ) is Sql Server 2014.
SQL Server Database Engine
X
SQL Server Management Studio Basic
Full-Text Search
Reporting Services
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 2008 Express with Advanced Services
SQL Server 2008R2 Express (Runtime Only)
SQL Server Database Engine - for creating, storing, updating, and retrieving data
SQL Server 2008R2 Management Studio Express (SSMSE)
For more information about SQL Server 2008R2 Express, go to the SQL Server 2008R2 Express Web site (http://www.microsoft.com/sqlserver/2008/en/us/express.aspx).
To download SQL Server 2008R2 Express, go to the download page (http://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 (http://www.microsoft.com/sqlserver/2008/en/us/express/redistregister.aspx).
Return to top
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 following VB.NET and C# code demonstrate how to accomplish this.
C#
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 service 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; } } }
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)
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;
/// Method returns the correct SQL namespace to use to detect SQL Server instances.
/// <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;
return wmiNamespaceToUse;
/// method extracts the instance name from the service name
/// <param name="serviceName"></param>
public static string GetInstanceNameFromServiceName(string serviceName)
if (!string.IsNullOrEmpty(serviceName))
if (string.Equals(serviceName, "MSSQLSERVER", StringComparison.OrdinalIgnoreCase))
return serviceName;
return serviceName.Substring(serviceName.IndexOf('$') + 1, serviceName.Length - serviceName.IndexOf('$') - 1);
return string.Empty;
/// Returns the WMI property value for a given property name for a particular SQL Server service Name
/// <param name="serviceName">The service name for the SQL Server engine service 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>
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;
VB.NET
Imports System.Management Module Program Sub Main() If Not EnumerateSQLInstances() Then Console.WriteLine("No instances") End If End Sub ''' <summary> ''' Enumerates all SQL Server instances on the machine. ''' </summary> ''' <returns></returns> Function EnumerateSQLInstances() As Boolean Dim correctNamespace As String = GetCorrectWmiNamespace() If String.Equals(correctNamespace, String.Empty) Then Return False End If Dim query As String = String.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = 'instanceID'") Dim getSqlEngine As New ManagementObjectSearcher With {.Scope = New ManagementScope(correctNamespace), .Query = New ObjectQuery(query)} If getSqlEngine.Get().Count = 0 Then Return False End If Console.WriteLine("SQL Server database instances disovered :") Dim instanceName As String = String.Empty Dim serviceName As String = String.Empty Dim version As String = String.Empty Dim edition As String = String.Empty Console.WriteLine("Instance name {0} ServiceName {0} Edition {0} Version {0}", vbTab) For Each sqlEngine As ManagementObject In getSqlEngine.Get() serviceName = sqlEngine("ServiceName").ToString() instanceName = GetInstanceNameFromServiceName(serviceName) version = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "Version") edition = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "SKUNAME") Console.Write("{0} {1}", instanceName, vbTab) Console.Write("{0} {1}", serviceName, vbTab) Console.Write("{0} {1}", edition, vbTab) Console.WriteLine("{0} {1}", version, vbTab) Next Return True End Function ''' <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> Function GetCorrectWmiNamespace() As String Dim wmiNamspaceToUse As String = "root\Microsoft\SqlServer" Dim namespaces As New List(Of String) Try 'Enumerate all WMI instances of '__namespace WMI class. Dim nsClass As New ManagementClass With {.Scope = New ManagementScope(wmiNamspaceToUse), .Path = New ManagementPath("__namespace")} For Each ns As ManagementObject In nsClass.GetInstances() namespaces.Add(ns("Name").ToString()) Next Catch ex As ManagementException Console.WriteLine("Exception = %1", ex.Message) End Try If namespaces.Count > 0 Then If namespaces.Contains("ComputerManagement10") Then 'use Katmai+ namespace wmiNamspaceToUse = wmiNamspaceToUse + "\ComputerManagement10" ElseIf namespaces.Contains("ComputerManagement") Then 'use Yukon namespace wmiNamspaceToUse = wmiNamspaceToUse + "\ComputerManagement" End If Else wmiNamspaceToUse = String.Empty End If Return wmiNamspaceToUse End Function ''' <summary> ''' method extracts the instance name from the service name ''' </summary> ''' <param name="serviceName"></param> ''' <returns></returns> Function GetInstanceNameFromServiceName(ByVal serviceName As String) As String If Not String.IsNullOrEmpty(serviceName) Then If String.Equals(serviceName, "MSSQLSERVER", StringComparison.OrdinalIgnoreCase) Then Return serviceName Else Return serviceName.Substring(serviceName.IndexOf("$"c) + 1, serviceName.Length - serviceName.IndexOf("$"c) - 1) End If Else Return String.Empty End If End Function ''' <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 service 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> Function GetWmiPropertyValueForEngineService(ByVal serviceName As String, ByVal wmiNamespace As String, ByVal propertyName As String) As String Dim propertyValue As String = String.Empty Dim query As String = String.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = '{0}' and ServiceName = '{1}'", propertyName, serviceName) Dim propertySearcher As New ManagementObjectSearcher With {.Scope = New ManagementScope(wmiNamespace), .Query = New ObjectQuery(query)} For Each sqlEdition As ManagementObject In propertySearcher.Get() propertyValue = sqlEdition("PropertyStrValue").ToString() Next Return propertyValue End Function End Module
Imports System.Management
Module Program
Sub Main()
If Not EnumerateSQLInstances() Then
Console.WriteLine("No instances")
End If
End Sub
''' <summary>
''' Enumerates all SQL Server instances on the machine.
''' </summary>
''' <returns></returns>
Function EnumerateSQLInstances() As Boolean
Dim correctNamespace As String = GetCorrectWmiNamespace()
If String.Equals(correctNamespace, String.Empty) Then
Return False
Dim query As String =
String.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = 'instanceID'")
Dim getSqlEngine As New ManagementObjectSearcher With {.Scope = New ManagementScope(correctNamespace), .Query = New ObjectQuery(query)}
If getSqlEngine.Get().Count = 0 Then
Console.WriteLine("SQL Server database instances disovered :")
Dim instanceName As String = String.Empty
Dim serviceName As String = String.Empty
Dim version As String = String.Empty
Dim edition As String = String.Empty
Console.WriteLine("Instance name {0} ServiceName {0} Edition {0} Version {0}", vbTab)
For Each sqlEngine As ManagementObject In getSqlEngine.Get()
serviceName = sqlEngine("ServiceName").ToString()
instanceName = GetInstanceNameFromServiceName(serviceName)
version = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "Version")
edition = GetWmiPropertyValueForEngineService(serviceName, correctNamespace, "SKUNAME")
Console.Write("{0} {1}", instanceName, vbTab)
Console.Write("{0} {1}", serviceName, vbTab)
Console.Write("{0} {1}", edition, vbTab)
Console.WriteLine("{0} {1}", version, vbTab)
Next
Return True
End Function
''' Method returns the correct SQL namespace to use to detect SQL Server instances.
''' <returns>namespace to use to detect SQL Server instances</returns>
Function GetCorrectWmiNamespace() As String
Dim wmiNamspaceToUse As String = "root\Microsoft\SqlServer"
Dim namespaces As New List(Of String)
Try
'Enumerate all WMI instances of
'__namespace WMI class.
Dim nsClass As New ManagementClass With {.Scope = New ManagementScope(wmiNamspaceToUse), .Path = New ManagementPath("__namespace")}
For Each ns As ManagementObject In nsClass.GetInstances()
namespaces.Add(ns("Name").ToString())
Catch ex As ManagementException
Console.WriteLine("Exception = %1", ex.Message)
End Try
If namespaces.Count > 0 Then
If namespaces.Contains("ComputerManagement10") Then
'use Katmai+ namespace
wmiNamspaceToUse = wmiNamspaceToUse + "\ComputerManagement10"
ElseIf namespaces.Contains("ComputerManagement") Then
'use Yukon namespace
wmiNamspaceToUse = wmiNamspaceToUse + "\ComputerManagement"
Else
wmiNamspaceToUse = String.Empty
Return wmiNamspaceToUse
''' method extracts the instance name from the service name
''' <param name="serviceName"></param>
Function GetInstanceNameFromServiceName(ByVal serviceName As String) As String
If Not String.IsNullOrEmpty(serviceName) Then
If String.Equals(serviceName, "MSSQLSERVER", StringComparison.OrdinalIgnoreCase) Then
Return serviceName
Return serviceName.Substring(serviceName.IndexOf("$"c) + 1, serviceName.Length - serviceName.IndexOf("$"c) - 1)
Return String.Empty
''' Returns the WMI property value for a given property name for a particular SQL Server service Name
''' <param name="serviceName">The service name for the SQL Server engine service 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>
Function GetWmiPropertyValueForEngineService(ByVal serviceName As String, ByVal wmiNamespace As String, ByVal propertyName As String) As String
Dim propertyValue As String = String.Empty
Dim query As String = String.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = '{0}' and ServiceName = '{1}'", propertyName, serviceName)
Dim propertySearcher As New ManagementObjectSearcher With {.Scope = New ManagementScope(wmiNamespace), .Query = New ObjectQuery(query)}
For Each sqlEdition As ManagementObject In propertySearcher.Get()
propertyValue = sqlEdition("PropertyStrValue").ToString()
Return propertyValue
End Module
C++
#include "stdafx.h" #include <comdef.h> #include <iostream> using namespace std; #include <windows.h> #include <assert.h> #include <wbemidl.h> HRESULT InitializeCOMandCOMSecurity(); IWbemServices* GetSQLServerWMIConnection(); void EnumerateSQLInstances(IWbemServices* pSvc); _bstr_t GetInstanceNameFromServiceName(_bstr_t serviceName); _bstr_t GetWmiPropertyValueForEngineService(_bstr_t serviceName, _bstr_t propertyName, IWbemServices* pSvc); int _tmain(int argc, _TCHAR* argv[]) { HRESULT hres; IWbemServices *pSvc = NULL; //initialize COM and COM Security if (FAILED(hres=InitializeCOMandCOMSecurity())) { return hres; } if ((pSvc=GetSQLServerWMIConnection()) == NULL) { return -1; } EnumerateSQLInstances(pSvc); // Cleanup if (pSvc != NULL) pSvc->Release(); CoUninitialize(); return 0; } /// <summary> /// Initializes COM and COM security /// </summary> /// <returns>If COM or COM security initialization fails, a failure code; otherwise, zero.</returns> HRESULT InitializeCOMandCOMSecurity() { HRESULT hres; hres = CoInitializeEx(0, COINIT_MULTITHREADED); // Initialize COM. if (FAILED(hres)) { cout << "Failed to initialize COM library. Error code = 0x" << hex << hres << endl; return hres; // Program has failed. } hres = CoInitializeSecurity(NULL, -1, NULL, NULL, RPC_C_AUTHN_LEVEL_CONNECT, RPC_C_IMP_LEVEL_IMPERSONATE, NULL, EOAC_NONE, 0 ); if (FAILED(hres)) { cout << "Failed to initialize security. Error code = 0x" << hex << hres << endl; CoUninitialize(); return hres; // Program has failed. } return hres; } /// <summary> /// Connects to the SQL Server WMI namespace /// </summary> /// <returns>If successful, a pointer to the IWebmServices object bound to the namespace; otherwise, null</returns> IWbemServices* GetSQLServerWMIConnection() { HRESULT hres; IWbemLocator *pLoc = NULL; IWbemServices *pSvc = NULL; // Get IWbemLocator object which is used to connect to WMI namespaces hres = CoCreateInstance(CLSID_WbemLocator, 0, CLSCTX_INPROC_SERVER, IID_IWbemLocator, (LPVOID *) &pLoc); if (FAILED(hres)) { cout << "Failed to create IWbemLocator object used for namespace connections. Err code = 0x" << hex << hres << endl; return NULL; // Program has failed. } // Attempt to connect to root\Microsoft\SQLServer\ComputerManagement10 (katmai+) _bstr_t bstrNamespace("\\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement10"); hres = pLoc->ConnectServer( bstrNamespace, NULL, NULL, 0, 0, 0, 0, &pSvc ); //if \ComputerManagement10 failed, try \ComputerManagement (yukon) if (FAILED(hres)) { cout << "Could not connect to \\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement10. Error code = 0x" << hex << hres << endl << "Trying \\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement" << endl; bstrNamespace = "\\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement"; hres = pLoc->ConnectServer( bstrNamespace, NULL, NULL, 0, 0, 0, 0, &pSvc ); //if \ComputerManagement failed, return a null if (FAILED(hres)) { cout << "Could not connect to \\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement. Error code = 0x" << hex << hres << endl; pSvc = NULL; } else { cout << endl << "Connected to " << (char*)bstrNamespace << endl << endl; } } else { cout << endl << "Connected to " << (char*)bstrNamespace << endl << endl; } pLoc->Release(); return pSvc; } /// <summary> /// Enumerate SQL Server Instances /// </summary> /// <param name="pSvc">pointer to the IWebmServices object bound to the interface</para> /// <returns></returns> void EnumerateSQLInstances(IWbemServices* pSvc) { _bstr_t bstrWQL(L"WQL"); // WQL to retrieve the instance information _bstr_t bstrQuery(L"SELECT * FROM SqlServiceAdvancedProperty WHERE SQLServiceType = 1 AND PropertyName = 'instanceID'"); _bstr_t bstrTemp; _bstr_t serviceName; _bstr_t instanceName; _bstr_t version; _bstr_t edition; IEnumWbemClassObject* pEnum = NULL; IWbemClassObject* pObject = NULL; ULONG count; HRESULT hres; // First lets get all the ServiceName info from the SQL properties. HRESULT hr = pSvc->ExecQuery(bstrWQL,bstrQuery,WBEM_FLAG_RETURN_IMMEDIATELY|WBEM_FLAG_FORWARD_ONLY,NULL,&pEnum); _variant_t vt; if (SUCCEEDED(hr)) { // Enumerate the ServiceName result set and get information for each related instance ===// while (SUCCEEDED(pEnum->Next(10000,1,&pObject,&count)) && count) { if (FAILED(hres=pObject->Get(L"ServiceName",0,&vt,NULL,NULL))) { cout << "Failed to get some ServiceName info. Error code = 0x" << hex << hres << endl; continue; } else { serviceName = vt.bstrVal; // Get the instance name instanceName = GetInstanceNameFromServiceName(serviceName); } pObject->Release(); pObject = NULL; // Get the version and edition version = GetWmiPropertyValueForEngineService(serviceName, L"Version", pSvc); edition = GetWmiPropertyValueForEngineService(serviceName, L"SKUNAME", pSvc); cout << "Service Name: " << serviceName << endl; cout << "Instance Name: " << instanceName << endl; cout << "Version: " << version << endl; cout << "Edition: " << edition << endl << endl; } pEnum->Release(); pEnum = NULL; } } /// <summary> /// method extracts the instance name from the service name /// </summary> /// <param name="serviceName"></param> /// <returns></returns> _bstr_t GetInstanceNameFromServiceName(_bstr_t serviceName) { _variant_t vt; _bstr_t instanceName; std::wstring str = serviceName; //Check for the presence of an instance name if(!str.empty()) { wstring::size_type idx, len; len = str.size(); //$ separates servicename from instance name idx = str.find_first_of(L"$"); if(idx!=std::wstring::npos) { instanceName = str.substr(idx+1,len-idx).c_str(); } else { // Assume it's the default instance instanceName=serviceName; } } else instanceName=L""; return instanceName; } /// <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 service to query for</param> /// <param name="propertyName">The property name whose value is required</param> /// <param name="pSvc">Pointer to the WMI service instance</param> /// <returns></returns> _bstr_t GetWmiPropertyValueForEngineService(_bstr_t serviceName, _bstr_t propertyName, IWbemServices* pSvc) { HRESULT hres; _variant_t vt; _bstr_t bstrWQL(L"WQL"); _bstr_t bstrQuery, bstr; IEnumWbemClassObject* pEnum = NULL; IWbemClassObject* pObject = NULL; ULONG count; // Build the WQL statement bstrQuery = L"select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = '"; bstrQuery += propertyName; bstrQuery += "' and ServiceName = '"; bstrQuery += serviceName; bstrQuery += L"'"; if (SUCCEEDED(pSvc->ExecQuery(bstrWQL,bstrQuery,WBEM_FLAG_RETURN_IMMEDIATELY|WBEM_FLAG_FORWARD_ONLY,NULL,&pEnum))) { while (SUCCEEDED(pEnum->Next(10000,1,&pObject,&count)) && count) { if (FAILED(hres=pObject->Get(L"PropertyStrValue",0,&vt,NULL,NULL))) { cout << "Failed to get some info. Error code = 0x" << hex << hres << endl; } else { // get the value bstr = vt.bstrVal; } pObject->Release(); pObject = NULL; } pEnum->Release(); pEnum = NULL; } return bstr; }
#include "stdafx.h"
#include <comdef.h>
#include <iostream>
using namespace std;
#include <windows.h>
#include <assert.h>
#include <wbemidl.h>
HRESULT InitializeCOMandCOMSecurity();
IWbemServices* GetSQLServerWMIConnection();
void EnumerateSQLInstances(IWbemServices* pSvc);
_bstr_t GetInstanceNameFromServiceName(_bstr_t serviceName);
_bstr_t GetWmiPropertyValueForEngineService(_bstr_t serviceName, _bstr_t propertyName, IWbemServices* pSvc);
int _tmain(int argc, _TCHAR* argv[])
HRESULT hres;
IWbemServices *pSvc = NULL;
//initialize COM and COM Security
if (FAILED(hres=InitializeCOMandCOMSecurity()))
return hres;
if ((pSvc=GetSQLServerWMIConnection()) == NULL)
return -1;
EnumerateSQLInstances(pSvc);
// Cleanup
if (pSvc != NULL)
pSvc->Release();
CoUninitialize();
return 0;
/// Initializes COM and COM security
/// <returns>If COM or COM security initialization fails, a failure code; otherwise, zero.</returns>
HRESULT InitializeCOMandCOMSecurity()
hres = CoInitializeEx(0, COINIT_MULTITHREADED); // Initialize COM.
if (FAILED(hres))
cout << "Failed to initialize COM library. Error code = 0x" << hex << hres << endl;
return hres; // Program has failed.
hres = CoInitializeSecurity(NULL, -1, NULL, NULL,
RPC_C_AUTHN_LEVEL_CONNECT,
RPC_C_IMP_LEVEL_IMPERSONATE,
NULL, EOAC_NONE, 0
);
cout << "Failed to initialize security. Error code = 0x" << hex << hres << endl;
/// Connects to the SQL Server WMI namespace
/// <returns>If successful, a pointer to the IWebmServices object bound to the namespace; otherwise, null</returns>
IWbemServices* GetSQLServerWMIConnection()
IWbemLocator *pLoc = NULL;
// Get IWbemLocator object which is used to connect to WMI namespaces
hres = CoCreateInstance(CLSID_WbemLocator, 0, CLSCTX_INPROC_SERVER, IID_IWbemLocator, (LPVOID *) &pLoc);
cout << "Failed to create IWbemLocator object used for namespace connections. Err code = 0x" << hex << hres << endl;
return NULL; // Program has failed.
// Attempt to connect to root\Microsoft\SQLServer\ComputerManagement10 (katmai+)
_bstr_t bstrNamespace("\\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement10");
hres = pLoc->ConnectServer(
bstrNamespace,
NULL,
0,
&pSvc
//if \ComputerManagement10 failed, try \ComputerManagement (yukon)
cout << "Could not connect to \\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement10. Error code = 0x"
<< hex << hres << endl << "Trying \\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement" << endl;
bstrNamespace = "\\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement";
//if \ComputerManagement failed, return a null
cout << "Could not connect to \\\\.\\root\\Microsoft\\SqlServer\\ComputerManagement. Error code = 0x"
<< hex << hres << endl;
pSvc = NULL;
cout << endl << "Connected to " << (char*)bstrNamespace << endl << endl;
pLoc->Release();
return pSvc;
/// Enumerate SQL Server Instances
/// <param name="pSvc">pointer to the IWebmServices object bound to the interface</para>
void EnumerateSQLInstances(IWbemServices* pSvc)
_bstr_t bstrWQL(L"WQL");
// WQL to retrieve the instance information
_bstr_t bstrQuery(L"SELECT * FROM SqlServiceAdvancedProperty WHERE SQLServiceType = 1 AND PropertyName = 'instanceID'");
_bstr_t bstrTemp;
_bstr_t serviceName;
_bstr_t instanceName;
_bstr_t version;
_bstr_t edition;
IEnumWbemClassObject* pEnum = NULL;
IWbemClassObject* pObject = NULL;
ULONG count;
// First lets get all the ServiceName info from the SQL properties.
HRESULT hr = pSvc->ExecQuery(bstrWQL,bstrQuery,WBEM_FLAG_RETURN_IMMEDIATELY|WBEM_FLAG_FORWARD_ONLY,NULL,&pEnum);
_variant_t vt;
if (SUCCEEDED(hr))
// Enumerate the ServiceName result set and get information for each related instance ===//
while (SUCCEEDED(pEnum->Next(10000,1,&pObject,&count)) && count)
if (FAILED(hres=pObject->Get(L"ServiceName",0,&vt,NULL,NULL)))
cout << "Failed to get some ServiceName info. Error code = 0x" << hex << hres << endl;
continue;
serviceName = vt.bstrVal;
// Get the instance name
pObject->Release();
pObject = NULL;
// Get the version and edition
version = GetWmiPropertyValueForEngineService(serviceName, L"Version", pSvc);
edition = GetWmiPropertyValueForEngineService(serviceName, L"SKUNAME", pSvc);
cout << "Service Name: " << serviceName << endl;
cout << "Instance Name: " << instanceName << endl;
cout << "Version: " << version << endl;
cout << "Edition: " << edition << endl << endl;
pEnum->Release();
pEnum = NULL;
_bstr_t GetInstanceNameFromServiceName(_bstr_t serviceName)
std::wstring str = serviceName;
//Check for the presence of an instance name
if(!str.empty())
wstring::size_type idx, len;
len = str.size();
//$ separates servicename from instance name
idx = str.find_first_of(L"$");
if(idx!=std::wstring::npos)
instanceName = str.substr(idx+1,len-idx).c_str();
// Assume it's the default instance
instanceName=serviceName;
instanceName=L"";
return instanceName;
/// <param name="pSvc">Pointer to the WMI service instance</param>
_bstr_t GetWmiPropertyValueForEngineService(_bstr_t serviceName, _bstr_t propertyName, IWbemServices* pSvc)
_bstr_t bstrQuery, bstr;
// Build the WQL statement
bstrQuery = L"select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = '";
bstrQuery += propertyName;
bstrQuery += "' and ServiceName = '";
bstrQuery += serviceName;
bstrQuery += L"'";
if (SUCCEEDED(pSvc->ExecQuery(bstrWQL,bstrQuery,WBEM_FLAG_RETURN_IMMEDIATELY|WBEM_FLAG_FORWARD_ONLY,NULL,&pEnum)))
if (FAILED(hres=pObject->Get(L"PropertyStrValue",0,&vt,NULL,NULL)))
cout << "Failed to get some info. Error code = 0x" << hex << hres << endl;
// get the value
bstr = vt.bstrVal;
return bstr;
For more information about troubleshooting problems with WMI, see Windows Management Instrumentation (http://msdn.microsoft.com/en-us/library/aa394582(VS.85).aspx).
The actions to take if a previous installation of SQL Server is found depends 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:
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:
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.
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:
System.Diagnostics.Process.Start(@"C:\Program Files\Microsoft\Web Platform Installer\webplatforminstaller.exe"," /id SQLExpress");
System.Diagnostics.Process.Start("C:\Program Files\Microsoft\Web Platform Installer\webplatforminstaller.exe"," /id SQLExpress")
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);
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>]
System.Diagnostics.Process.Start("wpi://SQLExpress/");
System.Diagnostics.Process.Start("wpi://SQLExpress/")
Using the Web App Gallery
Syntax: http://www.microsoft.com/web/gallery/install.aspx?[appsxml=<file id>][&appsxml=<file id>…]appid=<product id>[%3b<product id>…][&applang=<language id>]
System.Diagnostics.Process.Start("http://www.microsoft.com/web/gallery/install.aspx?appsxml=&appid=SQLExpress");
System.Diagnostics.Process.Start("http://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 http://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.
Command Prompt
Setup.exe /q /Action=Install /Hideconsole /IAcceptSQLServerLicenseTerms=True /Features=SQL,Tools /InstanceName=SQLExpress /SQLSYSADMINACCOUNTS="Builtin\Administrators" /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>
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""");
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""");
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""")
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""")
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);
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:
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 http://msdn.microsoft.com/en-us/library/ms144259(v=SQL.105).aspx.
Parameter
/AddCurrentUserAsSQLAdmin
Optional
Adds the current user to the SQL Server sysadmin fixed server role. The /ADDCURRENTUSERASSQLADMIN parameter can be used when installing Express editions or when /Role=ALLFeatures_WithDefaults is used. For more information, see /ROLE.
Use of /ADDCURRENTUSERASSQLADMIN is optional, but either /ADDCURRENTUSERASSQLADMIN or /SQLSYSADMINACCOUNTS is required. Default values:
/ACTION
Required
Required to indicate the installation workflow.
Supported values:
/CONFIGURATIONFILE
Specifies the ConfigurationFile to use.
/ERRORREPORTING
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).
/FEATURES
Specifies the components to install.
/INSTALLSHAREDDIR
Specifies a nondefault installation directory for 64-bit shared components.
/INSTALLSHAREDWOWDIR
Specifies a nondefault installation directory for 32-bit shared components. Supported only on a 64-bit system.
/INSTANCEDIR
Specifies a nondefault installation directory for instance-specific components.
/INSTANCENAME
Specifies a SQL Server instance name.
For more information, see Instance Configuration.
/Q
Specifies that Setup runs in a quiet mode without displaying user interface messages or requiring user input. Used for unattended installations.
/QS
Specifies that Setup runs and shows progress through the UI, but does not accept any user input or display any error messages.
/SQMREPORTING
Specifies feature usage reporting for SQL Server.
For more information, see Privacy Statement for the Microsoft Error Reporting Service.
/HIDECONSOLE
Specifies that the console window is hidden or closed. If not specified, the console stays open while Setup runs, which is usually not preferred.
/ENABLERANU
Enables run-as credentials for SQL Server Express installations. This option is disabled by default.
/INSTALLSQLDATADIR
Specifies the data directory for SQL Server data files.
Default values:
For all other installations: %Program Files%\Microsoft SQL Server\
/SAPWD
Required when /SECURITYMODE=SQL
Specifies the password for the SQL Server system administrator (sa) account.
/SECURITYMODE
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
/SQLBACKUPDIR
Specifies the directory for backup files.
The default value is
<InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Backup
/SQLCOLLATION
Specifies the collation settings for SQL Server.
SQL_Latin1_General_CP1_CS_AS
/SQLSVCACCOUNT
Specifies the startup account for the SQL Server service.
/SQLSVCPASSWORD
Specifies the password for SQLSVCACCOUNT. (This is required only if a local account or domain account is used.)
/SQLSVCSTARTUPTYPE
Specifies the startup mode for the SQL Server service. Supported values are:
/RSSVCACCOUNT
Specifies the startup account for the Reporting Server service. This is available only in the Express Advanced Package.
/RSSVCPASSWORD
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
Specifies the startup mode for the Reporting Server Service. Supported values are:
This is available only in the Express Advanced Package.
/SQLSYSADMINACCOUNTS
Provisions logins to be members of the sysadmin role.
This parameter is not required if /AddCurrentUserAsSQLAdmin is specified.
/SQLTEMPDBDIR
Specifies the directory for the data files for tempdb. The default value is <InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data
/SQLTEMPDBLOGDIR
Specifies the directory for the log files for tempdb. The default value is
<InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data
/SQLUSERDBDIR
Specifies the directory for the data files for user databases. The default value is <InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data
/SQLUSERDBLOGDIR
Specifies the directory for the log files for user databases. The default value is <InstallSQLDataDir>\ <SQLInstanceID>\MSSQL\Data
/USESYSDB
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
Specifies the access level for the FILESTREAM feature.
Supported values are:
/FILESTREAMSHARENAME
Required when FILESTREAMLEVEL is greater than 1.
Specifies the name of the Windows share on which the FILESTREAM data will be stored.
/FTSVCACCOUNT
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
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
Specifies the state of the Named Pipes protocol for the SQL Server service. Supported values are:
Note: To enable remote connections, you need to enable the NPENABLED or TCPENABLED parameters.
/TCPENABLED
Specifies the state of the TCP protocol for the SQL Server service. Supported values are:
/IACCEPTSQLSERVERLICENSETERMS
Indicates acceptance or refusal of SQL Server license terms. Supported values are:
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 (http://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 following table contains a list of the input parameters used for upgrading to SQL Server 2008 Express.
The supported value is Upgrade.
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 are:
/ INSTANCEDIR
Specifies a nondefault installation directory for shared components
Specifies that Setup run in a quiet mode without any user interface. Use this parameter for unattended installations.
Specifies feature usage reporting for SQL Server. For more information, see Privacy Statement for the Microsoft Error Reporting Service. Supported value are:
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.
/BROWSERSVCSTARTUPTYPE
Specifies the startup mode for SQL Server Browser service. Supported values are:
/FTUPGRADEOPTION
Specifies the full-text catalog upgrade option. Supported values are:
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 (http://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.
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);
//Loop until the process has exited
do
//refresh the process
processObj.Refresh();
} while (!processObj.WaitForExit(1000));
Console.WriteLine("Process exited with {0}!", processObj.ExitCode);
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 information processObj.Refresh() Loop Console.WriteLine("Process exited with {0}!", processObj.ExitCode)
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 information
processObj.Refresh()
Loop
Console.WriteLine("Process exited with {0}!", processObj.ExitCode)
#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; }
#include <Windows.h>
#include <stdio.h>
#include <tchar.h>
// Create the setup.exe process
if(!CreateProcess(L"c:\\temp\\sqlsetup\\setup.exe",
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 );
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.
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.
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
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.
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:
Other Languages
This article is also available in the following languages: