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

I recently created a template for spawning new development virtual machines (VM).  This template included SQL Server 2008.  I am using Virtual Machine Manager to generate my templates.  Software, such as SQL Server 2008, are sysprep’d as part of the process.

Since it is sysprep’d, then I’m going to need to finish the configuration once a new VM is spawned.  The great thing about SQL Server 2008 is that I can accomplish this with a PowerShell script.

Below is my configuration file and PowerShell script for making this happen.

---------- SqlServerConfig.xml ----------

<Configuration>

  <Log Path="\\SomeDefinedLocationForLogs\SqlServer.log.txt" />

  <SqlServer

    SqlSetupPath="\setup.exe"

   SqlInstallIso="\\SomeSharedDriveWithInstallMedia\ISOs\SQLServer\2008\R2\en_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522665.iso"

    InstanceID="SQL_INSTANCEID"

    InstanceName="NEW_MSSQLSERVER"

    SqlSvcAccount="domain\username"

    SqlSvcPassword="password"

    RsSvcAccount="domain\username"

    RsSvcPassword="password"

    SqlSysadminAccounts="builtin\administrators"

    AgtSvcAccount="domain\username"

    AgtSvcPassword="password"

    AsSvcAccount="domain\username"

    AsSvcPassword="password"

    AsSysadminAccounts="builtin\administrators"

    IsSvcAccount="domain\username"

    IsSvcPassword="password"   

    SecurityMode="SQL"

    SaPassword="password"

    TcpEnabled="1"

    />

</Configuration>

------------------------------------------------

---------- SqlServerSetup.ps1 ----------

    $configFilepath = $args[0]

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

    # Capture configuration into local variables

    $SqlSetupPath = $config.Configuration.SqlServer.SqlSetupPath

    $SqlIntallIso = $config.Configuration.SqlServer.SqlInstallIso

    $InstanceID = $config.Configuration.SqlServer.InstanceID

    $InstanceName = $config.Configuration.SqlServer.InstanceName

    $SqlSvcAccount = $config.Configuration.SqlServer.SqlSvcAccount

    $SqlSvcPassword = $config.Configuration.SqlServer.SqlSvcPassword

    $RsSvcAccount = $config.Configuration.SqlServer.RsSvcAccount

    $RsSvcPassword = $config.Configuration.SqlServer.RsSvcPassword

    $AsSvcAccount = $config.Configuration.SqlServer.AsSvcAccount

    $AsSvcPassword = $config.Configuration.SqlServer.AsSvcPassword

    $IsSvcAccount = $config.Configuration.SqlServer.IsSvcAccount

    $IsSvcPassword = $config.Configuration.SqlServer.IsSvcPassword      

    $AgtSvcAccount = $config.Configuration.SqlServer.AgtSvcAccount

    $AgtSvcPassword = $config.Configuration.SqlServer.AgtSvcPassword

    $SqlSysadminAccounts = $config.Configuration.SqlServer.SqlSysadminAccounts

    $AsSysadminAccounts = $config.Configuration.SqlServer.AsSysadminAccounts       

    $TcpEnabled = $config.Configuration.SqlServer.TcpEnabled

    # Mount SQL Server installation media

    $driveLetter = MountISO $SqlIntallIso

   

    # Build SQL Server installer executable string.

    $cmd = $driveLetter

    $cmd += $SqlSetupPath

    # Build  string with command-line arguments for SQL Server installer

    $arguments = " /q"

    $arguments += " /ACTION=CompleteImage"

    $arguments += " /INSTANCENAME=`"$InstanceName`""

    $arguments += " /INSTANCEID=`"$InstanceID`""

    $arguments += " /SQLSVCACCOUNT=`"$SqlSvcAccount`""

    $arguments += " /SQLSVCPASSWORD=`"$SqlSvcPassword`""   

    $arguments += " /RSSVCACCOUNT=`"$RsSvcAccount`""

    $arguments += " /RSSVCPASSWORD=`"$RsSvcPassword`""   

    $arguments += " /AGTSVCACCOUNT=`"$AgtSvcAccount`""

    $arguments += " /AGTSVCPASSWORD=`"$AgtSvcPassword`""     

    $arguments += " /SQLSYSADMINACCOUNTS=`"$SqlSysadminAccounts`""

    $arguments += " /IACCEPTSQLSERVERLICENSETERMS"

   

    # Combine arguments string with installer command to create a complete command-line executable statement

    $cmd += $arguments

    # Invoke the command

    invoke-expression $cmd

    # take pause to allow the install process to complete

    start-sleep -s 10

   

    # Unmount the ISO.  User doesn't need it "in the drive"

    UnmountISO

---------------------------------------------