SQLServer 2008R2 unattended installation and configuration via powershell

My first steps on Powershell

Ok, Ok, as an “Oracle on Linux Certified Expert”, I’ve never been a great fan of SQLServer (I shouldn’t say this, I’m working on SQLServer since release 6.5…) and I’ve always hated the DOS command prompt.
However, things are changing fast after Microsoft released the Powershell some years ago. It’s surprising, now Windows powershell support new best of breed features like aliases and pipelines. 😀

Today Microsoft itself recommends Windows Core installations instead of the full ones, and also SQLServer 2012 comes with a lot of new Commandlets to manage your server.

So I’ve decided to move my first steps in the Powershell world and I’ve created a script for a customer that installs and configure a SQL2008 with a single Powershell script.

It has been quite painful to complete, you can download the complete script HERE.

Taking parameters

The very first line accepts named parameters. I’ve tried to reduce the number but I’ve preferred to take, as an example, different disks for different tasks.

param ([string] $instance, [string] $collation, $ServiceAccountPassword, $saPassword, $sourceDir, $servicePackExec, $instDrive, $userDbDrive, $userLogDrive, $tempDbDrive,  $tempLogDrive, $backupDrive , $port )

1	param ([string] $instance, [string] $collation, $ServiceAccountPassword, $saPassword, $sourceDir, $servicePackExec, $instDrive, $userDbDrive, $userLogDrive, $tempDbDrive, $tempLogDrive, $backupDrive , $port )

Then I’ve put a little of interaction if some parameters are missing. In facts, I can launch my scripts without inline parameters and specify everything when prompted by the script.

######################
# Getting Parameters #
######################
if ( -not $sourceDir) { $sourceDir = Read-Host 'Source Path containing SQL2008R2 installation ? ' }
if ( -not $servicePackExec) { $servicePackExec = Read-Host 'Full Path to service pack executable [Empty for None]? ' }
if ( -not $instance) { $instance = Read-Host 'Instance Name? ' }
if ( -not $collation ) {$collation = Read-Host 'Collation [Latin1_General_CI_AS]? ' }
if ( -not $collation ) { $collation = "Latin1_General_CI_AS" }

if ( -not $port ) { $port = Read-Host 'TCP Port [50000]? ' }
if ( -not $port ) { $port = "50000" }

if ( -not $ServiceAccountPassword ) {
    [System.Security.SecureString]$ServiceAccountPassword = Read-Host "Enter the service account password: " -AsSecureString;
    [String]$syncSvcAccountPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceAccountPassword));
} else { [String]$syncSvcAccountPassword = $ServiceAccountPassword; }

if ( -not $saPassword ) {
    [System.Security.SecureString]$saPasswordSec = Read-Host "Enter the sa password: " -AsSecureString;
    [String]$saPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($saPasswordSec));
} else { [String]$saPassword = $ServiceAccountPassword; }

$instance = $instance.ToUpper()

######################

# Getting Parameters #

######################

if ( -not $sourceDir) { $sourceDir = Read-Host 'Source Path containing SQL2008R2 installation ? ' }

if ( -not $servicePackExec) { $servicePackExec = Read-Host 'Full Path to service pack executable [Empty for None]? ' }

if ( -not $instance) { $instance = Read-Host 'Instance Name? ' }

if ( -not $collation ) {$collation = Read-Host 'Collation [Latin1_General_CI_AS]? ' }

if ( -not $collation ) { $collation = "Latin1_General_CI_AS" }

if ( -not $port ) { $port = Read-Host 'TCP Port [50000]? ' }

if ( -not $port ) { $port = "50000" }

if ( -not $ServiceAccountPassword ) {

[System.Security.SecureString]$ServiceAccountPassword = Read-Host "Enter the service account password: " -AsSecureString;

[String]$syncSvcAccountPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceAccountPassword));

} else { [String]$syncSvcAccountPassword = $ServiceAccountPassword; }

if ( -not $saPassword ) {

[System.Security.SecureString]$saPasswordSec = Read-Host "Enter the sa password: " -AsSecureString;

[String]$saPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($saPasswordSec));

} else { [String]$saPassword = $ServiceAccountPassword; }

$instance = $instance.ToUpper()

Before I prompt for the drive letters for the installation paths, I display a little table with the available local disks:

function displayDrives () {
    Get-WmiObject -class "Win32_LogicalDisk" | ?{ @(2, 3) -contains $_.DriveType } | where {$_.Freespace} | select Name, VolumeName, Size, FreeSpace
}

########################
# Getting Disk Letters #
########################

$driveDisplayed=0

if ( -not $instDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $instDrive = Read-Host 'Drive letter (without colon) for Instance Installation? '
}

if ( -not $userDbDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $userDbDrive = Read-Host 'Drive letter (without colon) for User Databases? '
}

if ( -not $userLogDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $userLogDrive = Read-Host 'Drive letter (without colon) for Transaction Logs ? '
}

if ( -not $tempDbDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $tempDbDrive = Read-Host 'Drive letter (without colon) for Temp Database ? '
}

if ( -not $tempLogDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $tempLogDrive = Read-Host 'Drive letter (without colon) for Temp Logs ? '
}

if ( -not $backupDrive ) {
    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }
    $backupDrive = Read-Host 'Drive letter (without colon) for Backups ? '
}

function displayDrives () {

Get-WmiObject -class "Win32_LogicalDisk" | ?{ @(2, 3) -contains $_.DriveType } | where {$_.Freespace} | select Name, VolumeName, Size, FreeSpace

}

########################

# Getting Disk Letters #

########################

$driveDisplayed=0

if ( -not $instDrive ) {