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. 


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.



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.


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.


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.


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,

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

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,

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

Install Help:-

Install Assumptions:-

Based on the best practices followed in the industry I have detailed directory structure the install script will create and will be used by SQL Server.  The drives in which the directories will be created is based on the configuration file for SQL Install.

Usage
Default Instance
Named Instance
SQL Binary
SQL Install Default (C:\Program Files\Microsoft SQL Server)
SQL install Default (C:\Program Files\Microsoft SQL Server)
User and System database data Files
< SQLUSERDBDRIVE> \<Servername>\Data

< SQLUSERDBDRIVE>\<Servername_InstanceName>\Data
OLAP Data File
< ASDATADRIVE>\<Servername>\OLAPData

< ASDATADRIVE>\<Servername_InstanceName>\OLAPData
Backup and DBA Maintenance related files
<SQLBACKUPDRIVE>\<Servername>\Backup
<SQLBACKUPDRIVE>\<Servername>\SQLAdmin

<SQLBACKUPDRIVE>\<Servername_InstanceName>\Backup
<SQLBACKUPDRIVE>\< Servername_InstanceName >\SQLAdmin

OLAP Backup
<ASBACKUPDRIVE>\<Servername>\OLAPBackup

<ASBACKUPDRIVE>\<Servername_InstanceName>\OLAPBackup

User and System database Log Files

<SQLUSERLOGDRIVE >\<Servername>\TLog

<SQLUSERLOGDRIVE>\<Servername_InstanceName>\TLog
OLAP Log File
<ASLOGDRIVE>\<Servername>\OLAPLog

<ASLOGDRIVE>\<Servername_InstanceName>\OLAPLog
Paging File Drive
System Default
System Default
TempDB data file
<SQLTEMPDBDATADRIVE>\<Servername>\Data

<SQLTEMPDBDATADRIVE >\<Servername_InstanceName>\Data
OLAP Temp
< ASTEMPDRIVE >\<Servername>\OLAPTemp

< ASTEMPDRIVE >\<Servername_InstanceName>\ OLAPTemp



I came up with an Idea of using 2 parameters; a user configuration file for user input and an Action Parameter to carter all requirements,

Script Help:- 
SQLInstall.ps1 -c <User Input File Full UNC Path> -a <Action>

Parameter 1:- -c to User Input File for the Install

Parameter 2:- -a to Specify the Action of Install; The Value of -a can be
     Only Install, InstallFailoverCluster, AddNode, RemoveNode, 
     Uninstall, PreInstall, PostInstall or Reinstall

Example 1:- .\SQLInstall.ps1 -c c:\SQLAutoInstall.ini -a Install
Example 2:- .\SQLInstall.ps1 -c c:\Install.ini –a InstallFailoverCluster
Example 3:- .\SQLInstall.ps1 -c c:\SQLAutoInstall.ini -a AddNode
Example 4:- .\SQLInstall.ps1 -c c:\SQLAutoInstall.ini -a RemoveNode
Example 5:- .\SQLInstall.ps1 -c c:\SQLAutoInstallConfig.ini -a UnInstall
Example 6:- .\SQLInstall.ps1 -c U:\Admin\Install.ini –a ReInstall
Example 7:- .\SQLInstall.ps1 -c U:\Admin\Install.ini –a PreInstall

Parameter “-a” HelpAction:-
   Action specifies in which mode you want the script to run, Various Actions are Explained Below
ACTION
DESCRIPTION
-a PreInstall
Create SQL Server related as mention in Install assumption section on the drives specified in the user input file.
-a Install
PreInstall  Action + Install SQL Server Services(DB/AS/RS/IS) and SQL Server Client on a Stand Alone server depending on User input file Configuration + PostInstall Action
-a InstallFailoverCluster
PreInstall  Action + Install Failover Cluster SQL Server Services(DB/AS/RS/IS) and SQL Server Client  depending on User input file Configuration + PostInstall Action
-a AddNode
Add a node to an existing cluster specified in the user input file
-a RemoveNode
Remove a node to an existing cluster specified in the user input file
-a Uninstall
Uninstall SQL Server Services(DB/AS/RS/IS) and SQL Server Client on a Stand Alone server depending on User input file Configuration and Uninstall SQL Server Client on Failover SQL clusters.
-a Reinstall
Used for DR purpose to Install SQL Server components both Standalone and Failover Cluster with an already existing SQL Server install Configuration File created during the Initial Server Build.

 

Parameter “-c” Help:  User Input File Format:-

Sample User Input File,
User Input file format is very critical to successful execution of the unattended install script, details of every entry in user Input file is specified in the below table in this section,
#SQL Server Unattended Configuration File

SQL_SERVER_NAME=TESTSQLSERVER
SQLVERSION=SQL2012
INSTANCE_NAME=INST1
SQLINSTALL=TRUE
SQLCLIENTINSTALL=TRUE
ASINSTALL=TRUE
RSINSTALL=FALSE
ISINSTALL=FALSE
DQCINSTALL=FALSE
MDSINSTALL=FALSE

#Pre-InstallConfig-SQL DBEngine

SQLUSERDBDRIVE=D
SQLUSERLOGDRIVE=L
SQLBACKUPDRIVE=H
SQLTEMPDBDATADRIVE=T
SQLTEMPDBLOGDRIVE=L
SQLSVCACCT=TESTDOMAIN\SQLDBServiceAccount
SQLSVCPWD=Password
SQLCOLLATION=(null)
SQLSYSADMINACCOUNTS= TESTDOMAIN\SQLDBAADGroup
SQLSVCSTARTUPTYPE=Automatic
SAPWD=Password

#Pre-InstallConfig-AS Engine

ASDATADRIVE=D
ASLOGDRIVE=L
ASBACKUPDRIVE=H
ASTEMPDRIVE=T
ASSVCACCT= TESTDOMAIN\SQLASServiceAccount
ASSVCPWD=Password
ASCOLLATION=Latin
ASSYSADMINACCOUNTS= TESTDOMAIN\SQLDBAADGroup
ASSERVERMODE=MULTIDIMENSIONAL
ASSVCSTARTUPTYPE=Automatic

#Pre-InstallConfig-IS Engine

ISAVCACCT= TESTDOMAIN\SQLISServiceAccount
ISSVCPWD=Password
ISSVCSTARTUPTYPE=Automatic

#Pre-InstallConfig-RS Engine

RSSVCACCT= TESTDOMAIN\SQLRSServiceAccount
RSSVCPWD=Password

#Pre-InstallConfig-Cluster

FAILOVERCLUSTERDISKS="Cluster Disk 1" "Cluster Disk 2"
FAILOVERCLUSTERGROUP=SQL Server (INST1)
FAILOVERCLUSTERIPADDRESSES=IPv4;10.0.0.1;Public Network;255.255.255.0
FAILOVERCLUSTERNETWORKNAME=SQLVIRTUALSERVERNAME

#Install PowerShell Config

SQLSETUPEXEPATH=Y:\SQLEXECUTABLEPATH
REINSTALLFILEPATH=(null)

 Note: - The Default value of every configuration is (null) do not leave any value blank, instead update to (null).
  
Input Configuration
Description
Install mode Usage (Parameter 2)
Values
SQL_SERVER_NAME
Physical Server name for Stand Alone Install (without Instance name)

Cluster Network Name in case of Failover Cluster Install
All Install modes
<SQL_SERVER_NAME>, cannot be (null)
SQLVERSION
Value should be  either SQL2008 or SQL2012
All Install modes
SQL2008 or SQL2012, cannot be (null)
INSTANCE_NAME
MSSQLSERVER for Default instance
<InstanceName> for named instance
All Install modes
MSSQLSERVER or <InstanceName>, cannot be (null)
SQLINSTALL
Set this TRUE to for SQL DB Engine install/Uninstall for both Stand Alone and Clusters.
Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.
TRUE or FALSE, cannot be (null)
SQLCLIENTINSTALL
Set this TRUE to for SQL Client install/Uninstall for both Stand Alone and Clusters.
Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.
TRUE or FALSE, cannot be (null)
ASINSTALL
Set this TRUE to for SQL Analysis Services install/Uninstall for both Stand Alone and Clusters.
Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.
TRUE or FALSE, cannot be (null)
RSINSTALL
Set this TRUE to for SQL Reporting Services install/Uninstall for both Stand Alone and Clusters.
Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.
TRUE or FALSE, cannot be (null)
ISINSTALL
Set this TRUE to for SQL Integration Services install/Uninstall for both Stand Alone and Clusters.
Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.
TRUE or FALSE, cannot be (null)
DQCINSTALL
(Only for SQL 2012)
Set this TRUE to for Data Quality Client install/Uninstall for both Stand Alone and Clusters.
Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.
SQL2012:
TRUE or FALSE, cannot be (null)
SQL2008:
FALSE, cannot be (null)
MDSINSTALL
(Only for SQL 2012)
Set this TRUE to for Master Data Services install/Uninstall for both Stand Alone and Clusters.
Install, InstallFailoverCluster, AddNode, RemoveNode or Uninstall.
SQL2012:
TRUE or FALSE, cannot be (null)
SQL2008:
FALSE, cannot be (null)
SQLUSERDBDRIVE
SQL Server User database data file drive.
Install, InstallFailoverCluster
Drive name (Eg:- D) or
Can be (null) to set SQL Server Default
SQLUSERLOGDRIVE
SQL Server User database Tlog file drive.
Install, InstallFailoverCluster
Drive name (Eg:- L) or
Can be (null) to set SQL Server Default
SQLBACKUPDRIVE
SQL Server Backup drive.
Install, InstallFailoverCluster
Drive name (Eg:- H) or
Can be (null) to set SQL Server Default
SQLTEMPDBDATADRIVE
SQL Server Tempdb data file drive
Install, InstallFailoverCluster
Drive name (Eg:- T) or
Can be (null) to set SQL Server Default
SQLTEMPDBLOGDRIVE
SQL Server Tempdb TLog file drive
Install, InstallFailoverCluster
Drive name (Eg:- L) or
Can be (null) to set SQL Server Default
SQLSVCACCT
SQL Server Service Account
Install, InstallFailoverCluster,
AddNode
AD Service account or Can be (null) to set SQL Server Default
SQLSVCPWD
Above SQL Server Service Account Password
Install, InstallFailoverCluster,
AddNode
Above AD Service account Password, Cannot be (null) if SQLSVCACCT is specified.
SQLCOLLATION
SQL Server Collation
Install, InstallFailoverCluster
<CollationName> or Can be (null) to set SQL Server Default
SQLSYSADMINACCOUNTS
Default SQL Server Admin AD Account
Install, InstallFailoverCluster
Usually SQL DB Administrators TESTDOMAIN\SQLDBAADGroup
SQLSVCSTARTUPTYPE
SQL Server Service Start up type
Install
Automatic , Manual or Can be (null) to set SQL Server Default
SAPWD
sa Login Password for SQL Server
Install, InstallFailoverCluster
<Password>, cannot be (null) as Mixed mode authentication is default Authentication mode.
ASDATADRIVE
Analysis Server Data Drive
Install, InstallFailoverCluster
Drive name (Eg:- D) or
Can be (null) to set SQL Server Default
ASLOGDRIVE
Analysis Server Log Drive
Install, InstallFailoverCluster
Drive name (Eg:- L) or
Can be (null) to set SQL Server Default
ASBACKUPDRIVE
Analysis Server Backup Drive
Install, InstallFailoverCluster
Drive name (Eg:- H) or
Can be (null) to set SQL Server Default
ASTEMPDRIVE
Analysis Server Temp Drive
Install, InstallFailoverCluster
Drive name (Eg:- T) or
Can be (null) to set SQL Server Default
ASSVCACCT
Analysis Server Service Account
Install, InstallFailoverCluster,
AddNode
AD Service account or Can be (null) to set SQL Server Default
ASSVCPWD
Above Analysis Server Service Account Password
Install, InstallFailoverCluster,
AddNode
Above AD Service account Password, Cannot be (null) if ASSVCACCT is specified.
ASCOLLATION
Analysis Server Collation
Install, InstallFailoverCluster
<CollationName> or Can be (null) to set SQL Server Default
ASSYSADMINACCOUNTS
Default  Analysis Server Admin AD Account
Install, InstallFailoverCluster
Usually SQL DB Administrators TESTDOMAIN\SQLDBAADGroup
ASSERVERMODE
(Only for SQL 2012)
Analysis Server Install Mode MULTIDIMENSIONAL, POWERPIVOT or TABULAR
Install, InstallFailoverCluster
Value can be either MULTIDIMENSIONAL, POWERPIVOT or TABULAR. Can be (null) to set Analysis Server Default
ASSVCSTARTUPTYPE
Analysis Server Service Start up type
Install
Automatic , Manual or Can be (null) to set SQL Server Default
ISAVCACCT
Integration Server Service Account
Install, InstallFailoverCluster
AD Service account or Can be (null) to set SQL Server Default
ISSVCPWD
Above Integration Server Service Account Password
Install, InstallFailoverCluster,
AddNode
Above AD Service account Password, Cannot be (null) if ISAVCACCT is specified.
ISSVCSTARTUPTYPE
Integration Server Service Start up type
Install
Automatic , Manual or Can be (null) to set SQL Server Default
RSSVCACCT
Reporting Server Service Account
Install, InstallFailoverCluster
AD Service account or Can be (null) to set SQL Server Default
RSSVCPWD
Above Reporting Server Service Account Password
Install, InstallFailoverCluster,
AddNode
Above AD Service account Password, Cannot be (null) if RSSVCACCT is specified.
FAILOVERCLUSTERDISKS
Specifies the list of shared disks to be included in the SQL Server failover cluster resource group.
InstallFailoverCluster

List of Shared Disks to be included, Eg:- ("Cluster Disk 4" "Cluster Disk 7").Cannot be (null) for Cluster Install only.
FAILOVERCLUSTERGROUP
Specifies the name of the resource group to be used for the SQL Server failover cluster. It can be the name of an existing cluster group or the name of a new resource group.
InstallFailoverCluster, AddNode
<FAILOVERCLUSTERGROUPName>. Cannot be (null) for Cluster Install only.
FAILOVERCLUSTERIPADDRESSES
Specifies an encoded IP address. The encodings are semicolon-delimited (;) and follow the format <IP Type>;<address>;<network name>;<subnet mask>.
InstallFailoverCluster
<FAILOVERCLUSTERIPADDRESSESNAME> Cannot be (null) for Cluster Install only.
FAILOVERCLUSTERNETWORKNAME
Specifies the network name for the new SQL Server failover cluster. This name is used to identify the new SQL Server failover cluster instance on the network.
InstallFailoverCluster, AddNode,RemoveNode
<FAILOVERCLUSTERNETWORKNAME> Cannot be (null) for Cluster Install only.
SQLSETUPEXEPATH
Location of SQL Server Installation source binary folder.
All Install modes
<Full UNC Directory path/Share Directory Path> Cannot be (null)
REINSTALLFILEPATH
Location where the Unattended Install configuration File is Stored for DR
Reinstall
<Full UNC File path/Share File Path>. Cannot be (null) for Reinstall mode alone.

Hope this script helps to automate your SQL Install completely. As I mentioned earlier  this script has been designed only for SQL Server 2012/2008R2 or 2008. There is a new version of the script added recently to the blog which has SQL 2014 and SQL 2016 support (http://www.sqltechnet.com/2015/09/update-powershell-script-for-sql-server.html).


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