Installing SQL Server using PowerShell

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.

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

SQL Server can be installed different ways:

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:

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"'

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:

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:

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.