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

ClickOnce Install Actions

The following code class is designed to provide the ability to execute installing a Visual Studio Tools for Office (VSTO) into the ClickOnce cache.

This code would be desired for use by an MSI based installer or a utility tool.

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

using System;

using System.Configuration.Install;

using System.Diagnostics;

using System.IO;

using System.Security;

using System.Security.Permissions;

using System.Text;

namespace ClickOnceCustomActions

{

    public static class ClickOnceActions

    {

        public static bool CheckForPermission()

        {

            bool bRtn = false;

            try

            {

                SecurityPermission permission =

                    new SecurityPermission(PermissionState.Unrestricted);

                permission.Demand();

                bRtn = true;

            }

            catch (SecurityException)

            {

                throw new InstallException(

                    "You have insufficient privileges to " +

                    "install the add-in into the ClickOnce cache. " +

                    "Please contact your system administrator.");

            }

            return bRtn;

        }

        public static void InstallVSTO(string fileAbsolutePath)

        {

            string arguments = String.Format(

                        "/S /I \"{0}\"", fileAbsolutePath);

            int exitCode = ExecuteVSTOInstaller(arguments);

            if (exitCode != 0)

            {

                string message = null;

                switch (exitCode)

                {

                    case -300:

                        message = String.Format(

                            "The Visual Studio Tools for Office solution " +

                            "was signed by an untrusted publisher and as " +

                            "such cannot be installed automatically. " +

                            "Please use your browser to navigate to {0} in " +

                            "order to install the solution manually. " +

                            "You will be prompted if the solution is trusted for execution.",

                            fileAbsolutePath);

                        break;

                    default:

                        message = String.Format(

                            "The installation of the ClickOnce solution failed with exit code {0}",

                            exitCode);

                        break;

                }

                throw new InstallException(message);

            }

        }

        public static void UninstallVSTO(string fileAbsolutePath)

        {

            string arguments = String.Format(

                "/S /U \"{0}\"", fileAbsolutePath);

            ExecuteVSTOInstaller(arguments);

        }

        public static int ExecuteVSTOInstaller(string arguments)

        {

            string basePath = Environment.GetFolderPath(Environment.SpecialFolder.CommonProgramFiles);

            string subPath = @"Microsoft Shared\VSTO\9.0\VSTOInstaller.exe";

            string vstoInstallerPath = Path.Combine(basePath, subPath);

            if (File.Exists(vstoInstallerPath) == false)

            {

                throw new InstallException(

                    "The Visual Studio Tools for Office installer was not found.");

            }

            ProcessStartInfo startInfo = new ProcessStartInfo(vstoInstallerPath);

            startInfo.Arguments = arguments;

            Process process = Process.Start(startInfo);

            process.WaitForExit();

            int exitCode = process.ExitCode;

            return exitCode;

        }

    }

}