Adding SQL Services to an AutomatedLab Configuration

I switched back my efforts to AutomatedLab recently, mostly because education never stops. My main area of focus is to learn a little SQL Server, so I wanted a SQL Server lab. AutomatedLab – my preferred mechanism for creating labs on Hyper-V – has a nice role that does installs for SQL Server 2012 and 2014, but it wasn’t exactly adjustable and I wanted mine adjustable. I wanted to put the data on a separate drive, run the server as a domain service account, and only install the minimum required.

My first step was to add a SQL Server machine to my configuration. Since I’m also doing a little Splunk work (I can’t really get away from it right now), I did the following:

$SQLAct = @()
$SQLAct += Get-LabPostInstallationActivity -ScriptFileName InstallSplunkUF.ps1 -DependencyFolder "$PSScriptRoot\InstallSplunkUF"
Add-LabMachineDefinition -Name SQL1 `
  -Processors 2 -MemoryInMb 1024 -DiskSizeInGb 60 `
    -IPAddress "$Network.6" -DnsServer1 "$Network.2" -DnsServer2 "$Network.3" `
    -Network $LabName -DomainName $LabDNS -IsDomainJoined `
    -InstallationUserCredential $Creds -OperatingSystem 'Windows Server 2012 R2 SERVERSTANDARD' `
    -UserLocale en-US -TimeZone 'Pacific Standard Time' `
    -PostInstallationActivity $SQLAct -ToolsPath $Tools

This is very similar to our other machines – just no roles. We don’t want to use the SQL Server 2012 role. Down that path leads a completely standard SQL Server deployment that is good for development purposes, but no good for what I want. I need another hard drive before I get to installing SQL Server. I do that right before the Install-Lab -StartRemainingMachines so that I can add the hard drive after definition but before power-on:

Get-VM | Where Name -Like "SQL*" | Foreach-Object {
  $Name = $_.Name
  $VPath = "$LabPath\Data-$Name.vhdx"
  Remove-Item -Force -Path $VPath
  New-VHD -Path $VPath -SizeBytes 100GB -Dynamic
  Add-VMHardDiskDrive -VMName $Name -Path $VPath

This adds a 100GB drive to any machine starting with SQL (i.e. all my SQL machines, if they are so defined). I use the Install-LabPostInstallActivity cmdlet for the majority of the work, which includes:

  • Install any pre-requisites, like .NET Framework v3.5
  • Format the new drive as the I: drive
  • Create an I:\SQL directory
  • Mount the SQL Server ISO
  • Run the SQL Server unattended setup
# Handle each of the SQL Machines
$SQLMachines = Get-LabMachine -All | Where Name -like "SQL*"
foreach ($Machine in $SQLMachines)
    # Install .NET Framework 3.5
    Install-LabWindowsFeature -Name $Machine.Name -FeatureName Net-Framework-Core

    # Mount the DVD Drive with the ISO 
    Set-VMDvdDrive -VMName $Machine.Name -Path $SqlServer
    # Initialize the Data Disk
    Invoke-LabPostInstallActivity -ActivityName 'FormatDataDisk' -ComputerName $Machine.Name -UseCredSsp -ScriptBlock {
        Get-Disk | 
            Where PartitionStyle -eq 'raw' | 
            Initialize-Disk -PartitionStyle MBR -PassThru | 
            New-Partition -DriveLetter 'I' -UseMaximumSize | 
            Format-Volume -Verbose -FileSystem NTFS -NewFileSystemLabel "SQLData" -Confirm:$false

    # Turn off the Firewall
    Invoke-LabPostInstallActivity -ActivityName 'DisableFirewall' -ComputerName $Machine.Name -UseCredSsp -ScriptBlock {
        Set-NetFirewallProfile -Verbose -Name domain -Enabled false

    # Install SQL Server
    Invoke-LabPostInstallActivity -ActivityName 'InstallSQLServer' -ComputerName $Machine.Name -UseCredSsp -ScriptBlock {
        New-Item -ItemType Directory -Path I:\SQL

        Set-Location -Path (Get-WmiObject -Class Win32_CDRomDrive).Drive
        .\Setup.exe /Q /Action=Install /ENU /IAcceptSQLServerLicenseTerms `
                /UpdateEnabled=0 /ErrorReporting=0 /IndicateProgress `
                /Features="SQLEngine,Tools" /InstanceDir=I:\SQL /InstanceName=MSSQLSERVER `
                /AgtSvcStartupType=Disabled /BrowserSvcStartupType=Disabled `
                /SQLSvcAccount=LAB\svc_sqlserver /SQLSvcPassword=P@ssw0rd `
                /SQLSvcStartupType=Automatic /SQLSysAdminAccounts=LAB\SQLAdmins `
                /SQMReporting=0 /FilestreamLevel=0 /TCPEnabled=1

The Setup.exe contains all the necessary parameters to install SQL Server successfully. For this, I started with the TechNet page and then modified on a pre-built machine until I got it right. Then I just cut-and-paste it into the script. Note that I am specifying a service account on the domain and a domain group. I use an adjustment to my CreateDomain.ps1 script for these:

$sqlsvc = New-ADUser -Name svc_sqlserver -AccountPassword $SecurePassword -Path $ServicesOU -Enabled $true -PassThru
New-ADGroup -Name 'SQLAdmins' -SamAccountName SQLAdmins -Path $GroupsOU -GroupScope Domain -GroupCategory Security -DisplayName 'SQL Admins' 
Get-ADGroup -Identity 'SQLAdmins' | Add-ADGroupMember -Members @($devadmin,$sqlsvc) -PassThru

With this method (rather than the role method), you can install any type of SQL Server and also install differing types of SQL Server with differing settings as you see fit. This was exactly what I needed to move my work along.