Automating SQL Server Installation

By Rohit Khadka, Sr. Database Administrator

As a Production DBA, I have had the opportunity to do an upgrade/migration from SQL Server 2012 to SQL Server 2016. During the process, I realized that installing a new SQL Server on 20-30 servers in a short time was going to be challenging and monotonous. Going through the setup wizard and clicking next every time was just not going to be efficient. So, I almost always question myself before I do anything that requires a lot of effort – what is the opportunity cost to this? I guess my microeconomics class got in my head more than I wanted it to. 😊

            I remember one of the things that one of my ex co-workers used to repeat the phrase ‘Is the juice worth the squeeze?’. I suppose the answer really depends on the concept of opportunity cost. Is it worth spending 1-2 hours to build some scripts to automate the process? Or is it a waste of time if you only have 2 servers and you can do it in less than a couple of hours? For me at the time, the obvious answer was to build an automated script rather than spending hours, if not days doing the same thing repeatedly. So, I chose to find some ways to automate the SQL Server installation. Luckily, Microsoft was kind enough to allow installation using command prompt and provide us with a configuration file (.ini) file that stores defined installation parameters in the file during setup.

            But, how do you really automate the installation? That question remained. There were several ways to achieve this:

  • PowerShell
  • Batch File

I chose to go with the Batch File and here is how I did it:

Step 1: Create a configuration file (.ini) using the setup wizard:
            Starting with one of the servers that requires installation, create a configuration file going through the setup wizard until you get to the screen below ‘Ready to install’. On the screen, you will see the location where the configuration file is being saved under the configuration file path.

Step 2: Configure settings in the Configuration File

            Open the configuration file in an editor app like Notepad or Notepad++. We will need to change the following parameters in the file as per our requirements:

;SQL Server 2016 Configuration File
[OPTIONS]

; Required to acknowledge acceptance of the license terms.
IACCEPTSQLSERVERLICENSETERMS="True"

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

ACTION="Install"

; 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"

; 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="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"

; 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"

; 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 shared components. 

FEATURES=SQLENGINE,REPLICATION,FULLTEXT,CONN,IS,BC

; 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="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), Analysis Services (AS), or Reporting Services (RS). 

INSTANCENAME="MSSQLSERVER"

; 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="MSSQLSERVER"

; TelemetryUserNameConfigDescription 

SQLTELSVCACCT="NT Service\SQLTELEMETRY"

; TelemetryStartupConfigDescription 

SQLTELSVCSTARTUPTYPE="Automatic"

; TelemetryStartupConfigDescription 

ISTELSVCSTARTUPTYPE="Automatic"

; TelemetryUserNameConfigDescription 

ISTELSVCACCT="NT Service\SSISTELEMETRY130"

; Specify the installation directory. 

INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Agent account name 
AGTSVCACCOUNT="Domain\serviceAccount"

; Auto-start service after installation.  

AGTSVCSTARTUPTYPE="Automatic"

; Startup type for Integration Services. 

ISSVCSTARTUPTYPE="Automatic"

; Account for Integration Services: Domain\User or system account. 

ISSVCACCOUNT="NT Service\MsDtsServer130"

; 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="Domain\serviceAccount"

; 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="Domain\DBA"

; 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="128"

; 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="128"

; The Database Engine root data directory. 

INSTALLSQLDATADIR="D:\SQLMountPoint\SQLRoot"

; Default directory for the Database Engine user databases. 

SQLUSERDBDIR="D:\SQLMountPoint\SQLData"

; Default directory for the Database Engine user database logs. 

SQLUSERDBLOGDIR="D:\SQLMountPoint\SQLTLogs"

; Directories for Database Engine TempDB files. 

SQLTEMPDBDIR="D:\SQLMountPoint\SQLTemp"

; 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="Disabled"

; Add description of input argument EXTSVCACCOUNT 

EXTSVCACCOUNT="NT Service\MSSQLLaunchpad"

; Add description of input argument FTSVCACCOUNT 

FTSVCACCOUNT="NT Service\MSSQLFDLauncher"

Note: There maybe other parameters such as install directories that may need to be changed as per your need.

Step 3: Create a batch file to run the unattended install             If you are familiar with batch scripting, this step may be quite easy. Otherwise, there are many online resources out there if you need help with writing simple batch scripts. For me, this was a bit of a learning curve. Anyway, here is what I ended up with:

@echo off

set /p Env=Environment(Enter 1 for Prod or 2 for NonProd):
set /p SQLServiceAccount=SQL Service Account:
set /p SQLServiceAccountPwd=SQL Service Account password:
set /p SAPwd=SA password:
set sqlcmdpath="C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn"
set pshellpath="C:\Windows\system32\WindowsPowerShell\v1.0"

echo.

rem ----------- SQL Server 2016 Installation & Configuration ----------------

echo Mounting Disk Image....
IF %Env%==1 (
%pshellpath%\powershell.exe -Command Mount-DiskImage -ImagePath "C:\temp\2016\en_sql_server_2016_enterprise_with_service_pack_2_x64_dvd_12124051.iso"
)
IF %Env%==2 (
%pshellpath%\powershell.exe -Command Mount-DiskImage -ImagePath "C:\temp\DeveloperEdition\en_sql_server_2016_developer_with_service_pack_2_x64_dvd_12194995.iso"
)
IF ERRORLEVEL 1 goto :FAILED
echo Mount successful!

echo Installing SQL Server Database Engine and Other Components.......
start /wait E:\setup.exe /SQLSVCACCOUNT=%SQLServiceAccount% /SQLSVCPASSWORD=%SQLServiceAccountPwd% /AGTSVCACCOUNT=%SQLServiceAccount% /AGTSVCPASSWORD=%SQLServiceAccountPwd% /SAPWD=%SAPwd% /ConfigurationFile="C:\temp\Install\ConfigurationFile.ini"
echo SQL Server Installation successful!


echo Installing SQL Server Management Studio(SSMS).......
start /wait C:\temp\SSMS-Setup-ENU.exe /install /passive /norestart /log C:\temp\SSMSLogs\SSMS.log
IF ERRORLEVEL 1 goto :FAILED
echo SSMS Installation successful!

@echo off

echo.
echo. 
echo.
echo  ___________________________________________
echo *                                           *
echo *   Database Server Build is complete       *
echo *___________________________________________*
echo.
pause

goto :EOF

:FAILED
@echo off
echo.
echo.
echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
echo An error occurred!  SQL Server installation did not finish.
echo.
pause

You will need to copy this code and make necessary adjustments and save it as a .bat file. To run this file without permission issues, it is important to right click and ‘run as administrator’.

As you can see when we are setting variables, you will be prompted to provide values for the following:

Env: Enter 1 or 2 depending on the environment (Prod, Non-Prod) this will need to be installed.

SQLServiceAccount: Enter username for the service account which the services will run under.

SQLServiceAccountPwd: Enter username for the service account which the services will run under.

SAPwd: Enter password for the sys admin account.

Also, we can define these directly in the setup step below if we do not want to enter the values manually.

start /wait E:\setup.exe /SQLSVCACCOUNT=%SQLServiceAccount% /SQLSVCPASSWORD=%SQLServiceAccountPwd% /AGTSVCACCOUNT=%SQLServiceAccount% /AGTSVCPASSWORD=%SQLServiceAccountPwd% /SAPWD=%SAPwd% /ConfigurationFile="C:\temp\Install\ConfigurationFile.ini"


Step 4: Run the batch file

          Lastly, we will need to copy the configuration file and batch file we just created on the servers we will want to setup SQL Server. If you would like to automate the copy process, you could add a step to copy the files to the servers as well. To run the job, you could either schedule a SQL Server agent job or a windows task scheduler. Of course, it will depend on if you need to pass in the parameter values mention above.

            In a matter of less than an hour, you could have SQL Server running on multiple servers at the same time. I was happy with the results and I was able to use the “saved” time to do other DBA tasks. Everyone is happy!

Until next time…😊

Note: Below is a zip containing sample configuration file and batch script file to help you get started.
Automating SQL Server Installation sample files

Comments