Update - PowerShell Script for SQL Server Cluster/Stand Alone unattended Install and Uninstall - SQL 2014/SQL 2016 Support Added

For a long time I have been using different scripts for SQL Server install based on the various user requirements, with my latest client requirement I decided to create a master script for unattended install either in Clustered environment or  as Standalone install. The script carters installation of all SQL related services (DB Engine, SSIS, SSAS or SSRS) and Client components including (MDS and DQC) based on user request.  Basic idea was to create a SQL environment on a click of a button by any IT Personnel. 

The script creates a SQL configuration file image for install so we can easily reuse it in case of DR. The script also monitors install the script monitors the Install Log in the \Setup Bootstrap\Log folder and will return the status with relevant errors.

  

Using this SQL Server Install Script we perform the following tasks,

1. Pre-Install Task – Create SQL Server related directories as mentioned in assumption(See Install Assumptions section).
2. SQL Server Services(DB/AS/RS/IS) Stand Alone - Install
3. SQL Server Stand Alone –SQL Client Install
4. SQL Server Services/SQL Client Stand Alone – Uninstall
5. SQL Server Services(DB/AS/RS/IS/Client) Stand Alone Re-Install with an existing SQL Server install Configuration File (DR Recovery)
6. SQL Server Services(DB/AS/RS/IS) Failover Cluster – InstallFailoverCluster
7. SQL Server Services(DB/AS/RS/IS) Failover Cluster – AddNode
8. SQL Server Services(DB/AS/RS/IS) Failover Cluster – RemoveNode
9. SQL Server Services Failover Cluster – SQL Client Install
10. SQL Server Services Failover Cluster – SQL Client  Uninstall
11. SQL Server Services(DB/AS/RS/IS/Client) Failover Cluster Re-Install with an existing SQL Server install Configuration File (Disaster Recovery) 

Install Script:-

Download the SQL Server Installation Power Shell Script from- SQLInstall_2014_16.ps1

Note:- The Script has been designed for SQL Server 2016/SQL 2014/SQL 2012/2008R2 and 2008.

Install Help:-

Install Assumptions:-

Based on the best practices followed in the industry I have detailed directory structure the install script will create and will be used by SQL Server.  The drives in which the directories will be created is based on the configuration file for SQL Install.

Usage	Default Instance	Named Instance
SQL Binary	SQL Install Default (C:\Program Files\Microsoft SQL Server)	SQL install Default (C:\Program Files\Microsoft SQL Server)
User and System database data Files	< SQLUSERDBDRIVE> \<Servername>\Data	< SQLUSERDBDRIVE>\<Servername_InstanceName>\Data
OLAP Data File	< ASDATADRIVE>\<Servername>\OLAPData	< ASDATADRIVE>\<Servername_InstanceName>\OLAPData
Backup and DBA Maintenance related files	<SQLBACKUPDRIVE>\<Servername>\Backup <SQLBACKUPDRIVE>\<Servername>\SQLAdmin	<SQLBACKUPDRIVE>\<Servername_InstanceName>\Backup <SQLBACKUPDRIVE>\< Servername_InstanceName >\SQLAdmin
OLAP Backup	<ASBACKUPDRIVE>\<Servername>\OLAPBackup	<ASBACKUPDRIVE>\<Servername_InstanceName>\OLAPBackup
User and System database Log Files	<SQLUSERLOGDRIVE >\<Servername>\TLog	<SQLUSERLOGDRIVE>\<Servername_InstanceName>\TLog
OLAP Log File	<ASLOGDRIVE>\<Servername>\OLAPLog	<ASLOGDRIVE>\<Servername_InstanceName>\OLAPLog
Paging File Drive	System Default	System Default
TempDB data file	<SQLTEMPDBDATADRIVE>\<Servername>\Data	<SQLTEMPDBDATADRIVE >\<Servername_InstanceName>\Data
OLAP Temp	< ASTEMPDRIVE >\<Servername>\OLAPTemp	< ASTEMPDRIVE >\<Servername_InstanceName>\ OLAPTemp

I came up with an Idea of using 2 parameters; a user configuration file for user input and an Action Parameter to carter all requirements,

Script Help:- 

SQLInstall.ps1 -c <User Input File Full UNC Path> -a <Action> Parameter 1:- -c to User Input File for the Install Parameter 2:- -a to Specify the Action of Install; The Value of -a can be      Only Install, InstallFailoverCluster, AddNode, RemoveNode,       Uninstall, PreInstall, PostInstall or Reinstall Example 1:- .\SQLInstall.ps1 -c c:\SQLAutoInstall.ini -a Install Example 2:- .\SQLInstall.ps1 -c c:\Install.ini –a InstallFailoverCluster Example 3:- .\SQLInstall.ps1 -c c:\SQLAutoInstall.ini -a AddNode Example 4:- .\SQLInstall.ps1 -c c:\SQLAutoInstall.ini -a RemoveNode Example 5:- .\SQLInstall.ps1 -c c:\SQLAutoInstallConfig.ini -a UnInstall Example 6:- .\SQLInstall.ps1 -c U:\Admin\Install.ini –a ReInstall Example 7:- .\SQLInstall.ps1 -c U:\Admin\Install.ini –a PreInstall

Parameter “-a” Help:  Action:-
   Action specifies in which mode you want the script to run, Various Actions are Explained Below

ACTION	DESCRIPTION
-a PreInstall	Create SQL Server related as mention in Install assumption section on the drives specified in the user input file.
-a Install	PreInstall  Action + Install SQL Server Services(DB/AS/RS/IS) and SQL Server Client on a Stand Alone server depending on User input file Configuration + PostInstall Action
-a InstallFailoverCluster	PreInstall  Action + Install Failover Cluster SQL Server Services(DB/AS/RS/IS) and SQL Server Client  depending on User input file Configuration + PostInstall Action
-a AddNode	Add a node to an existing cluster specified in the user input file
-a RemoveNode	Remove a node to an existing cluster specified in the user input file
-a Uninstall	Uninstall SQL Server Services(DB/AS/RS/IS) and SQL Server Client on a Stand Alone server depending on User input file Configuration and Uninstall SQL Server Client on Failover SQL clusters.
-a Reinstall	Used for DR purpose to Install SQL Server components both Standalone and Failover Cluster with an already existing SQL Server install Configuration File created during the Initial Server Build.

 

Parameter “-c” Help:  User Input File Format:-

Sample User Input File,

User Input file format is very critical to successful execution of the unattended install script, details of every entry in user Input file is specified in the below table in this section,

#SQL Server Unattended Configuration File SQL_SERVER_NAME=TESTSQLSERVER SQLVERSION=SQL2016 INSTANCE_NAME=INST1 SQLINSTALL=TRUE SQLCLIENTINSTALL=TRUE ASINSTALL=TRUE RSINSTALL=FALSE ISINSTALL=FALSE DQCINSTALL=FALSE MDSINSTALL=FALSE #Pre-InstallConfig-SQL DBEngine SQLUSERDBDRIVE=D SQLUSERLOGDRIVE=L SQLBACKUPDRIVE=H SQLTEMPDBDATADRIVE=T SQLTEMPDBLOGDRIVE=L SQLTEMPDBFILECOUNT=8 SQLSVCACCT=TESTDOMAIN\SQLDBServiceAccount SQLSVCPWD=Password SQLCOLLATION=(null) SQLSYSADMINACCOUNTS= TESTDOMAIN\SQLDBAADGroup SQLSVCSTARTUPTYPE=Automatic SAPWD=Password #Pre-InstallConfig-AS Engine ASDATADRIVE=D ASLOGDRIVE=L ASBACKUPDRIVE=H ASTEMPDRIVE=T ASSVCACCT= TESTDOMAIN\SQLASServiceAccount ASSVCPWD=Password ASCOLLATION=Latin ASSYSADMINACCOUNTS= TESTDOMAIN\SQLDBAADGroup ASSERVERMODE=MULTIDIMENSIONAL ASSVCSTARTUPTYPE=Automatic #Pre-InstallConfig-IS Engine ISAVCACCT= TESTDOMAIN\SQLISServiceAccount ISSVCPWD=Password ISSVCSTARTUPTYPE=Automatic #Pre-InstallConfig-RS Engine RSSVCACCT= TESTDOMAIN\SQLRSServiceAccount RSSVCPWD=Password #Pre-InstallConfig-Cluster FAILOVERCLUSTERDISKS="Cluster Disk 1" "Cluster Disk 2" FAILOVERCLUSTERGROUP=SQL Server (INST1) FAILOVERCLUSTERIPADDRESSES=IPv4;10.0.0.1;Public Network;255.255.255.0 FAILOVERCLUSTERNETWORKNAME=SQLVIRTUALSERVERNAME #Install PowerShell Config SQLSETUPEXEPATH=Y:\SQLEXECUTABLEPATH REINSTALLFILEPATH=(null)

 Note: - The Default value of every configuration is (null) do not leave any value blank, instead update to (null).

  

Input Configuration	Description	Install mode Usage (Parameter 2)	Values
SQL_SERVER_NAME	Physical Server name for Stand Alone Install (without Instance name) Cluster Network Name in case of Failover Cluster Install	All Install modes	<SQL_SERVER_NAME>, cannot be (null)
SQLVERSION	Value should be  either SQL2008/SQL2012/SQL2014 or SQL2016	All Install modes	SQL2008/SQL2012/SQL2014 or SQL2016, cannot be (null)
INSTANCE_NAME	MSSQLSERVER for Default instance <InstanceName> for named instance	All Install modes	MSSQLSERVER or <InstanceName>, cannot be (null)
SQLINSTALL	Set this TRUE to for SQL DB Engine install/Uninstall for both Stand Alone and Clusters.	Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.	TRUE or FALSE, cannot be (null)
SQLCLIENTINSTALL	Set this TRUE to for SQL Client install/Uninstall for both Stand Alone and Clusters.	Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.	TRUE or FALSE, cannot be (null)
ASINSTALL	Set this TRUE to for SQL Analysis Services install/Uninstall for both Stand Alone and Clusters.	Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.	TRUE or FALSE, cannot be (null)
RSINSTALL	Set this TRUE to for SQL Reporting Services install/Uninstall for both Stand Alone and Clusters.	Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.	TRUE or FALSE, cannot be (null)
ISINSTALL	Set this TRUE to for SQL Integration Services install/Uninstall for both Stand Alone and Clusters.	Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.	TRUE or FALSE, cannot be (null)
DQCINSTALL (Only from SQL 2012)	Set this TRUE to for Data Quality Client install/Uninstall for both Stand Alone and Clusters.	Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.	SQL2012/SQL2014/SQL2016: TRUE or FALSE, cannot be (null) SQL2008: FALSE, cannot be (null)
MDSINSTALL (Only from SQL 2012)	Set this TRUE to for Master Data Services install/Uninstall for both Stand Alone and Clusters.	Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.	SQL2012/SQL2014/SQL2016: TRUE or FALSE, cannot be (null) SQL2008: FALSE, cannot be (null)
SQLUSERDBDRIVE	SQL Server User database data file drive.	Install, InstallFailoverCluster	Drive name (Eg:- D) or Can be (null) to set SQL Server Default
SQLUSERLOGDRIVE	SQL Server User database Tlog file drive.	Install, InstallFailoverCluster	Drive name (Eg:- L) or Can be (null) to set SQL Server Default
SQLBACKUPDRIVE	SQL Server Backup drive.	Install, InstallFailoverCluster	Drive name (Eg:- H) or Can be (null) to set SQL Server Default
SQLTEMPDBDATADRIVE	SQL Server Tempdb data file drive	Install, InstallFailoverCluster	Drive name (Eg:- T) or Can be (null) to set SQL Server Default
SQLTEMPDBLOGDRIVE	SQL Server Tempdb TLog file drive	Install, InstallFailoverCluster	Drive name (Eg:- L) or Can be (null) to set SQL Server Default
SQLTEMPDBFILECOUNT (Only for SQL Server 2016)	Number of Tempdb Data Files	Install, InstallFailoverCluster	Number of Tempdb Data Files Can be (null) to set SQL Server Default
SQLSVCACCT	SQL Server Service Account	Install, InstallFailoverCluster, AddNode	AD Service account or Can be (null) to set SQL Server Default
SQLSVCPWD	Above SQL Server Service Account Password	Install, InstallFailoverCluster, AddNode	Above AD Service account Password, Cannot be (null) if SQLSVCACCT is specified.
SQLCOLLATION	SQL Server Collation	Install, InstallFailoverCluster	<CollationName> or Can be (null) to set SQL Server Default
SQLSYSADMINACCOUNTS	Default SQL Server Admin AD Account	Install, InstallFailoverCluster	Usually SQL DB Administrators TESTDOMAIN\SQLDBAADGroup
SQLSVCSTARTUPTYPE	SQL Server Service Start up type	Install	Automatic , Manual or Can be (null) to set SQL Server Default
SAPWD	sa Login Password for SQL Server	Install, InstallFailoverCluster	<Password>, cannot be (null) as Mixed mode authentication is default Authentication mode.
ASDATADRIVE	Analysis Server Data Drive	Install, InstallFailoverCluster	Drive name (Eg:- D) or Can be (null) to set SQL Server Default
ASLOGDRIVE	Analysis Server Log Drive	Install, InstallFailoverCluster	Drive name (Eg:- L) or Can be (null) to set SQL Server Default
ASBACKUPDRIVE	Analysis Server Backup Drive	Install, InstallFailoverCluster	Drive name (Eg:- H) or Can be (null) to set SQL Server Default
ASTEMPDRIVE	Analysis Server Temp Drive	Install, InstallFailoverCluster	Drive name (Eg:- T) or Can be (null) to set SQL Server Default
ASSVCACCT	Analysis Server Service Account	Install, InstallFailoverCluster, AddNode	AD Service account or Can be (null) to set SQL Server Default
ASSVCPWD	Above Analysis Server Service Account Password	Install, InstallFailoverCluster, AddNode	Above AD Service account Password, Cannot be (null) if ASSVCACCT is specified.
ASCOLLATION	Analysis Server Collation	Install, InstallFailoverCluster	<CollationName> or Can be (null) to set SQL Server Default
ASSYSADMINACCOUNTS	Default  Analysis Server Admin AD Account	Install, InstallFailoverCluster	Usually SQL DB Administrators TESTDOMAIN\SQLDBAADGroup
ASSERVERMODE (Only from SQL 2012)	Analysis Server Install Mode MULTIDIMENSIONAL, POWERPIVOT or TABULAR	Install, InstallFailoverCluster	Value can be either MULTIDIMENSIONAL, POWERPIVOT or TABULAR. Can be (null) to set Analysis Server Default
ASSVCSTARTUPTYPE	Analysis Server Service Start up type	Install	Automatic , Manual or Can be (null) to set SQL Server Default
ISAVCACCT	Integration Server Service Account	Install, InstallFailoverCluster	AD Service account or Can be (null) to set SQL Server Default
ISSVCPWD	Above Integration Server Service Account Password	Install, InstallFailoverCluster, AddNode	Above AD Service account Password, Cannot be (null) if ISAVCACCT is specified.
ISSVCSTARTUPTYPE	Integration Server Service Start up type	Install	Automatic , Manual or Can be (null) to set SQL Server Default
RSSVCACCT	Reporting Server Service Account	Install, InstallFailoverCluster	AD Service account or Can be (null) to set SQL Server Default
RSSVCPWD	Above Reporting Server Service Account Password	Install, InstallFailoverCluster, AddNode	Above AD Service account Password, Cannot be (null) if RSSVCACCT is specified.
FAILOVERCLUSTERDISKS	Specifies the list of shared disks to be included in the SQL Server failover cluster resource group.	InstallFailoverCluster	List of Shared Disks to be included, Eg:- ("Cluster Disk 4" "Cluster Disk 7").Cannot be (null) for Cluster Install only.
FAILOVERCLUSTERGROUP	Specifies the name of the resource group to be used for the SQL Server failover cluster. It can be the name of an existing cluster group or the name of a new resource group.	InstallFailoverCluster, AddNode	<FAILOVERCLUSTERGROUPName>. Cannot be (null) for Cluster Install only.
FAILOVERCLUSTERIPADDRESSES	Specifies an encoded IP address. The encodings are semicolon-delimited (;) and follow the format <IP Type>;<address>;<network name>;<subnet   mask>.	InstallFailoverCluster	<FAILOVERCLUSTERIPADDRESSESNAME> Cannot be (null) for Cluster Install only.
FAILOVERCLUSTERNETWORKNAME	Specifies the network name for the new SQL Server failover cluster. This name is used to identify the new SQL Server failover cluster instance on the network.	InstallFailoverCluster, AddNode,RemoveNode	<FAILOVERCLUSTERNETWORKNAME> Cannot be (null) for Cluster Install only.
SQLSETUPEXEPATH	Location of SQL Server Installation source binary folder.	All Install modes	<Full UNC Directory path/Share Directory Path> Cannot be (null)
REINSTALLFILEPATH	Location where the Unattended Install configuration File is Stored for DR	Reinstall	<Full UNC File path/Share File Path>. Cannot be (null) for Reinstall mode alone.

Hope this script helps to automate your SQL Install completely. I am still working on adding Upgrade of SQL Server and Install SQL CU and Service pack updates to the same script. Till then I have separate script to update the CU and Service pack for SQL Servers on this Blog.

Copyright © 2015 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.