SQL Server
Setup Tool

One tool, one configuration, one standard. Set the domain once in the INI - everything else runs automatically afterward.

Prerequisites

SQLSources Structure

Phase 1 - Robocopy

Phase 3 - Optional Components

The tool passes all parameters fully from the INI + GUI to Install-DbaInstance. No INI file is created manually.

# Detect the slipstream CU automatically
$cuPath = Join-Path $localSrc 'SQL_Install\Updates'
$cuFile = Get-ChildItem $cuPath '*.exe' `
           -ErrorAction SilentlyContinue |
           Select-Object -First 1

# Install-DbaInstance call (excerpt)
$params = @{
  SqlInstance      = 'localhost'
  Version          = $ver
  Edition          = $edition
  Path             = $localSrc
  UpdateSourcePath = if ($cuFile) { $cuPath } else { $null }
  Collation        = $collation
  DataPath         = $paths.Data
  LogPath          = $paths.Log
  TempPath         = $paths.TempDB
  BackupPath       = $paths.Backup
  Features         = $features
  SqlSvcAccount    = $svcAccount
  SqlSvcPassword   = $svcPassword
  ProductID        = $productKey
  PerformVolumeMaintenanceTasks = $true
}
Install-DbaInstance @params -Confirm:$false

The tool detects the domain automatically at startup and loads domain-specific defaults from the INI - with no code changes.

# settings.ini (excerpt)
[DiskLayout_Standard]
DataDrive   = G
LogDrive    = H
TempDrive   = I
BackupDrive = F
InstallDrive= C

[Collations]
Standard       = Latin1_General_CI_AS
Domain_HLB     = SQL_Latin1_General_CP1_CI_AS
Domain_CONTOSO = Latin1_General_CI_AS

[SysadminGroups]
Domain_HLB     = HLB\Fg_DC_SqlAdminAll_Mod
Domain_CONTOSO = CONTOSO\Rg_SQL_Sysadmin
Standard       =   # empty = no sysadmin

[Monitoring]
Domain_HLB     = 2  # Full
Domain_CONTOSO = 1  # Standard
DefaultType    = 1

Previously 57 individual SQL scripts installed manually afterward. Today largely covered by sqmSQLTool - the rest runs automatically.

# Invoke-SqlScriptFolder - flow
$scripts = Get-ChildItem '\\srv\SQLSources\Scripts' `
            -Filter '*.sql' | Sort-Object Name

# Alphabetical: 001_ through 057_
foreach ($script in $scripts) {
  $sql = Get-Content $script.FullName -Raw

  # GO separator: split into batches
  $batches = $sql -split '\r?\nGO\r?\n|\r?\nGO$' |
              Where-Object { $_.Trim() -ne '' }

  foreach ($batch in $batches) {
    try {
      Invoke-DbaQuery -SqlInstance $inst `
          -Query $batch -ErrorAction Stop
      log "  OK: $($script.Name)"
    } catch {
      log "  WARN: $($script.Name) - $_"
      # ContinueOnError - move to the next script
    }
  }
}

The SA account is the best-known attack target on SQL Server. The tool obfuscates it automatically - safely, with no manual intervention.

After setup - sqmSQLTool tasks

AlwaysOn After Setup

Outside the Scope

Design Decisions

Key Takeaways

Links & Resources

Next Presentations