Tuesday, 4 February 2014

PowerShell Script for SQL Server Cluster/StandAlone unattended Install and uninstall

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.ps1
Please download the script using the link 
https://drive.google.com/file/d/0Byz0IoWq6fw-aTZTaExsdE5rbm8/view?usp=sharing
(Google Drive may say "no preview" as its a Power shell script, click the download button to download the script to your local machine)
Note:- The Script has been designed only for SQL Server 2012/2008R2 or 2008. There is a new version of the script added recently to the blog which has SQL 2014 and SQL 2016 support (http://www.sqltechnet.com/2015/09/update-powershell-script-for-sql-server.html).

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” HelpAction:-
   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=SQL2012
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
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 or SQL2012
All Install modes
SQL2008 or SQL2012, 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 for 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:
TRUE or FALSE, cannot be (null)
SQL2008:
FALSE, cannot be (null)
MDSINSTALL
(Only for 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:
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
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 for 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. As I mentioned earlier  this script has been designed only for SQL Server 2012/2008R2 or 2008. There is a new version of the script added recently to the blog which has SQL 2014 and SQL 2016 support (http://www.sqltechnet.com/2015/09/update-powershell-script-for-sql-server.html).


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

21 comments:

  1. Hi the link to the powershell script does not work. could you provide an updated link. Many thanks AC.

    ReplyDelete
    Replies
    1. Hi, The link seems to work but you might have to download the script, It will not give any preview. The Link will have a download option.

      Delete
  2. manohar,


    can u please give a sample .ini for fresh install i am confused with all options in this code

    ReplyDelete
  3. In the user input file, are we to change these variables with the "p" and "c" prefixes in that file?

    ReplyDelete
  4. No You dont need to change the variable prefixes...

    ReplyDelete
    Replies
    1. I keep getting in [Module 4], "The credentials you provided for the 'SQLAgent$Test' service is invalid'. Do you know what line I need to edit for this error?

      Delete
    2. Hi,

      the error seems to be an issue with the SQL Server Account/SQL Agent Account and its password, please verify you have mentioned the correct username and password.

      Delete
    3. Hi,
      This looks like a really good solution. Unfortunately I get an error when I try to run it ;

      .\SQLInstall.ps1 : The term '.\SQLInstall.ps1' is not recognized as the name of a cmdlet, function, script file, or ope
      rable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again
      .
      At line:1 char:1
      + .\SQLInstall.ps1 -c C:\Temp\mipe10\UnattendedConfigFile.ini -a Install
      + ~~~~~~~~~~~~~~~~
      + CategoryInfo : ObjectNotFound: (.\SQLInstall.ps1:String) [], CommandNotFoundException
      + FullyQualifiedErrorId : CommandNotFoundException

      Delete
    4. thank you for the greath work, i get the error "VERSION CHECK FAILED: SQLVERSION in user Input file need to be either SQL2008 or SQL2012" despite that i have the right version. Can you help me. Thx tradelingua

      Delete
  5. Works really great, thank you very much !
    Is there maybe also an option to install(add) only features to existing instance ?

    Greets Jacob

    ReplyDelete
  6. There seems to be no entry in input configuration for SQL Server Agent & Password

    ReplyDelete
  7. Hi,
    When i run the script, It comes to SQL Server Installation Center Dailog. It did not work as silent. I am using SQL 2014

    ReplyDelete
  8. How about adding provision for the Service Packs and CUs

    ReplyDelete
    Replies
    1. There is separate script for Service Packs on the Blog

      Delete
  9. I am not able to download file as it is giving error message that page can not be displayed. Can you help?

    ReplyDelete
    Replies
    1. Can you try now i was able to download as anonymous user, if you still have issues please use by updated blog post on the same with SQL 2014 and SQL 2016 added @ http://www.sqltechnet.com/2015/09/update-powershell-script-for-sql-server.html

      Delete
  10. Hi Vinoth,

    I am able to download it now. Thank you very much for quick response. Really appreciated. Thanks Again.

    ReplyDelete
  11. Is there a list of Features that get installed? If I want Full Test Search, how does it get installed?

    ReplyDelete
  12. Hi Vinoth,

    I am trying to install failover cluster, while installing i am getting error saying The IP Address '***.20.4*.**' has an invalid network name 'Public Network'. To continue, specify an IP address with a valid network name and retry. While trying to install in general method, it was working, Please let me know how to resolve this is issue.
    D:\Install.ini and D:\SQLInstall.ps1


    FAILOVERCLUSTERDISKS="Cluster Disk 3"
    FAILOVERCLUSTERGROUP= SQL Server (SQL99)
    FAILOVERCLUSTERIPADDRESSES=IPv4;***.2*.**.29;Public Network;255.255.255.0
    FAILOVERCLUSTERNETWORKNAME=SQLCLSDEMO


    SQL_SERVER_NAME=*********-0**
    SQLVERSION=SQL2012
    INSTANCE_NAME=SQL99
    SQLINSTALL=TRUE
    SQLCLIENTINSTALL=FALSE
    ASINSTALL=TRUE
    RSINSTALL=FALSE
    ISINSTALL=FALSE
    DQCINSTALL=FALSE
    MDSINSTALL=FALS


    ReplyDelete
    Replies
    1. Hi "Public Network" is the actual Public Network name in your cluster, You need to change it to whatever you have named in your Cluster...

      Delete
  13. This is a wonderful tool, thank you for sharing. I used your earlier version to install SQL 2012 onto 30 virtual servers; it was a huge help. I had one issue in the earlier version that appears to be carried forward into the 2014/2016 script - I can specify a disk drive for tempdb log file, but the script doesn't use that value. It seems that the userdb log file disk drive is used instead. My tempdb database resides on FusionIO cards (SSD drives), and I'd like to see both tempdb data and tempdb log files go to the named drives. Can you recommend a workaround to resolve this? I will be spinning up 45 virtual servers in the next few weeks, and hope that I will not need to manually alter settings for tempdb on each of these servers after installation.

    Many many thanks,

    SteveA

    ReplyDelete