Automating 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.
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 |
| Specifies the action that you wish to perform, such as install or repair. A complete list of actions is detailed in Table 2. |
| Confirms that you accept the SQL Server license terms |
| 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. |
| 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. |
| Specifies a name for the instance |
| Specifies the account that will run the SQL Server Agent service |
| Specifies the password of the account that will run the SQL Server Agent service |
| Specifies the account that will run the SQL Server Database Engine service |
| Specifies the password for the account that will run the SQL Server Database Engine service |
| Specifies the Windows security context(s) that will be given administrator permissions to the Database Engine |
| 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 |
| Installs a stand-alone instance |
| Prepares a vanilla stand-alone image, with no account, computer, or network details specified |
| Completes the installation of a prepared stand-alone image by configuring account-, computer-, and network-related settings |
| Upgrades an instance from SQL Server 2008, 2012, or 2014 |
| Upgrades a SQL Server 2014 from a lower edition, such as Developer Edition to Enterprise Edition, or Enterprise Edition to Enterprise Core |
| Repairs a corrupt instance |
| Rebuilds corrupted system databases |
| Uninstalls a stand-alone instance |
| Installs a failover clustered instance |
| Prepares a vanilla clustered image, with no account, computer, or network details specified |
| Completes the installation of a prepared clustered image by configuring account-, computer-, and network-related settings |
| Adds a node to a failover cluster |
| 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 |
| The Database Engine, including Full Test, Replication, and PolyBase components |
| The Database Engine, without its related components |
| Full test components |
| Replication components |
| PolyBase components |
| R Services |
| The components required to perform install Data Quality Server |
| Analysis Services |
| Reporting Services |
| Data Quality Client |
| Integration Services |
| Master Data Services |
| Microsoft R Server |
| All client tools and Books Online |
| Backward compatibility components |
| Books Online component |
| Connectivity components |
| SQL Server Management Studio, SQLCMD, and the SQL Server PowerShell provider |
| SSMS support for AS, RS, and IS; Profiler; Database Engine Tuning Advisor; and Utility management |
| Distributed Replay Controller |
| Distributed Replay Client |
| SDK for SQL Server Native Client |
| 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 |
| Installs SSAS in PowerPivot mode into an existing SharePoint farm |
| Installs SSAS in PowerPivot mode into a new SharePoint farm and configures the farm |
| 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 |
| /SWITCH |
| /PARAMETER=true/false |
| /PARAMETER=0/1 |
| /PARAMETER="Value" |
| /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 |
| 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. |
| Specifies if Product Update functionality will be used. Pass a value of 0 to disable or 1 to enable. |
| Specifies that Microsoft Update will be used to check for updates |
| 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. |
| 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. |
| Determines if error reporting will be sent to Microsoft. Set to 0 for off or 1 for on. |
| When this switch is used, the setup log is piped to the screen during installation. |
| Specifies a folder location for 64-bit components that are shared between instances |
| 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. |
| Specifies a folder location for the instance |
| Specifies an ID for the instance. It is considered bad practice to use this parameter. |
| Specifies the PID for SQL Server. Unless the media is pre-pidded, failure to specify this parameter will cause Evaluation edition to be installed. |
| Runs the installation in Quiet Simple mode, to avoid the need for interaction. Cannot be specified when the /qs parameter is specified |
| Specifies if only the minimum amount of dialog boxes should be displayed. Cannot be used in conjunction with either /q or /qs |
| Specifies if SQL Reporting will be enabled. Use a value of 0 to disable or 1 to enable. |
| Specifies that the console window should be hidden |
| Specifies the startup mode of the SQL Agent Service. This can be set to Automatic, Manual, or Disabled. |
| Specifies the startup mode of the SQL Browser Service. This can be set to Automatic, Manual, or Disabled. |
| Specifies the default folder location for instance data |
| 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. |
| Use this parameter, with a value of SQL, to specify mixed mode. If you do not use this parameter, Windows authentication will be used. |
| Specifies the default location for SQL Server backups |
| Specifies the collation the instance will use |
| 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. |
| Specifies the startup mode of the Database Engine Service. This can be set to Automatic, Manual, or Disabled. |
| Specifies a folder location for TempDB data files |
| Specifies a folder location for TempDB log files |
| 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 |
| Specifies the initial size of the TempDB data files |
| Specifies the growth increment for TempDB data files |
| Specifies the initial size of the TempDB transaction log |
| Specifies the growth increment for TempDB transaction log |
| Specifies a default location for the data files or user databases |
| Specifies that Instant File Initialization should be enabled for the instance |
| Specifies the default folder location for log files or user databases |
| 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. |
| 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. |
| Specifies the service account that will be used to run the Full Text service |
| Specifies the password of the service account that will be used to run the Full Text service |
| Specifies the service account that will be used to run the Integration Services service |
| Specifies the password of the service account that will be used to run the Integration Services service |
| Specifies the startup mode of the Integration Services service. This can be set to Automatic, Manual, or Disabled. |
| Specifies if Named Pipes should be enabled. This can be set to 0 for disabled or 1 for enabled. |
| Specifies if TCP will be enabled. Use a value of 0 to disable or 1 to enable. |
| Specifies the password of the service account that will be used to run the PolyBase service |
| Specifies the service account that will be used to run the PolyBase service |
| Specifies the startup mode of the PolyBase service. This can be set to Automatic, Manual, or Disabled. |
| Specifies a port range, of at least six ports, to be used by the PolyBase service |
| 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
, andCOMMFABRICPORT
. 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 |
| SQLEngine,Replication,Conn, IS,Adv_SSMS | Yes |
| As required | No |
| As required | No |
| As required | No |
| As required | No |
| As required | No |
| As required | No |
| TRUE | Yes |
| 1 | Yes |
| As required | No |
| As required | No |
| As required | No |
| 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