Automating Microsoft SQL Server Instance Builds

Automating Instance Builds in Microsoft SQL ServerAutomating an instance build not only reduces DBA effort and time to market for a new data-tier application, it also reduces operational overhead, as it provides a consistent platform for all new data-tier applications across the enterprise.

A fully automated build will consist of far more than just running setup.exe from the command line. After designing and producing automated builds for three different FTSE 100 companies, I have realized the importance of using the build process to ensure that the instance is installed using an organization’s most current patching level, the instance is configured to the DBA team’s standard, and the inventory database (and any other supporting applications) is updated to reflect the build.

This article will discuss how to automate each of these activities, before finally pulling them together into a single PowerShell orchestration.


Table of contents[Show]


 

Building the SQL Server Instance

We can install an instance of SQL Server from PowerShell by calling the setup.exe program from the SQL Server installation media. When calling setup.exe, there are switches and parameters that can be passed to let setup know what type of installation you require and how to configure the instance. Many of these are optional and can be omitted, depending on the features that you are choosing to install, but there are some that must always be specified when installing the database engine. Table 1 details the parameters that must always be specified when installing the SQL Server 2016 Database Engine.

Table 1. Required Parameters

Parameter

Description

/ACTION

Specifies the action that you wish to perform, such as install or repair. A complete list of actions is detailed in Table 2.

/IACCEPTSQLSERVERLICENSETERMS

Confirms that you accept the SQL Server license terms

/FEATURES

Specifies the features that you wish to install. This parameter is only required if /ROLE is not specified. A complete list of features is detailed in Table 3.

/ROLE

Specifies which pre-configured SQL Server role you would like to install. This parameter is only required if /FEATURES is not specified. A complete list of roles can be found in Table 4.

/INSTANCENAME

Specifies a name for the instance

/AGTSVCACCOUNT

Specifies the account that will run the SQL Server Agent service

/AGTSVCPASSWORD

Specifies the password of the account that will run the SQL Server Agent service

/SQLSVCACCOUNT

Specifies the account that will run the SQL Server Database Engine service

/SQLSVCPASSWORD

Specifies the password for the account that will run the SQL Server Database Engine service

/SQLSYSADMINACCOUNTS

Specifies the Windows security context(s) that will be given administrator permissions to the Database Engine

/q

Performs the installation in Quiet mode. While not technically a required parameter, it is required for automating an installation, to avoid interaction.

The /ACTION parameter specifies the action that setup will perform. When you are installing a stand-alone instance of SQL Server, the action will be install. Table 2 details the other acceptable values for the /ACTION parameter.

Table 2. Acceptable Values for the /ACTION Parameter

Value

Description

install

Installs a stand-alone instance

PrepareImage

Prepares a vanilla stand-alone image, with no account, computer, or network details specified

CompleteImage

Completes the installation of a prepared stand-alone image by configuring account-, computer-, and network-related settings

Upgrade

Upgrades an instance from SQL Server 2008, 2012, or 2014

EditionUpgrade

Upgrades a SQL Server 2014 from a lower edition, such as Developer Edition to Enterprise Edition, or Enterprise Edition to Enterprise Core

Repair

Repairs a corrupt instance

RebuildDatabase

Rebuilds corrupted system databases

Uninstall

Uninstalls a stand-alone instance

InstallFailoverCluster

Installs a failover clustered instance

PrepareFailoverCluster

Prepares a vanilla clustered image, with no account, computer, or network details specified

CompleteFailoverCluster

Completes the installation of a prepared clustered image by configuring account-, computer-, and network-related settings

AddNode

Adds a node to a failover cluster

RemoveNode

Removes a node from a failover cluster

The /FEATURES parameter is used to specify which components of SQL Server you wish to install. This parameter also acts as the driver for deciding which optional parameters are required. For example, if you are not installing SSIS, there is no need to pass parameters specifying the service account and service account password to use for the Integration Services service.

The features that can be selected for install are detailed in Table 3, along with the parameter value that should be passed. Parameter values should be comma-separated.

Table 3. Acceptable Values for the /FEATURES Parameter

Parameter Value

Feature Installed

SQL

The Database Engine, including Full Test, Replication, and PolyBase components

SQLEngine

The Database Engine, without its related components

FullText

Full test components

Replication

Replication components

PolyBase

PolyBase components

AdvancedAnalytics

R Services

DQ

The components required to perform install Data Quality Server

AS

Analysis Services

RS

Reporting Services

DQC

Data Quality Client

IS

Integration Services

MDS

Master Data Services

SQL_SHARED_MR

Microsoft R Server

Tools

All client tools and Books Online

BC

Backward compatibility components

BOL

Books Online component

Conn

Connectivity components

SSMS

SQL Server Management Studio, SQLCMD, and the SQL Server PowerShell provider

Adv_SSMS

SSMS support for AS, RS, and IS; Profiler; Database Engine Tuning Advisor; and Utility management

DREPLAY_CTLR

Distributed Replay Controller

DREPLAY_CLT

Distributed Replay Client

SNAC_SDK

SDK for SQL Server Native Client

SDK

Client tools SDK

The /ROLE parameter that can be used as an alternative to the /FEATURES parameter, to install pre-configured roles, which can be used to install SQL Server, are detailed in Table 4.

Table 4. SQL Server Setup Roles

Role

Description

SPI_AS_ExistingFarm

Installs SSAS in PowerPivot mode into an existing SharePoint farm

SPI_AS_NewFarm

Installs SSAS in PowerPivot mode into a new SharePoint farm and configures the farm

AllFeatures_WithDefaults

Installs a Database Engine instance with all available features, including SSAS, SSRS, and SSIS, but excluding the PolyBase Query Service and the SSRS add-in for SharePoint Products

 

Performing a Simple Installation

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

Table 5. Syntax for Parameters

Parameter Type

Syntax

Simple switch

/SWITCH

True/False

/PARAMETER=true/false

Boolean

/PARAMETER=0/1

Text

/PARAMETER="Value"

Multi-valued text

/PARAMETER="Value1" "Value2"

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

Tip

Technically, you only have to use quotation marks when a parameter value contains spaces; however, I generally recommend always including the quotation marks, for consistency and readability.

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

Tip

Change the service account details to match your own configuration, before executing the script.

Listing 1. Simple Installation of the Database Engine

.\SETUP.EXE /IACCEPTSQLSERVERLICENSETERMS /ACTION="Install" /FEATURES=SQLEngine,Replication,Conn /INSTANCENAME="ESPROD3" /SQLSYSADMINACCOUNTS="ESASS\SQLAdmin" /AGTSVCACCOUNT="ESASS\SQLServiceAccount" /AGTSVCPASSWORD="Pa££w0rd" /SQLSVCACCOUNT="ESASS\SQLServiceAccount" /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 6 details each of the optional parameters that can be configured when installing the Database Engine and Integration Services.

Table 6. Optional Parameters

Parameter

Description

/ENU

Dictates that the English version of SQL Server will be used. Use this switch if you are installing the English version of SQL Server on a server with localized settings and the media contains language packs for both English and the localized operating system.

/UPDATEENABLED

Specifies if Product Update functionality will be used. Pass a value of 0 to disable or 1 to enable.

/MICROSOFTUPDATE

Specifies that Microsoft Update will be used to check for updates

/UpdateSource

Specifies a location for Product Update to search for updates. A value of MU will search Windows Update, but you can also pass a file share or UNC.

/CONFIGURATIONFILE

Specifies the path to a configuration file, which contains a list of switches and parameters, so that they do not have to be specified inline when running setup.

/ERRORREPORTING

Determines if error reporting will be sent to Microsoft. Set to 0 for off or 1 for on.

/INDICATEPROGRESS

When this switch is used, the setup log is piped to the screen during installation.

/INSTALLSHAREDDIR

Specifies a folder location for 64-bit components that are shared between instances

/INSTALLSHAREDWOWDIR

Specifies a folder location for 32-bit components that are shared between instances. This location cannot be the same as the location for 64-bit shared components.

/INSTANCEDIR

Specifies a folder location for the instance

/INSTANCEID

Specifies an ID for the instance. It is considered bad practice to use this parameter.

/PID

Specifies the PID for SQL Server. Unless the media is pre-pidded, failure to specify this parameter will cause Evaluation edition to be installed.

/qs

Runs the installation in Quiet Simple mode, to avoid the need for interaction. Cannot be specified when the /qs parameter is specified

/UIMODE

Specifies if only the minimum amount of dialog boxes should be displayed. Cannot be used in conjunction with either /q or /qs

/SQMREPORTING

Specifies if SQL Reporting will be enabled. Use a value of 0 to disable or 1 to enable.

/HIDECONSOLE

Specifies that the console window should be hidden

/AGTSVCSTARTUPTYPE

Specifies the startup mode of the SQL Agent Service. This can be set to Automatic, Manual, or Disabled.

/BROWSERSVCSTARTUPTYPE

Specifies the startup mode of the SQL Browser Service. This can be set to Automatic, Manual, or Disabled.

/INSTALLSQLDATADIR

Specifies the default folder location for instance data

/SAPWD

Specifies the password for the SA account. This parameter is used when /SECURITYMODE is used to configure the instance as mixed-mode authentication. This parameter becomes required if /SECURITYMODE is set to SQL.

/SECURITYMODE

Use this parameter, with a value of SQL, to specify mixed mode. If you do not use this parameter, Windows authentication will be used.

/SQLBACKUPDIR

Specifies the default location for SQL Server backups

/SQLCOLLATION

Specifies the collation the instance will use

/ADDCURRENTUSERASSQLADMIN

Adds the security context that is running setup.exe to the sysadmin server role. Cannot be specified if /SQLSYSADMINACCOUNTS is specified. I recommend using /SQLSYSADMINACCOUNTS for consistency.

/SQLSVCSTARTUPTYPE

Specifies the startup mode of the Database Engine Service. This can be set to Automatic, Manual, or Disabled.

/SQLTEMPDBDIR

Specifies a folder location for TempDB data files

/SQLTEMPDBLOGDIR

Specifies a folder location for TempDB log files

/SQLTEMPDBFILECOUNT

Specifies the number of TempDB data files that should be created. The maximum value for this parameter equates to the maximum number of cores in the server

/SQLTEMPDBFILESIZE

Specifies the initial size of the TempDB data files

/SQLTEMPDBFILEGROWTH

Specifies the growth increment for TempDB data files

/SQLTEMPDBLOGFILESIZE

Specifies the initial size of the TempDB transaction log

/SQLTEMPDBLOGFILEGROWTH

Specifies the growth increment for TempDB transaction log

/SQLUSERDBDIR

Specifies a default location for the data files or user databases

/SQLSVCINSTANTFILEINIT

Specifies that Instant File Initialization should be enabled for the instance

/SQLUSERDBLOGDIR

Specifies the default folder location for log files or user databases

/FILESTREAMLEVEL

Used to enable FILESTREAM and set the required level of access. This can be set to 0 to disable FILESTREAM, 1 to allow connections via SQL Server only, 2 to allow IO streaming, or 3 to allow remote streaming. The options from 1 to 3 build on each other, so by specifying level 3, you are implicitly specifying levels 1 and 2 as well.

/FILESTREAMSHARENAME

Specifies the name of the Windows file share in which FILESTREAM data will be stored. This parameter becomes required when /FILESTREAMLEVEL is set to a value of 2 or 3.

/FTSVCACCOUNT

Specifies the service account that will be used to run the Full Text service

/FTSVCPASSWORD

Specifies the password of the service account that will be used to run the Full Text service

/ISSVCACCOUNT

Specifies the service account that will be used to run the Integration Services service

/ISSVCPASSWORD

Specifies the password of the service account that will be used to run the Integration Services service

/ISSVCStartupType

Specifies the startup mode of the Integration Services service. This can be set to Automatic, Manual, or Disabled.

/NPENABLED

Specifies if Named Pipes should be enabled. This can be set to 0 for disabled or 1 for enabled.

/TCPENABLED

Specifies if TCP will be enabled. Use a value of 0 to disable or 1 to enable.

/PBENGSVCACCOUNT

Specifies the password of the service account that will be used to run the PolyBase service

/PBDMSSVCPASSWORD

Specifies the service account that will be used to run the PolyBase service

/PBENGSVCSTARTUPTYPE

Specifies the startup mode of the PolyBase service. This can be set to Automatic, Manual, or Disabled.

/PBPORTRANGE

Specifies a port range, of at least six ports, to be used by the PolyBase service

/PBSCALEOUT

Specifies if the instance will be part of a PolyBase scale-out group

Note

Parameters used for the installation of Analysis Services and Reporting Services are beyond the scope of this article.

 

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 Files\Microsoft SQL Server\130\Setup Bootstrap\Log\YYYYMMDD_HHMMSS. This path assumes that you installed SQL Server in the default location. The timestamp relates to the time that the installation began.

Tip

For older versions of SQL Server, replace the folder 130 with the appropriate version number. For example, SQL Server 2014 would be 120, and SQL Server 2012 would be 110.

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

Tip

You will notice that the following parameters are specified: MATRIXCMBRICKCOMMPORT, MATRIXCMSERVERNAME, MATRIXNAME, COMMFABRICENCRYPTION, COMMFABRICNETWORKLEVEL, and COMMFABRICPORT. These parameters are intended for internal use by Microsoft only and should be ignored.

Listing 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 Files\Microsoft 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 Files\Microsoft 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: Domain\User 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. All parameters not listed will use default values.

Table 7. Required Instance Configuration

Parameter

Required Value

Static

/FEATURES

SQLEngine,Replication,Conn,

IS,Adv_SSMS

Yes

/INSTANCENAME

As required

No

/AGTSVCACCOUNT

As required

No

/AGTSVCPASSWORD

As required

No

/SQLSVCACCOUNT

As required

No

/SQLSVCPASSWORD

As required

No

/SQLSYSADMINACCOUNTS

As required

No

/q

TRUE

Yes

/NPENABLED

1

Yes

/ISSVCACCOUNT

As required

No

/ISSVCPASSWORD

As required

No

/ISSVCStartupType

As required

No

/SQLSVCINSTANTFILEINIT

TRUE

Yes

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 3 shows the configuration file for our new build.

Listing 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:\SQLServer2016\Patches"

; 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 Files\Microsoft 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 Files\Microsoft 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: Domain\User 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.

Tip

The update files will have to be unzipped, in order to be applied by the setup utility.

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 4 demonstrates the command that we would use to install an instance named ESPROD4.

Tip

Change the service account details to match your own configuration, before running the script.

Listing 4. Install an Instance with a Configuration File

.\setup.exe /INSTANCENAME="ESPROD4" /SQLSYSADMINACCOUNTS="ESASS\SQLAdmin" /AGTSVCACCOUNT="ESASS\SQLServiceAccount" /AGTSVCPASSWORD="Pa££w0rd" /SQLSVCACCOUNT="ESASS\SQLServiceAccount" /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, 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 5.

 Listing 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 6.

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

Next we discuss another important aspect - Microsoft SQL Server Instance Configuring

Вас заинтересует / Intresting for you:

Introduction to the Family of ...
Introduction to the Family of ... 28 views Doctor Thu, 10 Oct 2019, 10:04:22
Automating the SQL Server Inst...
Automating the SQL Server Inst... 154 views Doctor Sun, 02 Dec 2018, 08:44:21
Introduction to SQL Server 201...
Introduction to SQL Server 201... 114 views Antoni Sat, 02 Mar 2019, 05:42:31
Installing a MS SQL 2016 failo...
Installing a MS SQL 2016 failo... 427 views Arrived Topple Thu, 23 Aug 2018, 11:09:22

Comments on Automating Microsoft SQL Server Instance Builds

Be the first to comment
Please login to comment