SQL Server 2017 Unattended Installation on a Windows Server 2016 Failover Cluster via Command Line Part 1

By:   |   Comments (4)   |   Related: 1 | 2 | > Clustering


Problem

In a previous tip on Installing SQL Server 2017 on Windows Server 2016 Failover Cluster, I have seen how to install a SQL Server 2017 on a Windows Server 2016 failover cluster (WSFC). Manually installing a SQL Server 2017 failover clustered instance (FCI) can be very tedious and time consuming. I want to reduce the amount of time and effort that goes into deploying a SQL Server 2017 FCI. How do I go about performing an unattended installation of a SQL Server 2017 failover cluster on Windows Server 2016?

Solution

Performing an unattended installation of your SQL Server instances – or any server platform - can provide consistent deployments throughout the organization and reduce the overall total cost of ownership (TCO). The business benefits of automation include reduced deployment time and effort, reduced human errors, and faster deployments. You should always think of ways to automate a process especially if it is something that you have to perform repeatedly.

There are two ways to perform an unattended installation of SQL Server. The first one is to provide installation parameters and the second one is to use a Configuration File. Both approaches require installing SQL Server from the command line.

Installing a SQL Server 2017 Failover Clustered Instance (FCI) using Command-Line Parameters

Installing SQL Server 2017 FCI from the command line is similar to installing a standalone instance from the command line as described in this tip. However, the parameters are a bit different. As described in the tip Installing SQL Server 2017 on Windows Server 2016 Failover Cluster, you have to consider SQL Server FCI-specific parameters such as the SQL Server virtual network name and the virtual IP address. Below is a list of command-line parameters that I use when deploying a SQL Server 2017 FCI:

  • The /ACTION parameter is used to perform an installation. But unlike a standalone instance, the supported value for installing a SQL Server 2017 FCI is InstallFailoverCluster. Usage of this parameter with the corresponding value is as follows: /Action=InstallFailoverCluster
  • The /UpdateEnabled parameter is used to tell the setup process to discover and include product updates. If there are discovered updates, they will be included as part of the installation. The tip Slipstream Installation of Service Packs and Cumulative Updates on SQL Server 2016 and Higher describes how you can include SQL Server updates to further reduce deployment time and effort. Usage of this parameter with the corresponding value is as follows: /UpdateEnabled=True
  • The /UpdateSource parameter is used to tell the setup process to search the specified location for product updates. Usage of this parameter with the corresponding value is as follows: /UpdateSource=”location”
  • The /SQLSVCACCOUNT and /SQLSVCPASSWORD parameters are used for specifying the SQL Server service account and the corresponding password, respectively.
  • The /AGTSVCACCOUNT and /AGTSVCPASSWORD parameters are used for specifying the SQL Server Agent service account and the corresponding password, respectively.
  • The /FEATURES parameter specifies which SQL Server components to install. In a standalone instance, you can choose the Database Engine Services components individually. In a SQL Server 2017 FCI, selecting the Database Engine Services will also include the other components: SQL Server Replication, Full-Text and Semantic Extractions for Search, and Data Quality Services. Usage of this parameter with the corresponding values is as follows: /FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,CONN
  • The /INSTANCEID parameter specifies the Instance ID used to identify installation directories and registry keys for your SQL Server instance. By default, the instance name is used as the Instance ID value if not specified. For a default instance, the instance name and Instance ID values are MSSQLSERVER. Usage of this parameter with the corresponding value for a named instance is as follows: /INSTANCEID=”INST98”
  • The /INSTANCENAME parameter specifies the instance name of the SQL Server installation. Usage of this parameter with the corresponding value for a named instance is as follows: /INSTANCENAME=”INST98”
  • The /FAILOVERCLUSTERDISKS parameter specifies the list of shared disks to be included in the SQL Server 2017 FCI cluster resource group. If not specified, the setup process will use the first shared disk in the Available Storage cluster resource group as the default drive for all databases. You can retrieve this information in the Disks section of the Failover Cluster Manager and identifying the disks in the Available Storage cluster resource group. This is where you'll see the value of renaming the shared disk resources as done in Part 2 of this tip series.
failover cluster manager

Alternatively, you can use the Get-ClusterResource PowerShell cmdlet to list the shared disks in the Available Storage cluster resource group using the command below.

Get-ClusterResource | Where {$_.OwnerGroup -eq "Available Storage"}
powershell window

Usage of this parameter with the corresponding values is as follows: /FAILOVERCLUSTERDISKS="SQL_DATA_T"

If you want to include more than one shared disk in this SQL Server 2017 FCI, you can pass multiple values, separated by a space like this example: /FAILOVERCLUSTERDISKS="SQL_DATA_S" "SQL_DATA_T"

Note that you can only use shared disks in the Available Storage cluster resource group. The setup process will fail if you include shared disk resources that are already assigned to an existing cluster resource group.

  • The /FAILOVERCLUSTERNETWORKNAME parameter specifies the SQL Server virtual network name. It is used to identify the new SQL Server 2017 FCI on the network. Note that this should be unique on the network as it will be created as a virtual computer object (VCO) in Active Directory with a corresponding DNS record. Usage of this parameter with the corresponding values is as follows: /FAILOVERCLUSTERNETWORKNAME="TDPRDSQLCLS98"
  • The /FAILOVERCLUSTERIPADDRESSES parameter specifies the TCP/IP settings of the SQL Server virtual network name. The values are semicolon-delimited (;) and follow the format <IP Type>;<address>;<network name>;<subnet mask>. You can retrieve this information in the Networks section of the Failover Cluster Manager and identifying the cluster network configured with Cluster and Client. This is where you'll see the value of renaming the cluster network resources as done in Part 2 of this tip series.
failover cluster manager

Alternatively, you can use the Get-ClusterNetwork PowerShell cmdlet to list the shared disks in the Available Storage cluster resource group using the command below.

Get-ClusterNetwork | Where {$_.Role -eq "ClusterAndClient"}
powershell window

Usage of this parameter with the corresponding values for a single-subnet SQL Server 2017 FCI deployment is as follows: /FAILOVERCLUSTERIPADDRESSES="IPv4;172.16.0.98;LAN;255.255.0.0"

Be careful with the parameter values. An invalid IP address or subnet mask format will cause the setup process to fail.

  • The /FAILOVERCLUSTERGROUP parameter specifies the name of the cluster resource group/role for the SQL Server 2017 FCI. I usually include the full instance name to easily identify this from other SQL Server FCI installed in the same WSFC. Usage of this parameter with the corresponding values is as follows: /FAILOVERCLUSTERGROUP="SQL Server (TDPRDSQLCLS98\INST98)"
  • The /SQLSVCINSTANTFILEINIT parameter enables instant file initialization for SQL Server service account as described in this tip. Because this is a local permission assigned to an account, you need to explicitly do this on all the nodes in the SQL Server FCI. Usage of this parameter with the corresponding value is as follows: /SQLSVCINSTANTFILEINIT="True"
  • The /SQLSYSADMINACCOUNTS parameter specifies the account or security group that I want to add to the sysadmin fixed-server role. I usually specify an Active Directory security group to make managing SQL Server sysadmins a lot easier. Usage of this parameter with the corresponding value is as follows: /SQLSYSADMINACCOUNTS="TESTDOMAIN\SQLDBAs"
  • The /INSTALLSQLDATADIR parameter specifies the data directory for SQL Server data files and must be a drive letter of the shared disks listed in the /FAILOVERCLUSTERDISKSparameter. Usage of this parameter with the corresponding value is as follows: /INSTALLSQLDATADIR="T:"

If you have more than one shared disk resource listed in the /FAILOVERCLUSTERDISKS parameter, you can specify your preferred disk initially and just modify the placement of the data and log files accordingly when you create your databases.

  • The /IACCEPTSQLSERVERLICENSETERMS parameter is used to acknowledge acceptance of the end-user license agreement. This parameter is required if you are using either the /Q or /QS switches. I usually use the /Q parameter with the /INDICATEPROGRESS parameter to run the setup process in a quiet mode without any user interface but with progress indicator.

Any other parameters not specified will use the default values. The list of command-line parameters for SQL Server 2017 FCI installations can be found in this Microsoft documentation. The example command below is what I use for my deployments.

Setup.exe /Action=InstallFailoverCluster /UpdateEnabled=True /UpdateSource="\\CentralFileServer\SQLServer2017CUs\CU20" /SQLSVCACCOUNT="TESTDOMAIN\sqlservice" /SQLSVCPASSWORD="y0ur$ecUr3PAssw0rd" /AGTSVCACCOUNT="TESTDOMAIN\sqlservice" /AGTSVCPASSWORD="y0ur$ecUr3PAssw0rd" /FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,CONN /INSTANCEID="INST98" /INSTANCENAME="INST98" /FAILOVERCLUSTERDISKS="SQL_DATA_T" /FAILOVERCLUSTERNETWORKNAME="TDPRDSQLCLS98"  /FAILOVERCLUSTERIPADDRESSES="IPv4;172.16.0.98;LAN;255.255.0.0" /FAILOVERCLUSTERGROUP="SQL Server (TDPRDSQLCLS98\INST98)" /SQLSVCINSTANTFILEINIT="True" /SQLSYSADMINACCOUNTS="TESTDOMAIN\SQLDBAs" /INSTALLSQLDATADIR="T:" /IACCEPTSQLSERVERLICENSETERMS=1 /INDICATEPROGRESS /Q

Installing a SQL Server 2017 Failover Clustered Instance (FCI) using a Configuration File

A previous tip to Standardize SQL Server Installations with Configuration Files has provided a sample configuration file that you can use to install a SQL Server instance. However, it is for a standalone instance, not a SQL Server FCI. The best way to create a configuration file is to use the SQL Server Installation Wizard and walk through the process of installing a SQL Server 2017 FCI, providing the appropriate parameter values. You can then copy the ConfigurationFile.ini file that the wizard generated and use it as a parameter in your installation. The example below shows a sample configuration file for the same configuration specified in the section Installing a SQL Server 2017 Failover Clustered Instance (FCI) using Command-Line Parameters. Observe the parameters and their corresponding default values that were not specified in the previous section. The SQL Server Installation Wizard will automatically generate the parameters and their corresponding default values if not specified. Also, the UIMODE=”Normal” line has been commented so it doesn’t conflict with the use of the /Q parameter. 

;SQL Server 2017 Configuration File
[OPTIONS]
 
; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, you acknowledge that you have read and understood the terms of use. 
 
IACCEPTPYTHONLICENSETERMS="False"
 
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 
 
ACTION="InstallFailoverCluster"
 
; Specifies that SQL Server Setup should not display the privacy statement when ran from the command line. 
 
SUPPRESSPRIVACYSTATEMENTNOTICE="False"
 
; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, you acknowledge that you have read and understood the terms of use. 
 
IACCEPTROPENLICENSETERMS="False"
 
; 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"
 
; 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"
 
; 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"
 
; If this parameter is provided, then this computer will use Microsoft Update to check for updates. 
 
USEMICROSOFTUPDATE="False"
 
; 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="\\CentralFileServer\SQLServer2017CUs\CU20"
 
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, 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 shared components. 
 
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ
 
; Displays the command line parameters usage 
 
HELP="False"
 
; Specifies that the detailed Setup log should be piped to the console. 
 
INDICATEPROGRESS="True"
 
; 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 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), or Analysis Services (AS). 
 
INSTANCENAME="INST98"
 
; 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 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="INST98"
 
; Specify the installation directory. 
 
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
 
; Specifies a cluster shared disk to associate with the SQL Server failover cluster instance. 
 
FAILOVERCLUSTERDISKS="SQL_DATA_T"
 
; Specifies the name of the cluster group for the SQL Server failover cluster instance. 
 
FAILOVERCLUSTERGROUP="SQL Server (TDPRDSQLCLS98\INST98)"
 
; Specifies an encoded IP address. The encodings are semicolon-delimited (;), and follow the format <IP Type>;<address>;<network name>;<subnet mask>. Supported IP types include DHCP, IPV4, and IPV6. 
 
FAILOVERCLUSTERIPADDRESSES="IPv4;172.16.0.98;LAN;255.255.0.0"
 
; Specifies the name of the SQL Server failover cluster instance.  This name is the network name that is used to connect to SQL Server services. 
 
FAILOVERCLUSTERNETWORKNAME="TDPRDSQLCLS98"
 
; Agent account name 
 
AGTSVCACCOUNT="TESTDOMAIN\sqlservice"
 
; 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"
 
; Level to enable FILESTREAM feature at (0, 1, 2 or 3). 
 
FILESTREAMLEVEL="0"
 
; 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="TESTDOMAIN\sqlservice"
 
; 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"
 
; Windows account(s) to provision as SQL Server system administrators. 
 
SQLSYSADMINACCOUNTS="TESTDOMAIN\SQLDBAs"
 
; The number of Database Engine TempDB files. 
 
SQLTEMPDBFILECOUNT="1"
 
; 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"
 
; The Database Engine root data directory. 
 
INSTALLSQLDATADIR="T:"
 
; Add description of input argument FTSVCACCOUNT 
 
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$INST98"

The example command below shows installing a SQL Server 2017 FCI using the configuration file.

setup.exe /ACTION=InstallFailoverCluster /SQLSVCPASSWORD="y0ur$ecUr3PAssw0rd"  /AGTSVCPASSWORD="y0ur$ecUr3PAssw0rd" /ConfigurationFile=C:\ConfigurationFile.ini /IACCEPTSQLSERVERLICENSETERMS=1 /INDICATEPROGRESS /Q

You might be wondering why I still provided the passwords for both the SQL Server and SQL Server Agent service accounts in the command-line parameters. That is because the SQL Server Installation Wizard strips the configuration file of the passwords that you provide for security reasons. You wouldn't want anybody who has access to the configuration file to be able to see the credentials you used for the SQL Server service accounts.

Although this unattended installation process will yield a fully functioning SQL Server 2017 FCI, it is not highly availability yet because the SQL Server binaries are only installed on one of the nodes in the WSFC. To make it highly available, you still have to add the second node of the WSFC to the SQL Server FCI.

In the next tip in this series, you will add the second node in the SQL Server FCI.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, December 8, 2021 - 2:38:23 PM - bass_player Back To Top (89552)
Chris,

What entry was it? Can you provide more details?

Thanks.

Wednesday, December 8, 2021 - 5:20:03 AM - Chris Back To Top (89544)
I've found the problem. As this was a test in a LABO environment, an entry was missing in active directory.
Now it's working perfectly.

Regards,
Chris.

Friday, December 3, 2021 - 7:37:15 PM - bass_player Back To Top (89525)
What does the SQL Server Setup Log File say?

Friday, December 3, 2021 - 7:38:08 AM - Chris Back To Top (89515)
Hi Edwin,

Thanks for the great article.
I've tried an unattended installation but at the end I noticed that the SQL Server Agent was not part of the resources of the cluster. Same problem for SQL Server Launchpad. Is this by design or did I do something wrong?

Thanks,
Chris.














get free sql tips
agree to terms