SQL Server Technet: 2014

Monday, 27 October 2014

SQL Server 2014 new In-memory Tables

I have summarised basic concepts of In-Memory OLTP from various Microsoft articles which will help you to start up with the basics,

SQL Server 2014 new In-memory Tables:-

Microsoft has come up new concept called In-Memory OLTP with the introduction of SQL Server 2014 for performance optimization on OLTP database servers. The traditional SQL Server stores the tables in the disk and SQL OS moves the data pages from the disk to the memory (SQL Server Buffer) depending based on the query executed, most of the times the frequently accessed tables pages in the memory are paged to disk to accommodate other requested data pages resulting in frequent paging and high PAGIOLATCH waits. With the cost of hardware reducing over the years In-Memory OLTP gives us the option to store the frequently used table in memory instead of disk. In-Memory architecture is fully integrated with SQL Server and the Memory Optimized tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables.

Ref:- http://msdn.microsoft.com/en-us/library/dn133186.aspx

In-Memory Architecture

Legacy SQL server did have similar function called DBCC PINTABLE which pins the data pages of the table to memory and these pages does not flush to the disks, but this was a SQL Server internal function meaning the pages are pinned to SQL Server Buffer cache which most of times creates a memory crunch for other data which needs processing which resulted in more of a problem than resolution this function was depreciated in SQL Server. In-Memory OLTP architecture unlike DBCC PINTABLE has its own memory space for storing memory optimized tables and indexes and don’t use Buffer cache which can be still used effective for disk based table operation. SQL server can interact with memory optimized table and a disk based table in same transaction meaning you can have both memory-optimized tables and disk-based tables in the same database, and query across both types of tables.

Ref:- http://sqlmag.com/sql-server-2014/application-performance-inmemory-oltp-database-engine

Transactional Concurrency:-

One of major advantages of the memory optimized table is locks on the table are very minimal irrespective of the isolation level of the transaction. Take for example in READ SNAPSHOT ISOLATION on a disk based table does not block a SELECT operation during a data modification due to row versioning of the data in tempdb database but this still blocks any process which tries to update/delete the data simultaneously but in Memory optimized architecture there are no locking if two transactions attempt to update the same row, rather it is managed by a conflict management. memory-optimized tables use multi versioning meaning each row has different version instead of single row version used in snapshot isolation, Also unlike snapshot isolation which stores the row version in tempdb, memory optimized tables row version are stored in memory. This architecture allows even higher isolation levels to have transactional concurrency. As a part of conflict management SQL server detect conflicts between concurrent transactions, If a conflict is detected, the transaction is terminated and the client needs to retry.

Ref:- http://msdn.microsoft.com/en-gb/library/dn133169.aspx

Durability:-

Memory optimized Tables can be configured as both fully durable(SCHEMA_AND_DATA) and non-durable(SCHEMA_ONLY). Memory optimized tables configured fully durable(SCHEMA_AND_DATA) can be recovered without any data loss on an SQL Server restart whereas Tables configured with SCHEMA_ONLY durability will be able to recover only the table metadata on a SQL server restart, these tables can be typically your staging tables used in ETL process which can be flushed on database restart like tempdb tables.

Like Disk-based tables all changes are logged to a transaction log for durable memory optimized tables and the SQL server writes the transaction to the disk. Now this where it can be misleading on why the data is written to disk when the table is in memory, Memory-optimized tables is fully integrated with the same log stream used by disk-based tables, to recover the table point in time and to allow operations like T-Log, backup and restore without any additional steps, Memory optimized table maintains a copy of table and data on disk on a different data structure stored as free form data rows with the help of two files called data and delta files. The data and delta files are located in one or more containers (using the same mechanism used for FILESTREAM data). These containers are mapped to a new type of filegroup, called a memory-optimized filegroup. On a SQL Server restart memory optimized tables are flushed back into memory from the data and delta files for fully durable table.

Ref:- http://msdn.microsoft.com/en-gb/library/dn553125.aspx

Implementation:-

Create a database with a memory optimized filegroup and container

CREATE DATABASE MEMORYTABLETEST

ON PRIMARY

(NAME = ‘MEMORYTABLETEST_PRIMARY’, FILENAME = ‘C:\Data\ MEMORYTABLETEST_PRIMARY.mdf)

FILEGROUP INMEMORTYOLTP CONTAINS MEMORY_OPTIMIZED_DATA

(NAME = ‘MEMORYTABLETEST_INMEMORY’, FILE = ‘C:\InMemory\Container’)

Create a Durable memory optimized table

CREATE TABLE dbo.durabletable_memory

( Col1 int PRIMARY KEY,

  Col2 char(200) NONCLUSTERED HASH (Id) WITH (BUCKET_COUNT = 1024)

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

Memory Optimization Indexes

Memory optimized tables supports indexes, Memory-optimized indexes exist only in memory and index operation are not logged into transaction logs.

There are two types of memory-optimized indexes

Non-clustered Hash Index
Non clustered Index

Hash index architecture is different from traditional index and it uses a data structure called buckets to store index and data.

Each memory-optimized table must have at least one index. Note that each PRIMARY KEY constraint implicitly creates an index. Therefore, if a table has a primary key, it has an index. A primary key is a requirement for a durable memory-optimized table.

Ref:- http://msdn.microsoft.com/en-us/library/dn133166.aspx

Limitations

In-Memory OLTP will improve performance best in OLTP with short-running transactions, but to gain the performance improvement we need to select the right table to be optimized otherwise it can cause huge performance issues, being a new component memory optimized OLTP has lot of function limitation which may be improved in future versions. For example cross database transaction, replication, mirroring, database snapshots is not supported and there are also various limitations on operation you can perform on these tables. But if a right table is configured to use this feature Microsoft suggests we may have have 5-20 times performance improvement which is a huge leap.

Wednesday, 8 October 2014

How to create a SSRS DR/Copy Report Server Database across different servers

Imagine you have installed and initialized SSRS in your Primary and DR Environment and To Mirror the DR environment to the Primary SSRS follow the below steps,

Copy the ReportServer and ReportServerTempDB to the DR Server

Note:- If you just do the database restore and try to access the SSRS you get the error “The report server installation is not initialized. (rsReportServerNotActivated)”, You need to follow the below steps to configure the DR SSRS with the existing database.

2.Backup Encryption Key for the Primary SSRS

3. Copy the Encryption Key File to DR SSRS Server, In my case say C:\Test\ssrsencryptkey.snk

4. Run the following Query in ReportServer database of the DR server

'DELETE FROM [Dbo].[Keys] WHERE client > -1'

5. Now search for Program Files folder for “RSKeymgmt.exe” in DR Server

6. Execute the "RSKeymgmt.exe" from command Prompt of DR Server in Admin context with the below syntax,

RSKeymgmt.exe -a -f "C:\Test\ssrsencryptkey.snk" -p "<passwordof theencryptionkey>"

7. Restart SSRS.

Once you have completed these steps you access the Reports web page for the DR SSRS it should match the primary server. Remember I have not configured any real time DR for the ReportServer and ReportServerTempDB databases in the above example, You have to set up Logshipping/Mirroring for the SSRS databases for complete DR Solution. The above steps merely explains how to configure in case of failover.The same set of steps can be used if you want to copy SSRS across different servers. Hope the article helps.

Wednesday, 6 August 2014

SQL Database Supect Mode due to MSDTC Transaction

A database may go into suspect mode for various reason most of them are related to corruption of disk or data but SQL server may also put a database in a suspect mode if it’s not able to recover the database properly on restart

We had one such scenario recently, On a restart of planned outage one of the database went in to Suspect mode and we saw the following error in the SQL error log,

2014-08-01 10:37:31.760 spid5s Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

2014-08-01 10:39:13.480 spid5s SQL Server detected a DTC/KTM in-doubt transaction with UOW {14EBFD4C-0955-4C25-91B1-C418EE182E64}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

2014-08-01 10:39:13.480 spid5s Error: 3437, Severity: 21, State: 3.

2014-08-01 10:39:13.480 spid5s An error occurred while recovering database 'DBName'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:75299367). Fix MS DTC, and run recovery again.

2014-08-01 10:39:13.480 spid5s Error: 3414, Severity: 21, State: 2.

2014-08-01 10:39:13.480 spid5s An error occurred during recovery, preventing the database 'DBNAME' (6:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

We already know the application using the database uses MSDTC for various sources and any unexpected restart put an In-doubt transaction on DTC. Basically In-Doubt transactions are transaction which the DTC services cannot decide to abort or resolve by itself but since the UOW for the transaction exists

in SQL, SQLOS does not differentiate it aa transaction in In-doubt status and kill it and the transaction will be marked as active till is resolved or aborted from DTC. This is similar to the topic which I wrote earlier in the blog regarding “-2 SPID Blocking”.

when the database tries to recover a DB with UOW of in-doubt transaction on restart it could not rollback or roll forward the transaction so SQL puts database eventually in suspect mode failing to recover completely. You go Component Services->Computers->My Computer->Distributed transaction Coordinator->Local DTC->Transaction List for standalone servers or Component Services->Computers->My Computer->Distributed transaction Coordinator->Clustered DTC->Transaction List for a cluster instance, Right Click on all transaction marked In-Doubt either commit or abort the transaction depending on your requirements. And restart the SQL Services which should recover the database clean.

But the clean way of doing a restart of a SQL with DTC transactions is to switch of the application before the SQL restart which will avoid this issue in the first place. Hope this article helps!

Friday, 27 June 2014

UPDATE and REPLACE TSQL

Find the example TSQL to use UPDATE statement with REPLACE function,

UPDATE [AdventureWorks2008R2].[Person].[Person]

SET FirstName = REPLACE(FirstName,'sam','jim') Where FirstName = 'sam'

SSIS Out of Memory/Buffer Manager errors

Often I get queries from my developers regarding “out of memory exception” or Buffer Manager errors during data flow task, the frequent query they ask is “I am running the package in the SQL server box itself and if the SQL server is granted say 128 GB of memory why are we getting memory issues.

Valid question but is your SSIS Package actually using the memory allocated to SQL Server?

To understand better on how memory is used during SSIS execution you find interesting details in below article,

http://technet.microsoft.com/library/Cc966529

To summarise the above query SSIS uses two components run-time engines(To control the package flow) and data flow engine(To control the data flow tasks)

The Data flow engine uses buffer to move and work with the data, Meaning the data is actually physically moved to your memory or page file and part of the memory used for this is the memory allocated outside of SQL Server and not SQL server buffer manager .This is why we get the memory exceptions even though we run the package on SQL server as SQL server is more aggressive with memory allocation and willy occupy most of the memory allocated to it. SSIS has to fight for memory allocated outside of SQL server which is usually very less on a dedicated SQL box.

To control the buffer size and number of rows in each buffer you can use the DefaultMaxBufferSize and DefaultMaxBufferRows property in the dataflow task. The default size for buffer is 10 MB and default number of rows in a buffer is 10000.

Microsoft recommends the following “Tweak the values for DefaultMaxBufferRows and DefaultMaxBufferSize to get as many records into a buffer as possible. Setting these values too low causes SSIS to create many small buffers instead of fewer but larger buffers, which is a great scenario if you have enough memory“.

In case you could not avoid lack of memory resource, SSIS can page the buffers in the disk, this event usually slows down the process. But you can control the location where these buffers are paged in the disk using the property BufferTempStoragePath. The default value for this property is the TEMP/TMP system variable location.

Monday, 14 April 2014

Monitor and Alert SQL Stack dumps

As many of know not all stack dumps raise an error in SQL Server and may go unnoticed for days, SQL stack dumps can sometimes cause database corruption and its always better to analyse the server immediately after a stack dump. Since there are no errors raised we cannot configure normal SQL Alert to report the SQL Stack dumps, I created a PowerShell script which can also be scheduled as a SQL Agent job to monitor the SQL Stack dumps, I have used Chad Millers invoke-sqlcmd2 just for flexibility in the future, you can use native invoke-sqlcmd without any issue.

If you want to schedule this script as SQL Agent job remember to comment the clear-host and the invoke-sqlcmd2 function in the below script and try to use invoke-sqlcmd instead.Also I have scripted this to check the stack dumps for last one hour as I am running the SQL agent every hour in my environment, please change accordingly.

Friday, 14 March 2014

PowerShell to list the packages stored in Integration Services SQL2008/SQL2008R2

PowerShell to list the packages stored in Integration Services SQL2008/SQL2008R2; I will come up with a SQL 2012 version soon.

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,

Pre-Install Task – Create SQL Server related directories as mentioned in assumption(See Install Assumptions section).
SQL Server Services(DB/AS/RS/IS) Stand Alone - Install
SQL Server Stand Alone –SQL Client Install
SQL Server Services/SQL Client Stand Alone – Uninstall
SQL Server Services(DB/AS/RS/IS/Client) Stand Alone Re-Install with an existing SQL Server install Configuration File (DR Recovery)
SQL Server Services(DB/AS/RS/IS) Failover Cluster – InstallFailoverCluster
SQL Server Services(DB/AS/RS/IS) Failover Cluster – AddNode
SQL Server Services(DB/AS/RS/IS) Failover Cluster – RemoveNode
SQL Server Services Failover Cluster – SQL Client Install
SQL Server Services Failover Cluster – SQL Client Uninstall
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” 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=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).