Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

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:

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).