Tuesday, 21 June 2016

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!!!




Thursday, 24 March 2016

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.

Friday, 15 January 2016

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.

Monday, 16 November 2015

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

Friday, 23 October 2015

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




Thursday, 3 September 2015

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
Please download the script using the link 
https://drive.google.com/a/sqltechnet.com/file/d/0Byz0IoWq6fw-NnAyUUtpVUx3aTA/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 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.