Configuring SQL Express During Installation

  • Article

In this post I'm going to explore the various ways you can configure SQL Server Express during installation. The easiest way is to just use the Setup UI, but that isn't ideal when you want to embed SQL Express into your own application installer.

Command Line Installation

The SQL Setup program supports a number of command line parameters that can configure many aspects of SQL Express directly during the installation process. The compressed installer file used by SQL Express (SQLEXPR_ADV.EXE for example) supports the exact same set of command line parameters by passing the parameters to Setup.exe once it has been extracted onto the hard drive. The full details of the command line parameters can be found in Books Online and in the install.ini file that is included in the installation package. Some of the parameters that are interesting for SQL Express are:

Quite mode - SQL Express support two different quite mode installations. You can show a status UI, but not require any User input using the /qb parameter. To install without any UI at all, use the /qn parameter.

Naming your server - You specify the Named Instance for your server using the INSTANCENAME parameter, as in INSTANCENAME=SQLEXPRESS. If you want to install SQL Express as the default instance of SQL Server, i.e. you refer to it simply by the machine name, you specify INSTANCENAME=MSSQLSERVER.

Handling Components - You specify which components you want to install using the ADDLOCAL parameter. You can do a full install by specifying ADDLOCAL=ALL, or you can specify specific components, as in ADDLOCAL=SQL_Engine,SQL_FullText. You can also use ADDLOCAL during maintenance mode to add additional features to an installed server. The REMOVE and UPGRADE parameters work in a similar way only for removing features and upgrading features respectively.

Setting Security - SQL Express uses Windows Integrated Security by default. You can switch to SQL Security by using the SECURITYMODE and SAPWD parameters. When you specify SECURITYMODE=SQL you must also specify a strong password for the SA account using the SAPWD parameter.

Enabling Network Connections - By default, SQL Express does not allow network connections. This can be changed using the DISABLENETWORKPROTOCOLS parameter. SQL Express supports connections through Shared Memory (a local only protocol), TCP/IP and Named Pipes. Shared Memory is always available, the others are controlled as follows:

  • DISABLENETWORKPROTOCOLS=0 - All protocols are enabled.
  • DISABLENETWORKPROTOCOLS=1 - Only Shared Memory is enabled.
  • DISABLENETWORKPROTOCOLS=2 - Shared Memory and TCP/IP are enabled, Named Pipes are disabled.

There are a number of other options you can configure from a command line install such as the directory where SQL Express is installed and the Service Account that SQL Express runs as. Explore the BOL topic for more information on other command line parameters.

Wrapping the SQL Express Installer

SQL Express is only available as an MSI based installer, so it can not be embedded inside another MSI, such as the MSI used to install your custom application. In order to install both your application and SQL Express, you need to create a wrapper that handles installing both. The Visual Studio Bootstrapper is one implementation of this kind of a wrapper. The VS Bootstrapper is pre-configured to allow you to build and install your application along with a set of pre-requisites, such as SQL Server and the .NET Framework 2.0. The VS Bootstrapper is used for both ClickOnce deployment and for VS Setup projects.

If the VS Bootstrapper isn't flexible enough for you, you can write a custom wrapper of you own. Wrapper can be as restricted or as dynamic as you like, but the ultimate goal is to create the correct command line to install SQL Express in the desired configuration. Luckily, there is already a white paper that describes how to do this, thus saving me from having to document it here.

A note on the SQL Express installer package - You might be asking how a file named SQLEXPR.EXE (The SQL Express installer) is an "MSI based installer" as I stated earlier. SQL Server is actually a set of MSI installers that each install specific components. The collection of MSI files for a given SQL Edition are installed using our own wrapper program named Setup.exe. (pretty clever name if you ask me) The fact that the individual components are MSI base causes the inability to embed SQL Express directly into another MSI, which is a limitation of Windows Installer.

Since SQL Express is a web download rather than a CD based installation, we actually compress all the files into a special kind of CAB archive that understands how to pass the command line parameters into the Setup.exe process once the archive has been extracted. You can actually extract the SQL Express installation files manually by typing SQLEXPR.EXE /x and then specifying the directory where you want the file copied.

Post-install Configuration using the SAC Utility

The SQL Surface Area Configuration tool (SqlSAC) is a UI tool that allows you to configure a number of aspects of SQL Server, for example, whether CLR is enabled or not. (CLR is disabled by default in SQL Express.) In addition to using the UI, SqlSAC supports a command line interface we call the SAC Utility that allows you to provide configuration settings as a file and apply them to any server. The SAC Utility can be used to configure a number of servers identically using the same configuration settings file or can be called once SQL Server has been installed to apply certain post-install configurations. You could call the SAC Utility as part of a wrapper, or from a Custom Action in your MSI package.

Post-install Configuration using sp_configure

The most common way to configure SQL Server is probably sp_configure, so I'll end the post with this one. You can call sp_configure at any time to change server configuration options, you just need to be connected to the server. This is another technique you can use to configure SQL Express once it's been installed. Just like the SAC Utility, you can use a wrapper or Custom Action to run code that connects to the server and runs the appropriate T-SQL statements. This can be done by simply including a T-SQL script in your installation and then calling SQLCmd to run the script at the end of your installation procedure.

   

  • Mike