Situation
The CFO finally agreed to your department’s request for additional database server hardware. Now you have a completely new database environment that needs to be built consisting of several database servers. The Infrastructure Team has finished their part of allocating the resources necessary and installing the Operating System. They configured the servers so that the hard drive assignments are all identical. Management agrees that the database server version to be installed is identical, pretty much the same server builds except the RAM and CPU allocations based upon expected workloads. All of the settings are either identical or very similar.
As you are building one of the new servers you can take screen shots of every step, put them into a Word document or some such and click the appropriate buttons, and manually enter the data according to the captured steps. The other option is to use something you may have heard of that will allow for a consistent build and will also save you from making errors at 0300 hours and on your 6th build when you are really tired, and your eyes are getting a little buggy from repeatedly seeing the same screens over and over again. It is a configuration file called, cunningly enough, ConfigurationFile.ini and it is generated by SQL Server during the install process.
What is a ‘ConfigurationFile.ini’ file and how do I get one?
It is a file that is created every time a SQL Server Database Engine is installed. The path to it is provided right before you click ‘Install’. Here is the final screen when installing a named instance of SQL Server 2019 Developer Edition. Right on the very bottom you can see the path where this file is located. The second image is the close up of the same pathing information, just isolated to make it easier to find. Every install of the database engine produces this file, and your existing servers should have this file as well based upon the version installed
Figure 1 Configuration setting for SQL Server 2019 install
Figure 2 Close up of ConfigurationFile.ini location
Below is my Configuration File, particulars altered of course. In this example there are 56 switches/options that will need to be reviewed and set for each installation. We will not be covering any of them in detail as that is covered off better here by Microsoft.
Contents of a Configuration File
;SQL Server 2019 Configuration File
[OPTIONS]
; By specifying this parameter and accepting Microsoft Python Open and Microsoft Python 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=”Install”
; 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=”True”
; Specifies that SQL Server Setup should not display the privacy statement when ran from the command line.
SUPPRESSPRIVACYSTATEMENTNOTICE=”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=”False”
; 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=”True”
; Specifies that SQL Server Setup should not display the paid edition notice when ran from the command line.
SUPPRESSPAIDEDITIONNOTICE=”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=”MU”
; 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
; 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 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=”SAMPLEDBSERVER”
; Specify the root installation directory for shared components. This directory remains unchanged after shared components are already installed.
INSTALLSHAREDDIR=”F:\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=”F:\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=”SAMPLEDBSERVER”
; Account for SQL Server CEIP service: Domain\User or system account.
SQLTELSVCACCT=”NT Service\SQLTELEMETRY$SAMPLEDBSERVER”
; Startup type for the SQL Server CEIP service.
SQLTELSVCSTARTUPTYPE=”Automatic”
; Specify the installation directory.
INSTANCEDIR=”S:\Program Files\Microsoft SQL Server”
; Agent account name
AGTSVCACCOUNT=”NT Service\SQLAgent$SAMPLEDBSERVER”
; 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=”Manual”
; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
FILESTREAMLEVEL=”0″
; The max degree of parallelism (MAXDOP) server configuration option.
SQLMAXDOP=”12″
; 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=”DomainName\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”
; Windows account(s) to provision as SQL Server system administrators.
SQLSYSADMINACCOUNTS=”YourDomain\YourUserIDGoesHere”
; The default is Windows Authentication. Use “SQL” for Mixed Mode Authentication.
SECURITYMODE=”SQL”
; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT=”8″
; 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=”E:\Data”
; Default directory for the Database Engine backup files.
SQLBACKUPDIR=”Z:\DB Backup”
; Default directory for the Database Engine user databases.
SQLUSERDBDIR=”E:\Data”
; Default directory for the Database Engine user database logs.
SQLUSERDBLOGDIR=”H:\Logs”
; Directories for Database Engine TempDB files.
SQLTEMPDBDIR=”E:\TempDB”
; Directory for the Database Engine TempDB log files.
SQLTEMPDBLOGDIR=”H:\Logs”
; Provision current user as a Database Engine system administrator for SQL Server 2019 Express.
ADDCURRENTUSERASSQLADMIN=”False”
; Specify 0 to disable or 1 to enable the TCP/IP protocol.
TCPENABLED=”0″
; Specify 0 to disable or 1 to enable the Named Pipes protocol.
NPENABLED=”0″
; Startup type for Browser Service.
BROWSERSVCSTARTUPTYPE=”Manual”
; Use SQLMAXMEMORY to minimize the risk of the OS experiencing detrimental memory pressure.
SQLMAXMEMORY=”16384″
; Use SQLMINMEMORY to reserve a minimum amount of memory available to the SQL Server Memory Manager.
SQLMINMEMORY=”0″
I have a Configuration File, now what?
There are two ways that this file can be leveraged. While on the setup screen click on ‘Advanced’ then select ‘Install based on configuration file’. Provide the path to the configuration file and you are good to start the installation process.
Figure 3 Advanced installation options for SQL Server
Another method to complete a SQL Server install is to use the command prompt coupled with the configuration file. To do this, open a command prompt window and navigate to the installation folder for SQL Server. As passwords are not stored in the configuration file you will need to be able to provide those as part of the command prompt options.
In this instance I would change directory to my ‘I$’ as I am using a Development iso for installation purposes and it is a temporarily mounted DVD drive. The specific passwords have been modified, so the command line in the command prompt window would be:
I:> Setup.exe /SQLSVCPASSWORD=”SVCAccountPassword” /SAPWD=”SysAdmPassword” /ConfigurationFile=S:\NewSQLServer3\ConfigurationFile.ini
If you want to perform and unattended installation you will need to include /Q or /QS along with /IACCEPTSQLSERVERLICENSETERMS options. /Q is known as Quiet Mode and does not a user interface and /QS is QuietSimple Mode and shows the UI but does not require any input. These options can also be set in the Configuration File but keep in mind that you can override the Configuration File’s options by providing the options in the command line as the command line will override the Configuration File parameters.
It is also worth mentioning that this installation process can be used for far more than just a basic engine installation, this process can be used to add a node to a fail-over cluster, install Analysis Services and Integration Services.
Why Configuration Files are very useful?
For that, let us go back to what we are trying to accomplish. We have a several new machines, be it new bare metal boxes or a new single large physical host with new virtual machines carved out on it. Either way, we have a number of new SQL Servers to build. Given that they probably all have different configurations necessary due to different hardware allocations it can get a bit confusing as you switch from machine to machine to monitor the installation process. Mistakes are possible especially when installing several servers simultaneously. I would rather front-end load the planning before I start the installation process. By creating a ConfigurationFile.ini file for each server to be installed ensures that the configuration settings are correct on each server. It also allows me to perform simultaneous installations across multiple servers with installation of all of the configuration, file placements, accounts, etc. etc. being error free. Not sure about you, but anything that can simplify the installation process is always a good thing.
Written By:
Robert Koros
Robert is a Senior Database Consultant with a track record of success as a Senior Database Administrator / Architect with proven ability to optimize performance of corporate databases, ensure full accessibility of data, and streamline dataflow. He possess over 25 years’ hands-on SQL Server database management experience in the Oil & Gas and Professional Services industries with clients in the Financial Services, Healthcare (Electronic Medical Records), Manufacturing and Public Sector.
More By This Author
