Scripting SQL Server installations

Scripting SQL Server installations

I was addressing performance issues onsite just a couple of weeks ago. While waiting for the right authorizations, access tokens, and whatever you need nowadays just to run some scripts…, one of the developers approached me. They asked for some insights on how to Scripting SQL Server installations.

Fortunately, this isn’t as complex as it sounds, and with a bit of PowerShell knowledge, it’s possible to create some very advanced installation scripts! Of course, just like anything in life, this can be as complex as you want it to be. I will start with a 101 baseline, introducing the basics.

Scripting SQL Server installations the basics

What will be covered within this blog post:

  • Feed installation configuration data to the setup.exe executable
  • Generating and altering the setup configuration file
  • Wrapping all in a PowerShell installer script
  • Verify the installation using PowerShell

1. Feed installation configuration data to the setup.exe executable

What’s important to note is that there are two options to feed data into the setup process; the first option involves providing all data via switches. The second option provides the location of a pre-configured installation file.  The drawback of option one is that this will require lots of knowledge regarding the setup switches (and there are quite some options this also becomes unreadable pretty quickly in my opinion. To avoid all of this, we will generate a baseline configuration file and prepare the output for automation afterward.

Very simple sample using switches:


2. Generating and altering the setup configuration file

This step is fairly easy, Start the installation SQL Server installation wizard (New SQL Server stand-alone installation) and configure all options as desired. However, make sure to stop when reaching step “Ready to Install”. The bottom of the dialog will display the location of the generated configuration file “.ini” Copy the file to a new location and cancel the installation wizard.

configuration file path

configuration file path

Before we can use the configuration file, there are a couple of things that will need to be addressed; firstly, you will need to add a line to accept the license agreement, which won’t be included by default due to legal reasons. (add below ACTION=”Install”)

iacceptsqlserverlicenseterms= true

Secondly, if you would like to be able to kick off the installation process without a GUI and perhaps even remotely, it’s required to  alter the following within the configuration file:

Search for UIMODE= “Normal” and comment out the line.

; UIMODE="Normal"

Besides, search for QUIET and make sure to set it’s value to true.


3. Wrapping all in a PowerShell script

At his point, we have the script with all our variables predefined; the only step left is to call the executable and make sure we point to the configuration file. Also, this will allow us to provide additional pre- and post-requisites, e.q. Opening firewall ports, installing SQL Server prerequisites, or altering SQL Server instance configuration.

Start by creating a new PowerShell script and add the following to sick-off the installer:

One important note to make is that it’s required to create the installation directories before running setup. The paths are matching with the configuration file variables INSTALLSHAREDDIR and INSTANCEDIR)

4. Verify the installation using PowerShell

In case you don’t want to verify the installation manually, you could include a basic check, as provided below. This will parse the log searching for application exit code and also creates a database using SQL SMO.

Post Navigation