PowerShell - SQL Server 2008 Configuration

In an effort to continue to improve the quality of my code while expounding into new areas, I am now revisiting SQL Server 2008.

Previously, I created a PowerShell script to handle finishing the completion of a sysprep'd installation.  Now, I want to change my script logic to be more generic, and to include adding/installing the Analysis Service (SSAS) feature.

Below will be the script code followed by configuration xml snippet (I like to drive things via configurable XML file).  At the end of this post, you will see links tied back to the past post, and other related ones as well.

*************************

***** PowerShell Script *****

*************************

$configFilepath = $args[0]

$config = [xml](Get-Content $configFilepath)

$logFile = $config.Configuration.Log.Path

$ssCfg = $config.Configuration.SqlServer

$lvlInfo = 0

$lvlWarn = 1

$lvlError = 2

Write-Log $logFile "Begin SQL Server Setup" $lvlInfo

# Capture configuration into local variables

$argStrs = $ssCfg.Installation.ArgumentString

$properties = $ssCfg.InstallationProperty

$SqlSetupPath = $ssCfg.Installation.Installer

$SqlInstallIso = $ssCfg.Installation.ISO

$InstanceID = $properties | Where {$_.name -eq "instanceId"} | %{$_.value}

$InstanceName = $properties | Where {$_.name -eq "instanceName"} | %{$_.value}

$userName = $properties | Where {$_.name -eq "username"} | %{$_.value}

$password = $properties | Where {$_.name -eq "password"} | %{$_.value}

$propArray = $properties | Sort {$_.index} | %{$_.value}

Write-Log $logFile "Mount SQL Server installation media ($($SqlInstallIso))" $lvlInfo

$driveLetter = MountISO $SqlInstallIso

# Build foundation for SQL Server installer executable string.

$cmd = $driveLetter

$cmd += $SqlSetupPath

# Execute each installation action

foreach($cmdArgs in $argStrs)

{

    $expr = $cmd + ([string]::Format($cmdArgs, $propArray))

    Write-Log $logFile "Invoke expression: $($expr)" $lvlInfo

    invoke-expression $expr | Out-Null

    # take pause to ensure install process is complete

    start-sleep -s 10

}

# We are done with the installation media.  Time to unmount.

UnmountISO

# *** Address the SQLBrowser service ***

Write-Log $logFile "Configure SQLBrowser service." $lvlInfo

# Capture the name of the server

$serverName = $env:computername

#Retrieve the WMI object presentation of the service

$service Get-WMIObject win32_service -ComputerName $serverName -filter "name='SQLBrowser'"

# Try stopping the service

try

{

    $service.StopService()

}

catch

{

    Write-Log $logFile $Error[0] $lvlWarn

}

$service Get-WMIObject win32_service -ComputerName $serverName -filter "name='SQLBrowser'"

# Modify the log on credentials for the service.

$service.Change($null, $null, $null, $null, $null, $null, $userName, $password, $null, $null, $null)

# Try starting the service

try

{

    $service.StartService()

    # Give the service a moment to ensure completely started.

    start-sleep -s 10

}

catch

{

    Write-Log $logFile $Error[0] $lvlWarn

}

*************************

***** Configuration File *****

*************************

<Configuration>

    <Log Path="c:\windows\temp\install.log.txt" />

    <SqlServer>

        <Installation ISO="\\server\folder\en_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522665.iso" Installer="\setup.exe">

        <ArgumentString> /q /ACTION=CompleteImage /INSTANCEID="{0}" /INSTANCENAME="{1}" /SQLSVCACCOUNT="{2}" /SQLSVCPASSWORD="{3}" /RSSVCACCOUNT={2}" /RSSVCPASSWORD="{3}" /AGTSVCACCOUNT="{2}" /AGTSVCPASSWORD="{3}" /SQLSYSADMINACCOUNTS="{4}" /IACCEPTSQLSERVERLICENSETERMS</ArgumentString>

        <ArgumentString> /q /ACTION=Install  /INSTANCEID="{0}" /INSTANCENAME="{1}" /FEATURES=AS /ASSVCACCOUNT="{2}" /ASSVCPASSWORD="{3}" /ASSYSADMINACCOUNTS="{4}" /IACCEPTSQLSERVERLICENSETERMS</ArgumentString>

        <Property index="0" name="instanceId" value="SQL_INSTANCEID" />

        <Property index="1" name="instanceName" value="NEW_MSSQLSERVER" />

        <Property index="2" name="username" value="domain\username" />

        <Property index="3" name="password" value="complexpassword" />

        <Property index="4" name="sysadminaccounts" value="builtin\administrators" />

        </Installation>

    </SqlServer>

</Configuration>

*************************

*************************

PowerShell - Completing the configuration of a SysPrep'd Sql Server 2008

Mounting ISOs with PowerShell