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,

  1. 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>"

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,

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.

# Script to Monitor Stack Dump in SQL Server
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 17/02/2014

Param(
 [Parameter(Mandatory=$false,Position=0)]
 [String]$sqlinstance_name = 'sqlinstancename'
  )
##################################################Function Invoke-sqcmd##########################################################################
#Script refered from http://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894
<# 
.SYNOPSIS 
Runs a T-SQL script. 
.DESCRIPTION 
Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified 
.INPUTS 
None 
    You cannot pipe objects to Invoke-Sqlcmd2 
.OUTPUTS 
   System.Data.DataTable 
.EXAMPLE 
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1" 
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query. 
StartTime 
----------- 
2010-08-12 21:21:03.593 
.EXAMPLE 
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt" 
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file. 
.EXAMPLE 
Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose 
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command. 
VERBOSE: hello world 
.NOTES 
Version History 
v1.0   - Chad Miller - Initial release 
v1.1   - Chad Miller - Fixed Issue with connection closing 
v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation 
v1.3   - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type 
#> 
function Invoke-Sqlcmd2 
{ 
    [CmdletBinding()] 
    param( 
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, 
    [Parameter(Position=1, Mandatory=$false)] [string]$Database, 
    [Parameter(Position=2, Mandatory=$false)] [string]$Query, 
    [Parameter(Position=3, Mandatory=$false)] [string]$Username, 
    [Parameter(Position=4, Mandatory=$false)] [string]$Password, 
    [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, 
    [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, 
    [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, 
    [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" 
    ) 
 
    if ($InputFile) 
    { 
        $filePath = $(resolve-path $InputFile).path 
        $Query =  [System.IO.File]::ReadAllText("$filePath") 
    } 
 
    $conn=new-object System.Data.SqlClient.SQLConnection 
      
    if ($Username) 
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } 
    else 
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 
 
    $conn.ConnectionString=$ConnectionString 
     
    #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
    if ($PSBoundParameters.Verbose) 
    { 
        $conn.FireInfoMessageEventOnUserErrors=$true 
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
        $conn.add_InfoMessage($handler) 
    } 
     
    $conn.Open() 
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) 
    $cmd.CommandTimeout=$QueryTimeout 
    $ds=New-Object system.Data.DataSet 
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) 
    [void]$da.fill($ds) 
    $conn.Close() 
    switch ($As) 
    { 
        'DataSet'   { Write-Output ($ds) } 
        'DataTable' { Write-Output ($ds.Tables) } 
        'DataRow'   { Write-Output ($ds.Tables[0]) } 
    } 
 
} 
##################################################END: Function Invoke-sqcmd##########################################################################
Clear-Host
$Errorlog = @()
$Errorlog = Invoke-Sqlcmd2 -ServerInstance $sqlinstance_name -Database 'master' -Query 'sp_readerrorlog 1'

ForEach($line in $Errorlog)
{
 #$line.text
 if($line.text -like '*BEGIN STACK DUMP*')
 {
  if($line.LogDate -gt (Get-Date).AddHours(-1)) #Change According to your requirement
  {
   $mailsql = "EXEC msdb.dbo.sp_send_dbmail
      @recipients = 'EIGSQLDBA@equitygroup.co.uk',
      @copy_recipients = 'EIGSQLDBA@equitygroup.co.uk',
      @from_address =  'EIGSQLDBA@equitygroup.co.uk', 
      @body = 'SQLDumps has been reported in the last one hour for the SQL instance "+$sqlinstance_name+" Please Investigate-"+$line.logDate+":"+$line.text+"',
       @subject = '*******ERROR:SQL Dumps Reported********'"
   Invoke-Sqlcmd2 -ServerInstance $sqlinstance_name -Database 'msdb' -Query $mailsql
  }
 }
}

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.

# Get SSIS Package List from a Integration Services Server
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 14/02/2014

$SSISServerName = "Enter SSIS Servername"

Clear-Host
 add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Import-Module SQLPS –DisableNameChecking -ErrorAction SilentlyContinue
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS")|Out-Null
$FnChildFolders = @()
$FnBaseFolderPath = $null
$SubFnChildFolder = @()
$FnChildFolders = @()
$FolderName = $null
$ChkFolderName = $null
$ChildFolderElements = @()
$Element = @()
$SubChkFolder = $null
$BaseFolderPath = $null
$SSISServerName = $null
$SSISServer = @()
$BaseFolder = @()
$ChildFolders = @()
$BaseFolderPakages = @()
$SubChildFolder = @()
$pkgfolderpath = $null
$subpkgfolderpath = $null

Function GetPackageFromFolder($FnChildFolders,$fnFoldername)
{ 
 
 [String]$FolderName= $FnChildFolders
 Write-Host $fnFoldername
   Write-Host "----------------------------`n"
 $ChildFolderElements = $SSISServer.GetPackageInfos($FolderName,$SSISServerName,$null,$null) 
 #$ChildFolderElements
   if($ChildFolderElements -ne @() -or $ChildFolderElements -ne $null)
 {
 forEach($Element in $ChildFolderElements)
 {
  if($Element.Flags -eq "Folder")
  {
   Write-Host "`n`tSubFolder----------->"$Element.Name
   $subpkgfolderpath = $FolderName+$Element.Name+"\"
   #$subpkgfolderpath
   GetPackageFromFolder $subpkgfolderpath $Element.Name
  }
  Else
  {
   Write-Host $Element.Name
  }
 }
 }
      
Write-Host "`n"
}
$BaseFolderPath = "\\"
$SSISServer =  New-Object Microsoft.SQLServer.DTS.RunTime.Application
$BaseFolder = $SSISServer.GetPackageInfos('\',$SSISServerName,$null,$null) 
$ChildFolders = $BaseFolder|Where{$_.Flags -eq "Folder"}
$BaseFolderPakages = $BaseFolder|Where{$_.Flags -eq "Package"}
Write-Host "\"
Write-Host "----"
$BaseFolderPakages.name
Write-Host "`n"
ForEach($SubChildFolder in $ChildFolders)
{
  $FolderName = $SubChildFolder.Name
  $SubChildFolderpath = $BaseFolderPath+$FolderName+"\"
  GetPackageFromFolder $SubChildFolderpath $FolderName
}
Copyright © 2014 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

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/0B6V70litIDj-dUhIdDVYTnJOcFE/edit?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.

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.


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