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
