How To – Setup SQL 2016 – Part III – Unattended Install

Griffon

Hello World, 

In Part I, we have seen how to perform a manual installation of SQL Server 2016. In part II, we have seen how to perform an unattended installation using the generated configuration ini file during a manual installation setup.  In this part, we will simply check how to perform an unattended installation through the command line only.  In some cases, you might not have the time to perform a manual installation, and/or use the generated ini file.  However, you still need to automate and perform an unattended installation. So, how do you do this ?  

The solution to your problem is to simply use the command line approach and provide all the necessary parameters to the Setup.exe command line.. 

Let’s have a look at which parameters can be used….  

Command line unattended installation Process

As mentioned above, you might not have the option to use the configuration ini file ready to be used but you need to automate as much as possible your SQL deployment. So, the best option in this case is to use the command line approach.  Below, you can see the most useful parameters that can be used while performing an unattended installation through command line.   You cand find all the possible switches and their usage at the following location 

setup.exe /Q /ACTION=INSTALL 
/IACCEPTSQLSERVERLICENSETERMS 
/FEATURES=SQLEngine,RS
/INSTANCENAME=<%Name%> /INSTANCEDI="c:\Program Files\Microsoft SQL Server" 
/INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
/INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server" 
/SQLSVCAccount="<%NAME%>
/SQLSYSADMINACCOUNTS="BUILTIN\Administrators" 
/SQLSVCPASSWORD="<%PWD%>
/AGTSVCACCOUNT="NT AUTHORITY\Network Service" 
# if you do not specify this, it will be using windows authentication
/SECURITYMODE=SQL 
/SAPWD=<%PWD%>
/SQLTEMPDIR="C:\Program Files\Microsoft SQL Server\TEMP\"
/SQLUSEDBDIR="C:\Program Files\Microsoft SQL Server\DATA\"
/SQLUSERDBLOGDIR=C:\Program Files\Microsoft SQL Server\LOG\"
/SQLINSTANFILEINIT="True"

 

In the example above, you see that you have to provide the password for the service accounts (if used) in clear text.  In part II, we have mentioned that the fact that the password is visible on the command prompt can be considered as a security risk.  A person with bad intention could see the password and use it to access your infrastructure.  To mitigate the risk, we would use two possible options : 

  • use local service accounts and updated them as required after the installation of SQL server 
  • use a PowerShell script to get prompted for the credentials and perform the installation in a secure way 

An example of Powershell Script could be something like this 

------------------------------------------------------------------------------------
##  Script_Name : DefautlSQLInstall.ps1
##  Description :  Perform a standard installation of SQL Server using default location for files 
##  Written by : Griffon
##  Date : June 2017
##  Version : 0.1  - For Illustration purpose only - have not been tested !!!!
## -----------------------------------------------------------------------------------

$password = Read-Host -assecurestring "Please enter your password for SQL Service Accounts"
$password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))
## - Should be one line but for readability has been splitted --- #
$StrCMD=".\setup.exe /Q /ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS 
/FEATURES=SQLEngine,RS /INSTANCENAME=MSSQLSERVER 
/SQLSVCAccount='Disaster\SQLSVC' 
/SQLSYSADMINACCOUNTS='BUILTIN\Administrators' 
/SQLSVCPASSWORD='$password' /AGTSVCACCOUNT='Disaster\SQLAGENT' 
/AGTSVCPASSWORD='$password' /RSSVCACCOUNT='DISASTER\SQLSVC'
/RSSVCPASSWORD='$password' 
/SQLSVCINSTANTFILEINIT='False'"
Invoke-Expression $StrCMD

 

If you need to change from default path location, you can adapt the command line and use the following script (again this is for illustration purposes- so please test it before) 

------------------------------------------------------------------------------------
##  Script_Name : Custom_SQL_Install.ps1
##  Description :  Perform a Custom installation of SQL Server using D Drive for files
##  Written by : Griffon
##  Date : June 2017
##  Version : 0.1  - For Illustration purpose only - have not been tested !!!!
## -----------------------------------------------------------------------------------

#Request one or multiple passwords..... 
$password = Read-Host -assecurestring "Please enter your password"
$password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))

#Prepare the command the be executed

$StrCMD=".\setup.exe /Q /ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine,RS 
/INSTANCENAME='MSSQLSERVER' 
/INSTANCEDIR='c:\Program Files\Microsoft SQL Server' 
/INSTALLSHAREDWOWDIR='C:\Program Files (x86)\Microsoft SQL Server' 
/INSTALLSHAREDDIR='C:\Program Files\Microsoft SQL Server' 
/SQLSVCAccount='Disaster\SVCSQL' 
/SQLSYSADMINACCOUNTS='BUILTIN\Administrators' 
/SQLSVCPASSWORD=$password /AGTSVCACCOUNT='Disaster\SQLAGENT' 
/AGTSVCPASSWORD=$password 
/SQLTEMPDBDIR='D:\Microsoft SQL Server\TEMP' 
/SQLUSERDBDIR='D:\Microsoft SQL Server\DATA' 
/SQLUSERDBLOGDIR='D:\Microsoft SQL Server\LOG' 
/SQLSVCINSTANTFILEINIT='False'"

Invoke-Expression $StrCMD

 

As you can see, using PowerShell scripting, we can prepare the deployment of SQL in an unattended way and in an efficient way.  Obviously, you would need to come up with your own customize script and add any actions you would like to automate.  For example, in the scripts provided above for illustration, we could add an additional line that would be used to configure the firewall something like this…. 

New-NetFirewallRule -DisplayName “SQL Server Traffic ” -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow

or you could use GPO that would ensure that the port 1433 is open for all SQL Servers in your organization.  

Final Notes

As you have seen, there are multiple ways to perform a SQL server installation.  The easiest and classical way is the manual installation process.  Another way is to use the configuration ini file.  I have to admit that I never used this approach and I had to struggle a bit to have a ready to use configuration ini file.  The most versatile option might be the command line as you can execute it on the fly and perform an automated installation.  Combining the command line with PowerShell technology, you could propably automate the process to a maximum.  

This is where the Desired Configuration State could be another good option.  Desired Configuration is basically using Powershell language to apply a certain configuration on a system.  We never used Desired configuration and if we have the opportunity, we would try to perform an automated installation of SQL through DSC.   As you can imagine, automating SQL installation could be an ongoing subject…… 

Till next time 

See ya

 

 

Leave a Reply