SaltStack Config (SSC) comes bundled with vRealize Automation since version 8.3. You can use it to provision, configure, and deploy software to your virtual machines at any scale using event-driven automation. You can also use SaltStack Config to define and enforce optimal, compliant software states across your entire environment. This article showcases how I installed SQL server using SSC. This is a learning exercise and not a prod ready build.

I have created a simple Cloud Template with one machine, 4 disks and SaltStack Config resource.

The size of the disks are user input fields. SQL instance name, System Administrator (sa) password, SQL service account and password are also user inputs and are passed through to SSC as variables.

 variables:
        SqlInstanceName: ${input.SqlInstanceName}
        sqlServiceAccount: ${input.sqlServiceAccount}
        sqlServiceAccountPassword: ${input.sqlServiceAccountPassword}
        saPassword: ${input.saPassword}

The disks are assigned individual SCSI controller in the Cloud Template and I have used the disk number to format the disks and assign labels in the correct order. Below is the snippet from the PS script that does the disk format.

try {
    WriteInfo "Format SQLDATA disk"
    New-Partition -DiskNumber 4 -UseMaximumSize -DriveLetter D
    Format-Volume -DriveLetter D -NewFileSystemLabel SQLDATA

    WriteInfo "Format SQLLOG disk"
    New-Partition -DiskNumber 1 -UseMaximumSize -DriveLetter E
    Format-Volume -DriveLetter E -NewFileSystemLabel SQLLOG

    WriteInfo "Format TEMPDB disk"
    New-Partition -DiskNumber 2 -UseMaximumSize -DriveLetter F
    Format-Volume -DriveLetter F -NewFileSystemLabel TEMPDB

    WriteInfo "Format TEMPLOG disk"
    New-Partition -DiskNumber 3 -UseMaximumSize -DriveLetter G 
    Format-Volume -DriveLetter G -NewFileSystemLabel TEMPLOG
}
catch {
    writeError "Failed to configure disk : "+$_.Exception.Message
}

The above script is then called from the SSC state file.

initialize-disks:
  cmd.script:
    - name: salt://powershell/sql/disks.ps1
    - shell: powershell

The next step is to copy the SQL server installation files to the destination VM and I did that by using the file.recurse module. Once the file is copied I initiated the install.

For the install, I used a CMD script which is then run using the Invoke-Expression module.

param (
    [string]$sqlInstanceName,
    [string]$saPassword,
    [string]$sqlServiceAccount,
    [string]$sqlServiceAccountPassword
)
$SystemAdminAccounts = @("$Env:USERDOMAIN\$Env:USERNAME")
#$sqlInstanceName,$saPassword,$sqlServiceAccount,$sqlServiceAccountPassword | out-file "C:\Temp\variables.txt"
#Start Transscript for logging
Start-Transcript -Path "C:\Temp\sqlinstall.log" -Append

$installCmd = @(
    "C:\Temp\SQLServer2019\setup.exe"
    '/Q'
    '/INDICATEPROGRESS'
    '/IACCEPTSQLSERVERLICENSETERMS'
    '/ACTION="install"'
    '/UPDATEENABLED=false'
    '/PID="00000-00000-00000-00000-00000"'
    '/FEATURES=SQLENGINE,SDK'
    "/INSTANCENAME=$sqlInstanceName"
    "/SQLSVCACCOUNT='$sqlServiceAccount'"
    "/SQLSVCPASSWORD=$sqlServiceAccountPassword"
    '/SECURITYMODE="SQL"'
    '/SAPWD="$saPassword"'
    "/SQLSYSADMINACCOUNTS=$SystemAdminAccounts"
    '/INSTALLSQLDATADIR="D:"'
    '/SQLUSERDBDIR="D:\$sqlInstanceName\Data"'
    '/SQLUSERDBLOGDIR="E:\$sqlInstanceName\Log"'
    '/SQLTEMPDBDIR="F:\$sqlInstanceName\TempDB"'
    '/SQLTEMPDBLOGDIR="G:\$sqlInstanceName\TempLog"'
)

#debug
$installCmd | out-file "C:\Temp\sqlscript.txt"

Invoke-Expression "$installCmd"
Stop-Transcript

The above PS script is run using the state file like so.

{% set sqlInstanceName = pillar.get('SqlInstanceName', '') %}
{% set saPassword = pillar.get('saPassword', '') %}
{% set sqlServiceAccount = pillar.get('sqlServiceAccount', '') %}
{% set sqlServiceAccountPassword = pillar.get('sqlServiceAccountPassword', '')|json %}

install-sql:
  cmd.script:
    - name: salt://powershell/sql/sqlinstall.ps1
    - shell: powershell
    - args: {{sqlInstanceName}} {{saPassword}} {{sqlServiceAccount}} {{sqlServiceAccountPassword}}

All the state files to be run by SaltStack Config are specified in the resource in the Cloud Template.

Cloud_SaltStack_1:
    type: Cloud.SaltStack
    properties:
      hosts:
        - ${resource.Machine.id}
      masterId: saltstack_enterprise_installer
      stateFiles:
        - /powershell/debug/debug.sls
        - /powershell/sql/copyfile.sls
        - /powershell/sql/disks.sls
        - /powershell/sql/sqlinstall.sls
        - /powershell/sql/ssmsinstall.sls
      saltEnvironment: sse

Once the cloud template is deployed, it takes about 30 minutes for the deployment to complete and SQL server is installed.


0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *