Scripting SQL Server installations

Scripting SQL Server installations

I was addressing performance issues onsite just a couple of weeks ago and while waiting for the right authorizations, access tokens and whatever you need nowadays to just run some scripts…, one of the developers approached me and 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 live, this can be as complex as you want it to be. I will just 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 just 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 http://msdn.microsoft.com/en-us/library/ms144259.aspx) this also becomes unreadable pretty quickly in my option. To avoid all of this, we will generate a baseline configuration file and prepare the output for automation afterwards.

very simple sample using switches:

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS....{MORE MORE MORE}

2. Generating and altering the setup configuration file

This step is fairly easy, just 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 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 kickoff he 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"

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

QUIET="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. In addition, this will also allow is 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 by hand you could include a basic check as provided below. This will just parse the log searching for application exit code and in addition creates a database using SQL SMO.

Post Navigation