Performing a Simple Installation

Table 7-5 details the syntax for using parameters when running setup.exe from PowerShell.

The exception to these rules is the /FEATURES parameter, which is comma-separated, with the syntax /FEATURES=Feature1,Feature2.

If you are calling setup.exe, or any other executable, from the folder wherein the executable resides, you should prefix the call with ., to indicate that the executable resides in the same location. Therefore, assuming that PowerShell is scoped to the root of the SQL Server installation media, we could install a named instance, called ESPROD3, with default values for all optional parameters, by using the command in Listing 7-1.

Listing 7-1. Simple Installation of the Database Engine

.SETUP.EXE /IACCEPTSQLSERVERLICENSETERMS /ACTION="Install" /FEATURES=SQLEngine,Replication,Conn /INSTANCENAME="ESPROD3" /SQLSYSADMINACCOUNTS="ESASSSQLAdmin" /AGTSVCACCOUNT="ESASSSQLServiceAccount" /AGTSVCPASSWORD="Pa££w0rd" /SQLSVCACCOUNT="ESASSSQLServiceAccount" /SQLSVCPASSWORD="Pa££w0rd" /q  

This command will install the Database Engine, Replication components, and connectivity components. The Database Engine service and SQL Server Agent service will both run under the context of the domain account SQLServerService, and the SQLAdmin domain account will be added to the sysadmin fixed server role. The /q switch will ensure that no interaction is required.

Depending on the features that you choose to install, there are a variety of parameters that can be configured. Table 7-6 details each of the optional parameters that can be configured when installing the Database Engine and Integration Services.

Using a Configuration File

If you have to configure many parameters for your build, it is cumbersome to create a script that passes the value for every parameter, every time you call it. To resolve this, you can use a configuration file, which will store the values for any parameters that are consistent across every build. Parameters that will be specific to each individual build, such as instance name, or service account should still be passed from the script.

Every time you install an instance from the GUI, a configuration file is automatically generated and placed in C:Program FilesMicrosoft SQL Server130Setup BootstrapLogYYYYMMDD_HHMMSS. This path assumes that you installed SQL Server in the default location. The timestamp relates to the time that the installation began.

Listing 7-2 shows the configuration file that was created when the ESPROD3 instance was installed. Lines that are prefixed with a semicolon are comments and are ignored by the installer.

Listing 7-2. ESPROD3 Configuration File

;SQL Server 2016 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

ACTION="Install"

; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.

ENU="True"

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.

UIMODE="Normal"

; Setup will not display any user interface.

QUIET="True"

; Setup will display progress only, without any user interaction.

QUIETSIMPLE="False"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.

UpdateEnabled="True"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.

ERRORREPORTING="True"

; If this parameter is provided, then this computer will use Microsoft Update to check for updates.

USEMICROSOFTUPDATE="False"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.

FEATURES=SQLENGINE,REPLICATION

; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.

UpdateSource="MU"

; Displays the command line parameters usage

HELP="False"

; Specifies that the detailed Setup log should be piped to the console.

INDICATEPROGRESS="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.

X86="False"

; Specify the root installation directory for shared components. This directory remains unchanged after shared components are already installed.

INSTALLSHAREDDIR="C:Program FilesMicrosoft SQL Server"

; Specify the root installation directory for the WOW64 shared components. This directory remains unchanged after WOW64 shared components are already installed.

INSTALLSHAREDWOWDIR="C:Program Files (x86)Microsoft SQL Server"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).

INSTANCENAME="ESPROD3"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.

SQMREPORTING="True"

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.

INSTANCEID="ESPROD3"

; Specify the installation directory.

INSTANCEDIR="C:Program FilesMicrosoft SQL Server"

; Agent account name

AGTSVCACCOUNT="SQLServiceAccount"

; Auto-start service after installation.  

AGTSVCSTARTUPTYPE="Manual"

; CM brick TCP communication port

COMMFABRICPORT="0"

; How matrix will use private networks

COMMFABRICNETWORKLEVEL="0"

; How inter brick communication will be protected

COMMFABRICENCRYPTION="0"

; TCP port used by the CM brick

MATRIXCMBRICKCOMMPORT="0"

; Startup type for the SQL Server service.

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3).

FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express.

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine.

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: DomainUser or system account.

SQLSVCACCOUNT="SQLServiceAccount"

; Set to "True" to enable instant file initialization for SQL Server service. If enabled, Setup will grant Perform Volume Maintenance Task privilege to the Database Engine Service SID. This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.

SQLSVCINSTANTFILEINIT="False"

; Windows account(s) to provision as SQL Server system administrators.

SQLSYSADMINACCOUNTS="SQLAdmin"

; The number of Database Engine TempDB files.

SQLTEMPDBFILECOUNT="2"

; Specifies the initial size of a Database Engine TempDB data file in MB.

SQLTEMPDBFILESIZE="8"

; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.

SQLTEMPDBFILEGROWTH="64"

; Specifies the initial size of the Database Engine TempDB log file in MB.

SQLTEMPDBLOGFILESIZE="8"

; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.

SQLTEMPDBLOGFILEGROWTH="64"

; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express.

ADDCURRENTUSERASSQLADMIN="False"

; Specify 0 to disable or 1 to enable the TCP/IP protocol.

TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol.

NPENABLED="0"

; Startup type for Browser Service.

BROWSERSVCSTARTUPTYPE="Automatic"

Because every build of SQL Server generates a configuration file, an easy way to generate a configuration is to run through the installation steps using the GUI. At the end of the process, you will be provided a link to the configuration file, without actually installing the instance. This configuration file can then be used as a template for your automated build.

Let’s install another instance of SQL Server 2016, this time using a configuration file. We have determined the parameters that we want to configure, and these are detailed in Table 7-7. All parameters not listed will use default values.

The parameters that will be static across all builds, we will specify in the configuration file. Parameters which change for every build, we will specify in our PowerShell script. Listing 7-3 shows the configuration file for our new build.

Listing 7-3. New Configuration File

;SQL Server 2016 Configuration File
[OPTIONS]

; Accept the SQL Server license terms

IACCEPTSQLSERVERLICENSETERMS="True"

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

ACTION="Install"

; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.

ENU="True"

; Setup will not display any user interface.

QUIET="True"

; Setup will display progress only, without any user interaction.

QUIETSIMPLE="False"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.

UpdateEnabled="True"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.

ERRORREPORTING="True"

; If this parameter is provided, then this computer will use Microsoft Update to check for updates.

USEMICROSOFTUPDATE="False"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.

FEATURES=SQLENGINE,REPLICATION,IS

; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.

UpdateSource="E:SQLServer2016Patches"

; Displays the command line parameters usage

HELP="False"

; Specifies that the detailed Setup log should be piped to the console.

INDICATEPROGRESS="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.

X86="False"

; Specify the root installation directory for shared components. This directory remains unchanged after shared components are already installed.

INSTALLSHAREDDIR="C:Program FilesMicrosoft SQL Server"

; Specify the root installation directory for the WOW64 shared components. This directory remains unchanged after WOW64 shared components are already installed.

INSTALLSHAREDWOWDIR="C:Program Files (x86)Microsoft SQL Server"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.

SQMREPORTING="True"

INSTANCEDIR="C:Program FilesMicrosoft SQL Server"

; Auto-start service after installation.  

AGTSVCSTARTUPTYPE="Manual"

; CM brick TCP communication port

COMMFABRICPORT="0"

; How matrix will use private networks

COMMFABRICNETWORKLEVEL="0"

; How inter brick communication will be protected

COMMFABRICENCRYPTION="0"

; TCP port used by the CM brick

MATRIXCMBRICKCOMMPORT="0"

; Startup type for the SQL Server service.

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3).

FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express.

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine.

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: DomainUser or system account.

SQLSVCACCOUNT="SQLServiceAccount"

; Set to "True" to enable instant file initialization for SQL Server service. If enabled, Setup will grant Perform Volume Maintenance Task privilege to the Database Engine Service SID. This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.

SQLSVCINSTANTFILEINIT="True"

; The number of Database Engine TempDB files.

SQLTEMPDBFILECOUNT="2"

; Specifies the initial size of a Database Engine TempDB data file in MB.

SQLTEMPDBFILESIZE="8"

; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.

SQLTEMPDBFILEGROWTH="64"

; Specifies the initial size of the Database Engine TempDB log file in MB.

SQLTEMPDBLOGFILESIZE="8"

; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.

SQLTEMPDBLOGFILEGROWTH="64"

; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express.

ADDCURRENTUSERASSQLADMIN="False"

; Specify 0 to disable or 1 to enable the TCP/IP protocol.

TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol.

NPENABLED="0"

; Startup type for Browser Service.

BROWSERSVCSTARTUPTYPE="Automatic"

You may notice that we have left UpdateEnabledconfigured as "True", but we have changed the UpdateSource parameter to point to a local folder, as opposed to "MU". This means that we can drop into a folder on a network share all SQL Server updates that we wish to apply. We can then ensure that all new database servers have a network drive mapped to this location, as part of the Windows build.

With the use of the UpdateEnabled and UpdateSourceparameters, we can now ensure that all new builds are patched to our latest tested version. We can also drop multiple updates into the folder location. So, for example, we could drop SP1 and CU3. The instance will then patch the instance to SP1, CU3 during the installation. This reduces the overhead of applying the patches manually post-installation.

Assuming that we save the configuration file in the root of the SQL Server installation media and name it ConfigurationFile.ini (the default name), the command in Listing 7-4 demonstrates the command that we would use to install an instance named ESPROD4.

Listing 7-4. Install an Instance with a Configuration File

.setup.exe /INSTANCENAME="ESPROD4" /SQLSYSADMINACCOUNTS="ESASSSQLAdmin" /AGTSVCACCOUNT="ESASSSQLServiceAccount" /AGTSVCPASSWORD="Pa££w0rd" /SQLSVCACCOUNT="ESASSSQLServiceAccount" /SQLSVCPASSWORD="Pa££w0rd" /CONFIGURATIONFILE=".ConfigurationFile.ini"

Parameterizing the Script

To maximize the potential of using PowerShell to automate our SQL Server build, we will have to parameterize the script. This means that instead of having to modify the script with appropriate parameter values every time we install a new instance, we will simply be able to pass those parameter values into the script when we call it.

The additional benefit of this approach is that when we create an orchestration script (which is discussed in the “Orchestrating the Build” section of this chapter), we can pass parameter values into the orchestration script, and common parameters can then be passed into our scripts to install the instance, configure the instance, and update the Inventory database. This saves the DBA time and reduces the risk of human error causing delays.

In order to parameterize the script, we will have to add a param block at the beginning of the script. We will then replace the parameter values that we are passing into setup.exe with the parameters declared in the param block, as demonstrated in Listing 7-5.

Listing 7-5. Parameterizing the Script

param(
[string] $InstanceName,
[string] $SQLServiceAccount,
[string] $SQLServiceAccountPassword,
[string] $AgentServiceAccount,
[string] $AgentServiceAccountPassword,
[string] $Administrators
)

.SETUP.EXE /INSTANCENAME=$InstanceName /SQLSYSADMINACCOUNTS=$Administrators /SQLSVCACCOUNT=$SQLServiceAccount /SQLSVCPASSWORD=$SQLServiceAccountPassword /AGTSVCACCOUNT=$AgentServiceAccount /AGTSVCPASSWORD=$AgentService AccountPassword /CONFIGURATIONFILE="./ConfigurationFile.ini"

If we now save the script as AutoBuild.ps1 in the root folder of the SQL Server installation media, we can run the script to create ESPROD5, by using the command in Listing 7-6.

Listing 7-6. Call the AutoBuild.ps1 Script

# To specify multiple members of the sysadmin server role,
# pass a comma seperate list to the -Administrators parameter
./AutoBuild.ps1 -InstanceName 'ESPROD5' -SQLServiceAccount 'SQLServiceAccount'
-SQLServiceAccountPassword 'Pa££w0rd' -AgentServiceAccount 'SQLServiceAccount'
-AgentServiceAccountPassword 'Pa££w0rd' -Administrators 'SQLAdmin'

Configure Database Scoped Configurations

Microsoft has simplified the task of configuring an instance in SQL Server 2016 by adding the ability to configure the number of TempDB files required and if the instance should use Instant File Initialization during the installation. There are also more sensible file size settings, with data and log files defaulting to 8MB instead of 1MB, and file growth defaulting to 64MB. In my opinion, these settings should still be configured based on your capacity planning, but if you do forget to configure them, your log fragmentation will not be quite as bad as in previous versions.

Microsoft has also simplified instance configuration by bringing the functionality of some common trace flags to the database scope, as opposed to the server scope, through fully supported ALTER DATABASE statements. Additionally, some instance-level configurations, such as MAXDOP and parameter sniffing, have also been implemented at the database level.

Table 7-8 details our required configurations for instances supporting Data Warehouse workloads and OLTP workloads.

We can create a PowerShell script, which will accept a parameter detailing the type of instance and then conditionally configure these settings, by using the invoke-sqlcmd cmdlet, to modify the Model database. Once the Model database has been configured, all user databases that are created will inherit its properties, unless specifically overridden. For properties that are not permitted to be configured in Model, we will conditionally create a DDL trigger, which will fire on new database creation. This is demonstrated in Listing 7-7.

Listing 7-7. Conditionally Configure Instance

param(
[string] $InstanceName,
[string] $InstanceWorkload #The expected workload of the instance
)

$ServerInstance = $env:COMPUTERNAME + "" + $InstanceName

IF ($InstanceWorkload -eq "Data Warehouse")
{
    Invoke-Sqlcmd -Server $ServerInstance -Query "--Configure Model
USE Model
GO

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8

USE [msdb]
GO

EXEC  msdb.dbo.sp_add_job @job_name='ConfigureNewDatabase',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=2,
                @notify_level_netsend=2,
                @notify_level_page=2,
                @delete_level=0,
                @category_name='[Uncategorized (Local)]',
                @owner_login_name='ESPROD3SQLAdmin'

GO

EXEC msdb.dbo.sp_add_jobstep @job_name='ConfigureNewDatabase', @step_name='ConfigureDatabase',
                @step_id=1,
                @cmdexec_success_code=0,
                @on_success_action=1,
                @on_fail_action=2,
                @retry_attempts=0,
                @retry_interval=0,
                @os_run_priority=0, @subsystem='TSQL',
                @command='DECLARE @DatabaseName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)
SET @DatabaseName = (
        SELECT TOP 1 name
        FROM sys.databases
        ORDER BY create_date DESC
        )

IF @DatabaseName IS NOT NULL
BEGIN
    SELECT @sql = ''ALTER DATABASE '' + @DatabaseName + '' MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES''
    EXEC(@SQL)
END',
                @database_name='master',
                @flags=0
GO

EXEC msdb.dbo.sp_add_jobserver @job_name='ConfigureNewDatabase', @server_name = @@SERVERNAME
GO

--Create the trigger

CREATE TRIGGER ConfigureNewDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
    EXEC msdb..sp_start_job 'ConfigureNewDatabase'  
GO"
}

IF ($InstanceWorkload -eq "OLTP")
{
     Invoke-Sqlcmd -Server $ServerInstance -Query "--Configure Model

USE Model
GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8

USE [msdb]
GO

EXEC  msdb.dbo.sp_add_job @job_name='ConfigureNewDatabase',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=2,
                @notify_level_netsend=2,
                @notify_level_page=2,
                @delete_level=0,
                @category_name='[Uncategorized (Local)]',
                @owner_login_name='ESPROD3SQLAdmin'

GO

EXEC msdb.dbo.sp_add_jobstep @job_name='ConfigureNewDatabase', @step_name='ConfigureDatabase',
                @step_id=1,
                @cmdexec_success_code=0,
                @on_success_action=1,
                @on_fail_action=2,
                @retry_attempts=0,
                @retry_interval=0,
                @os_run_priority=0, @subsystem='TSQL',
                @command='DECLARE @DatabaseName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)
SET @DatabaseName = (
        SELECT TOP 1 name
        FROM sys.databases
        ORDER BY create_date DESC
        )

IF @DatabaseName IS NOT NULL
BEGIN
    SELECT @sql = ''ALTER DATABASE '' + @DatabaseName + '' SET MIXED_PAGE_ALLOCATION ON''
    EXEC(@SQL)
END',
                @database_name='master',
                @flags=0
GO

EXEC msdb.dbo.sp_add_jobserver @job_name='ConfigureNewDatabase', @server_name = @@SERVERNAME
GO

--Create the trigger

CREATE TRIGGER ConfigureNewDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
    EXEC msdb..sp_start_job 'ConfigureNewDatabase'  
GO"
}

Configure the Port Number

If you install a named instance of SQL Server, the installer will configure the instance to use dynamic ports. When dynamic ports are configured, every time the instance starts, it will request a port number from Windows. Windows will then assign it a random available port from the dynamic range, which is from 49152 to 65535, assuming that you are running on Windows Server 2008 or later versions. For earlier versions of Windows, the dynamic port range was from 1024 to 5000, but Microsoft changed this in Windows Vista and Windows Server 2008, to comply with the IANA (Internet Assigned Numbers Authority).

If your instance is configured to use dynamic ports, configuring firewalls can be challenging. At the Windows Firewall level, it is possible to configure a specific service to communicate on any port, but this can be hard to replicate at the corporate firewall level. Instead of this approach, you have to keep the full dynamic port range open bidirectionally. This is an obvious security hole, so I recommend that the instance is configured to use a specific port.

You can create a PowerShell script that will be called off as part of your automated build, which will assume the responsibility of changing the port number. This is achieved through SMO, as demonstrated in Listing 7-8.

Listing 7-8. Configure the Port Number

Param(
[string] $InstanceName,
[string] $Port
)

# Load SMO Wmi.ManagedComputer assembly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

# Create a new smo object

$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')

#Disable dynamic ports

$m.ServerInstances[$Instance].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value = ""

# Set static port

$m.ServerInstances[$Instance].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = "$Port"

# Reconfigure TCP

$m.ServerInstances[$Instance].ServerProtocols['Tcp'].Alter()