Automating the installation of SQL Server I am sure is an ask of every DBA team. There are many different processes that companies select to automate server delivery such as Ansible or Rundeck. Our team, like many others were tasked with creating process for automating the installation of SQL server. We chose to complete this using PowerShell as this would allow us to use any Server Build Automation software to call the PowerShell script and maintain the standards that are already setup and tested. Before SQL can be installed, a Confirguation.ini file needs to be generated that follows your companies standards. I will not be supplying an an example of the Configuration.ini but I will post examples of settings that were changed after the file was generated.
Microsoft's Help page for the Configuration.ini file:
To generate the Configuration.ini file. Launch the SQL Server installation manually and follow the steps in the wizard until you get to the step that says "Ready to Install". Displayed on the page is the location of the Configuration.ini file that you are able to copy and modify to work with the PowerShell installation script.
Here are some changes that we chose to make to the Configuration.ini with the explanation of why it was done.
; Specifies that SQL Server Setup should not display the privacy statement when ran from the command line.
SUPPRESSPRIVACYSTATEMENTNOTICE="True"
; 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"
; Accept SQL 2016 License Terms
IACCEPTSQLSERVERLICENSETERMS="True"
These lines accept all of the license terms so that the installation can proceed.
; Setup will not display any user interface.
QUIET="False"
; Setup will display progress only, without any user interaction.
QUIETSIMPLE="True"
This will show you a progress window during installation, helpful if running the PowerShell script manually. If you would like it completely silent with no progress window, switch the true and false values.
; 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=.\
I use these values to let the installer know to look in the installation directory for any updates and install them along side of the base install.
; 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="INSTANCE_NAME_TO_BE_REPLACED"
; 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="INSTANCE_NAME_TO_BE_REPLACED"
The reason "INSTANCE_NAME_TO_BE_REPLACED" is used here is that the script that launches the install will write this value in before launching Setup.exe
; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT="4"
If this value is not supplied, SQL determines the optimal TempDB file count at installation time. I suggest you comment out or remove this line or you will end up with a static number of TempDB files on every install.
There are a few more changes that I made to the defaults but they are more in line with the standards set out by my current organization. Review the rest of the file to determine if it makes sense for you.
Once you are satisfied with the Configuration.ini, we can switch our attention to the SQL Server Installation. I will post an example of the PowerShell script that is used in our environment. You would need to customize it to work in your company as it expects a certain filesystem structure to work properly. Right now the script is designed to launched by a DBA but it can easily be adapted to run fully automated.
The script checks to make sure that the data volumes are formatted with a 64k Block size and will prompt you to format the volume if is not already:
The service accounts and instance name can be provided as parameters when calling the script or if none are provided, the script will prompt you to enter them in:
.\Install_SQL.ps1 -InstanceName "SQL2016Instance" -EngineServiceAccount "svc.2K16eng" -EngineServicePassword "ENPass" -AgentServiceAccount "svc.2K16ag" -AgentServicePassword "AGPass" -ISServiceAccount "svc.2K16is" -ISServicePassword "ISPass"
The script will then check each account against AD to verify that the credentials provided are correct. After that, it will generate a SA account password and launch the installation. It will record the SA Password in the installation log file.
The script posted is designed to work with 2016 currently but can easily be modified to work with any version or multiple versions as needed.
Here is the PowerShell Script Installation Script:
