Installing SQL Server using PowerShell

If you're really eager to dive into PowerShell and start installing SQL Server, this recipe will give you a taste of installing SQL Server with PowerShell using the SQL Server setup.exe file and a configuration file.

Getting ready

Get your SQL Server binaries ready. If you have it burned on a DVD, place your copy in the DVD drive. If you have it as an ISO or image file, mount the files now.

You will also need to identify the service accounts you want to use for the SQL Server services you want to install, as well as the locations for all the files that SQL Server will save on your system. In order to perform a completely automated install, the following script will need to be adjusted to use the default service account credentials, or specify the usernames and passwords within the $command variable.

In this exercise, we will generate a configuration (.ini) first, and then use this for the installation.

How to do it...

The steps to install a standalone SQL Server instance are as follows:

1. Generate the configuration file using the following steps:
   1. Load your SQL Server install disk or image and launch the setup.exe file.
   2. Go through the Wizard and enter all the configuration values.
   3. Once you get to the Ready to Install screen, note the Configuration file path:
      
   4. Cancel the installation using the Wizard.
2. Change the configuration file to enable a silent install. Open the .ini file and make the following changes:
   1. Change the QUIET setting to True:

      QUIET="True"

   2. Comment out the UIMODE setting by putting a semicolon before it:

      ;UIMODE="Normal"

   3. Add the IAcceptSQLServerLicenseTerms value:

      IAcceptSQLServerLicenseTerms="True"

3. Save your .ini file.
4. Run your PowerShell ISE as administrator.
5. Add the following code to your script editor:

   #change this to the location of your configuration file
   $configfile = "C:\Configurations\SQL_ConfigurationFile.ini"
   
   #we are still using the setup.exe that comes with
   #the SQL Server bits
   #adjust the path below to where your setup.exe is
   $command = "D:\setup.exe /ConfigurationFile=$($configfile)"
   
   #run the command
   Invoke-Expression -Command $command

6. Change the location of the $configfile variable to the location where you saved your .ini file. Change the location of the executable as well. In the preceding script, the executable is in the D:\ directory.
7. Execute the code.

How it works...

SQL Server can be installed different ways:

• Using Wizard: You may choose to install SQL Server using the wizard-driven GUI approach, which starts by double-clicking on the setup.exe file that comes with your SQL Server binary (https://technet.microsoft.com/en-us/library/ms143219.aspx).
• Via command prompt: You can also install SQL Server using the command prompt by invoking setup.exe from the command prompt, and providing all the configuration values in the proper setup parameters (https://technet.microsoft.com/en-us/library/ms144259.aspx).
• Via configuration file: You can install SQL Server still by using the setup executable, but instead of providing all the values in the command prompt, use a configuration file that will host all the configuration values. Visit https://technet.microsoft.com/en-us/library/dd239405.aspx for more information.
• Via SysPrep: Install SQL Server using SysPrep. It is Microsoft's system preparation tool that allows administrators to deploy an image to multiple servers and/or workstations. Visit https://technet.microsoft.com/en-us/library/ee210664.aspx for more information.

In the recipe, we went with the third option and installed SQL Server using a configuration file. We are simply going to wrap a few components in PowerShell. You might be asking, "Why not script the whole process in PowerShell instead of using the executable and configuration file?" The answer is, we can do so, and there may be cases where that's the best approach. However, for simple and straightforward installations, it will be easiest to reuse as much of SQL Server's robust, tried-and-true installation process and wrap it inside PowerShell.

The SQL Server configuration file, which has the .ini extension, is a text file that contains installation parameter key-value pairs based on your entries and selections within the wizard. The format you will find in the file looks like this:

;comment or description
PARAMETERNAME = "value"

Some of the common parameters that will be specified in the configuration file include the following:

Parameter	Description
ACTION	This is required to start the installation. It accepts only a single value of Install.
IACCEPTSQLSERVERLICENSETERMS	This is required for unattended installations, and it accepts End User License Agreement.
UPDATEENABLED	This specifies whether the installation should discover and include product updates, and it accepts True or False.
FEATURES	This specifies components to install, for example, SQLENGINE, AS, RS, IS, SSMS, or REPLICATION.
INSTANCENAME	This is a SQL Server instance name.
AGTSVCACCOUNT	This is a SQL Agent service account.
AGTSVCSTARTUPTYPE	This is a SQL Agent startup type, and it accepts any of the following values: Automatic, Manual, or Disabled.
SQLCOLLATION	This is the SQL Server instance collation.
SQLSVCACCOUNT	This is the SQL Server database engine service account.
SQLSYSADMINACCOUNTS	These are the SQL Server system admin accounts.
TCPENABLED	This specifies whether an instance has TCP enabled.

The list of supported settings is outlined at https://technet.microsoft.com/en-us/library/ms144259.aspx.

You can create the .ini file from scratch, but it would be best to at least start with the configuration file you get with the wizard. From here, you can adjust and provide additional settings.

Once we've finalized the .ini file, the next step is to compose the actual command that needs to be executed. In the following code, we are simply creating a string that contains the path to the setup.exe and passing in a single parameter for the ConfigurationFile:

$command = "D:\setup.exe /ConfigurationFile=$($configfile)"

Alternatively, you can also dynamically build the contents .ini file using PowerShell and then pass this configuration file to setup.exe, just like how we built $command previously.

Once the command string is ready, we can use the Invoke-Expression PowerShell cmdlet to run the expression contained by the $command variable:

Invoke-Expression -Command $command

Instead of using the .ini file, you can also dynamically build all the parameters in a long string based on specific conditions or cases. You can take advantage of PowerShell's logic operators and other constructs when you do this. You should be able to compose the complete command and use Invoke-Expression to perform the actual installation:

$command = 'D:\setup.exe /ACTION=Install /Q /INSTANCENAME="SQL01"
/IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLENGINE,REPLICATION SQLSYSADMINACCOUNTS="QUERYWORKS\Administrator"'

There's more...

You can also take advantage of Desired State Configuration (DSC), which was introduced in PowerShell v4 and works with Windows Server 2012 R2, to install SQL Server.

DSC is a set of language extensions that will allow you to specify a desired state, or a set of ideal configurations, for your servers. This simplifies the configuration of new SQL Server instances, because all you have to do is to identify the desired state for your SQL Server installations and reuse the script for every deployment.

These are the simplified steps to take advantage of DSC:

1. Write a configuration script.
2. Run the configuration script to create a Management Object Framework (MOF).
3. Copy the MOF to the server you're installing SQL Server to. After the installation, at some point, you will want your server to pull the updated MOF automatically.
4. Apply the configuration to the target server and start the installation process.

The PowerShell team made the xSqlPs PowerShell module available, which is currently an experimental module, in the Technet Script Center (https://gallery.technet.microsoft.com/scriptcenter/xSqlps-PowerShell-Module-aed9426c). Here is a description of the xSqlPs module from the site:

The xSqlPs module is a part of the Windows PowerShell Desired State Configuration resource kit, which is a collection of DSC resources produced by the PowerShell team. This module contains the xSqlServerInstall, xSqlHAService, xSqlHAEndpoint, xSqlHAGroup, and xWaitForSqlHAGroup resources.

To install SQL Server, you will need to work with xSqlServerInstall. The PowerShell team has provided an excellent tutorial on how to use this DSC resource. This is a good starting script for a SQL Server Enterprise installation, and you can adjust it as needed. By the time this book is in your hands, the scripts in the module may have already been updated, or moved from an experimental to stable state. Please note that these scripts are also provided as is, with no support or warranty from Microsoft.

Note

If you are looking for a good tutorial on DSC, check out the Microsoft Virtual Academy site (http://www.microsoftvirtualacademy.com/liveevents/getting-started-with-powershell-desired-state-configuration-dsc).