SQL Server 2016 SSMS

With the official release of SQL Server 2016 this month let’s try out what is new in SSMS 2016 and how we can access the new 2016 features via SSMS,

To Start with the SSMS is now a separate component and does not come with SQL DB engine install as in earlier versions.

As you can see in the above figure all the client components needs to be installed separately and does not come with the DB engine anymore, when you move on to the features page on the install it no more gives us the option of the client components.

The install of SSMS and other client components are straight forward I would not like to go into details of the install anymore. Let’s try some new features of SSMS  

1) Pin your solution screen, This is a big relief now you don’t need to search for your primary query window when you have multiple query windows are open, you can pin a query window and then work around it, the pinned window will never move out of your working space.

2) Although not entirely a SSMS concept but with the introduction of live query stats you have option to select include “Live Query Statistics” near the Include execution Plan option in SSMA(Shown in the pic below) More on Live Query statistics https://msdn.microsoft.com/en-us/library/dn831878.aspx 

                         

                          

3) Another new and what i think is the most interesting concept introduced in SQL Server 2016 is Query Store can be found in database properties window now, we can discuss the Query store in detail in a separate blog.(More Info:- https://msdn.microsoft.com/en-GB/library/dn817826.aspx)

                 

4) Row level security:- RLS is another new concept for more granular security at the row level introduced in SQL 2016.(More Info:- https://msdn.microsoft.com/en-us/library/dn765131.aspx )

You can create RLS via SSMS through Databases->Security->Security Policies folder as shown below.

5) Finally you have the new Polybase concept to query non relational data stored in Hadoop or Azure Blob storage(More Info:- https://msdn.microsoft.com/en-us/library/mt143171.aspx )

You can access this via polybase folder in the SSMS as below.

In in the coming blog we will discuss each one of these new concepts in detail.

From 2016 the SQL Server developer edition has beed made free, please download and make use of it. you get all enterprise features for testing absolutely free now!!!

NUMA Nodes and SQL Scheduler Configuration Performance Impact

I recently built a new SQL server for one of my client with 72 cores, It was an SQL 2014 enterprise edition and was an upgrade from Amazon Web services to Physical infrastructure with higher processing power, Memory and faster disk. The SQL Version was the same (SQL 2014 SP1 on AWS and Physical Infrastructure) but surprising the physical infrastructure was slower than AWS in some instances. Some queries were taking long time in the new server and waits pointed to CPU Schedulers.

Thanks to our Performance Auditors(Mike Walsh from Linchpin People) who found out the following message from the SQL error log,

“SQL Server detected 2 sockets with 18 cores per socket and 36 logical processors per socket, 72 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.”

We found the client was licenced SQL for only 40 cores even though the hardware had 72 cores SQL can use only the licenced 40 cores. This was still an non-issue as this was still more processing power to AWS and that’s when Mike pointed me to Glenn Berry's Blog http://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

As mention in the above blog post, I ran

-- SQL Server NUMA node information

SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count

FROM sys.dm_os_nodes WITH (NOLOCK)

WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

node_id	node_state_desc	memory_node_id	processor_group	online_scheduler_count
0	ONLINE	0	0	36
1	ONLINE	1	1	4

As you can see in the above result,  36 Schedulers are in use on the first NUMA node with the first 36 logical processors and then 4 are in use on the second NUMA node. This is not an optimal configuration If we have had SQL licensed for all the 72 cores we would have had equal balance with the schedulers on the NUMA nodes. This might be a performance bottleneck.

I followed Glenn's blog again and ran

-- SQL Server NUMA node and cpu_id information

SELECT parent_node_id, scheduler_id, cpu_id

FROM sys.dm_os_schedulers WITH (NOLOCK)

WHERE [status] = N'VISIBLE ONLINE';

From the above query result I found the cpu id’s in use were from 0 to 19 and 64 to 83 after which I ran the below ALTER query as suggested in the blog with right CPU id.

ALTER SERVER CONFIGURATION

SET PROCESS AFFINITY CPU = 0 TO 19, 64 TO 83;

Now I ran first query again to find NUMA node information,

-- SQL Server NUMA node information

SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count

FROM sys.dm_os_nodes WITH (NOLOCK)

WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

node_id	node_state_desc	memory_node_id	processor_group	online_scheduler_count	active_worker_count
0	ONLINE	0	0	20	41
1	ONLINE	1	1	20	56

As you can see now we reached a balance now with CPU schedulers and the NUMA nodes. This change resolved our slowness issue and the server is now much faster than our lower hardware system.

Interpret SQL Transaction Log using sys.fn_dblog

Ever wondered how to read the transaction log for a database? There is an undocumented SQL function sys.fn_dblog which may help you to read T-Log except for the truncated transaction details. We can use this function effectively for point in time recovery at a LSN level.

First lets see how the typical output of the function, I have run the function on the AdventureWorks2012 DB,

select * from sys.fn_dblog(NULL,NULL)

Note:-The 2 parameters for are sys.fn_dblog StartLSN and EndLSN if you want to see the operation between specific LSN range. Default NULL, NULL will read the entire T-Log.

There you go you can see a CurrentLSN Column, Operations Column, TransactionID, PreviousLSN column etc, I am not going to discuss all columns in detail but we will see the use of some of them in our exercise today.

As you can see in the Operations column you can see operation like INSERT(LOP_INSERT_ROWS), Begin transaction(LOP_BEGIN_XACT), CheckpointEnd(LOP_END_CKPT)etc. Lets carry out an example and see how can we interpret these details.

Lets Create a Table and see what happens in the Log,

CREATE TABLE Test (a int)

GO

select [Current LSN],Operation,Context,[Transaction ID],[Previous LSN],[Transaction Name],AllocUnitName,[Begin Time] from sys.fn_dblog(NULL,NULL) ORDER BY [Transaction ID] 

As you can see there is a BEGIN TRAN event(LOP_BEGIN_XACT) with the corresponding Transaction name column as "CREATE TABLE", this is followed by set of INSERT and UPDATE operation(LOP_INSERT_ROWS and LOP_MODIFY_ROW) on various system tables(Refer AllocUnitName Column on the pic) for the new table creation.

Next I insert values to the table,

INSERT INTO Test Values(1)

GO

INSERT INTO Test Values(2)

GO

INSERT INTO Test Values(3)

GO

select [Current LSN],Operation,Context,[Transaction ID],[Previous LSN],[Transaction Name],AllocUnitName,[Begin Time],[End Time] from sys.fn_dblog(NULL,NULL) ORDER BY [Transaction ID]

As you can see we have 3 LOP_BEGIN_XACT,LOP_INSERT_ROWS,LOP_COMMIT_XACT set of operation for the 3 row insert and some page allocation tasks. You can also see we have start time for Begin TRAN and a End Time for the Commit Tran. If you see the Context column for the INSERT operation you can see LCK_HEAP indicating inserting rows to a heap table.

Next I ran UPDATE,

UPDATE Test SET a = 5 where a = 1

As expected you can see LOP_MODIFY_ROW on a LCX_HEAP for the UPDATE statement. Next I ran DELETE,

DELETE from Test where a = 5

 There you go LOP_DELETE_ROWS operation on LCX_HEAP. 

Next I ran,

DROP TABLE Test

As in CREATE Table statement DROP Table has LOP_BEGIN_XACT,LOP_LOCK_XACT operation followed by updating system tables operations and finally Commit operation LOP_COMMIT_XACT.

Finally Lets see how a rollback looks like in the T-Log,

BEGIN TRAN

INSERT INTO Test Values(1)

ROLLBACK

There was a INSERT operation then on Rollback there was a DELETE operation of the inserted row and finally Abort Transaction operation(LOP_ABORT_XACT).

Now that we have seen how we can interpret the sys.fn_dblog output now we can see how we can put it to use, 

For example if an user DELETED multiple row by mistake and does not know what rows he deleted and comes to you for help and want to restore the database to a state exactly before the data deletion. We usually go back to restoring the backups but if you have multiple users using the DB how will you go to the exact state before the DELETE was issues that's where sys.fn_dblog can be very useful. You can just read the out of the Log around the time the delete was issued then note the CurrentLSN on the LOP_BEGIN_XACT operation for the Delete then Restore backup and Transaction log backup with STOPBEFOREMARK as the CurrentLSN on the LOP_BEGIN_XACT operation.

For assumption lets say my LOP_BEGIN_XACT is '0000002c:000000a9:001e' just prefix lsn:0x to the current LSN Value on LOP_BEGIN_XACT  operation in our case it will be lsn:0x0000002c:000000a9:001e.

Now Run RESTORE LOG with STOPBEFOREMARK,

RESTORE LOG [AdventureWorks2012]

FROM DISK = 'H:\DBBkp\Log\AdventureWorks2012_bkp25.trn'

WITH STOPBEFOREMARK = 'lsn:0x0000002c:000000a9:001e',

RECOVERY;

This should exactly get us back to a state exactly before the DELETE statement issued.

SQL Symmetric Encryption TSQL

Recently my client wanted to create a password vault in SQL database to store SQL Server service account, SQL users and their respective passwords. I used symmetric key to create encryption for the encrypting the password, Find the T-SQL below to accomplish this.

Assumption:-

DB Name - SQLDBA
TableName - SQLAccounts

--********create password encrypted column*********
USE SQLDBA
GO
ALTER TABLE SQLAccounts
ADD EncryptedSQLPassword varbinary(MAX) NULL
GO

--********Create Master Key*********
USE master;
GO
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';

--**********Create database Key*********
USE SQLDBA
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
GO

--*********Create self signed certificate*********
USE SQLDBA;
GO
CREATE CERTIFICATE SQLAccountCertificate
WITH SUBJECT = 'Protect SQL Password';
GO

--**********Create Symmetric Key***********
USE SQLDBA;
GO
CREATE SYMMETRIC KEY SQLAccountSymmetricKey
 WITH ALGORITHM = AES_128
 ENCRYPTION BY CERTIFICATE SQLAccountCertificate;
GO

--*********TSQL to Insert New row with encrypted Password**********
USE SQLDBA;
GO
OPEN SYMMETRIC KEY SQLAccountSymmetricKey
DECRYPTION BY CERTIFICATE SQLAccountCertificate;
GO
INSERT INTO SQLAccounts VALUES ('ServerName\Instance','SQLusername',EncryptByKey (Key_GUID('SQLAccountSymmetricKey'),'Password'))
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SQLAccountSymmetricKey;
GO

--*************TSQL to view decrypted Password**************
USE SQLDBA;
GO

OPEN SYMMETRIC KEY SQLAccountSymmetricKey
DECRYPTION BY CERTIFICATE SQLAccountCertificate;
GO
-- Now list the original ID, the encrypted ID
SELECT *,CONVERT(varchar, DecryptByKey(EncryptedSQLPassword)) AS 'EncryptedSQLPassword'
FROM dbo.SQLAccounts;
 -- Close the symmetric key
CLOSE SYMMETRIC KEY SQLAccountSymmetricKey;

--*********TSQL to update the encrypted column*************
USE SQLDBA;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SQLAccountSymmetricKey
DECRYPTION BY CERTIFICATE SQLAccountCertificate;
GO
UPDATE SQLAccounts
SET EncryptedSQLPassword = EncryptByKey (Key_GUID('SQLAccountSymmetricKey'),Password)
FROM dbo.SQLAccounts;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SQLAccountSymmetricKey;
GO

PowerShell Invoke-sqlcmd Verbrose output to a File

Recently I have had lot queries regarding how to redirect the SQL output message from invoke-sqlcmd/invoke-sqlcmd2 function into a notepad.When you use Out-File it will return only the result set but it will not write messages associated as you expect in SSMS (Errors are not written nor any PRINT Statement when out-file is used). 

When you use -Verbose with the invoke-sqlcmd or invoke-sqlcmd2 it will write all the associated messages to your screen and All we need to do is is redirect the Verbose output to a File, how do we do it standard >> will not redirect Verbose to a File. Then I happended to Stumble across the MSDN link  https://technet.microsoft.com/en-us/library/hh847746.aspx which gave a detailed explanation of all the Redirectors :-) 

So all I need to use is 4> or 4 >> instead >> to accomplish this, , Try to execute the below 6 examples and see how the output is redirected, Only in Example6 You would be able to get a result equivalent to SSMS result editor.

Example1:-(Expected Result Powershell will not print 'HELLO WORLD' on the result editor/Stdout)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB

Example2:-(Expected Result Powershell will not print 'HELLO WORLD'  to the test file)
invoke-sqlcmd2 -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB|out-File <TestPath>

Example3:-(Expected Result Powershell will print  'HELLO WORLD' on result editor/Stdout)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose

Example4:-(Expected Result Powershell will print  'HELLO WORLD' on result editor/Stdout but The out File will be empty)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose|out-File <TestPath>

Example5:-(Expected Result Powershell will print  'HELLO WORLD' on result editor/Stdout but The out File will be empty)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose> .\Test.out

Example6:-(Expected Result Powershell will print 'HELLO WORLD'  to the test file)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose 4> .\Test.out

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.