DBA Scripts: Install and Configure SQL Server

Part of the DBA-Tools Project.

---

Unattended Install, Then Sensible Defaults Applied on Top

Two scripts covering the doing half of a SQL Server install: install-sql.ps1 drives setup.exe with validated, logged parameters instead of clicking through the GUI, and configure-sql.ps1 applies the recommended sp_configure settings afterward with a before/after comparison. Every change from both scripts is logged.

Every script in powershell/installation/ shipped with a corrupted double UTF-8 BOM at the very start of the file, silently breaking PowerShell’s parser on all six of them. Fixed below.

---

The Bug: A Double BOM Broke Every Script in the Folder

Running pre-install-check.ps1 for the first time failed immediately:

ParserError: pre-install-check.ps1:7:4
Line |
   7 |  for each check. Does not make any changes to the system.
     |     ~
     | Missing opening '(' after keyword 'for'.

Line 7 is plain English inside the script’s <# ... #> help comment, “for each check” isn’t code at all. PowerShell was somehow not recognizing the comment block as a comment. Checking the raw bytes at the start of the file explained why:

efbb bfef bbbf 3c23 0a2e 5359 4e4f 5053  ......<#..SYNOPS

ef bb bf is a UTF-8 byte-order mark, and it appears twice in a row before the <# that should open the comment block. A double BOM, not a single one. Something had re-saved these files with “add a BOM” enabled when they already had one, stacking a second, uninterpreted U+FEFF character directly in front of the comment opener and confusing the tokenizer badly enough that it stopped treating the whole block as a comment.

All six scripts in powershell/installation/ had the identical corruption: pre-install-check.ps1, install-sql.ps1, configure-sql.ps1, post-install-validation.ps1, uninstall-sql.ps1, and generate-install-report.ps1. None of them would run. The fix was mechanical, strip the duplicate BOM bytes, leaving a single valid one:

data = open(path, 'rb').read()
bom = bytes([0xef, 0xbb, 0xbf])
assert data[:6] == bom + bom
fixed = bom + data[6:]
open(path, 'wb').write(fixed)

All six now parse and run cleanly, confirmed by actually running them, not just checking syntax.

---

Why a Generated, Logged Install and Configure Pair Matters

• Unattended install parameters reviewed once, run consistently every time, no risk of a fat-fingered click through setup.exe‘s GUI on server 14 of 20
• The SA password is never written to disk or logged, everything else about the install is
• configure-sql.ps1 shows exactly what it’s about to change before it changes it, a before/after table, not a silent RECONFIGURE

---

install-sql.ps1

Parameter-driven install with interactive fallback. Validates inputs before calling setup.exe, applies recommended MaxMemory/MaxDOP/CostThreshold post-install unless skipped, and logs full install output to output-files\installation\.

<#
.SYNOPSIS
Install SQL Server from setup.exe with validated parameters and post-install configuration.

.PARAMETER SetupPath
Full path to SQL Server setup.exe (e.g. D:\SQL2022\setup.exe).

.PARAMETER SAPassword
SA account password as a SecureString. Prompted interactively if not supplied.

.PARAMETER MaxMemoryGB
Max server memory in GB. Auto-calculated as (TotalRAM - 4 GB) if not supplied.

.PARAMETER MaxDOP
Max degree of parallelism. Auto-calculated from logical CPU count if not supplied.

.PARAMETER WhatIf
Preview the setup.exe command without executing it.
#>

(The full script is in the repo, link below.)

Not run live on this machine. -WhatIf on this script still requires an elevated shell (confirmed: it errors with “This script must be run as Administrator” even in preview mode, a real inconsistency against its sibling scripts covered below, none of which require elevation just to preview), and actually installing a second SQL Server instance onto this lab box wasn’t worth the disk space or the risk to the working environment this whole site runs from. Covered here from a full code read rather than a live run, said plainly rather than implied.

---

configure-sql.ps1

Applies recommended or custom sp_configure settings to an existing instance, with a before/after comparison for every setting changed. All changes use RECONFIGURE WITH OVERRIDE and are logged.

<#
.SYNOPSIS
Apply sp_configure settings to an existing SQL Server instance.

.DESCRIPTION
Applies recommended or custom SQL Server configuration settings.
Shows a before/after comparison for every setting changed.
All changes use RECONFIGURE WITH OVERRIDE and are logged.
#>

(The full script is in the repo, link below.)

Real Output — WhatIf Against This Lab Instance

Unlike install-sql.ps1, this one previews without needing elevation:

[11:09:38] SQL Server configuration — .
[11:09:38] Log: output-files\installation\configure-.-20260730-110938.log

[11:09:39] Planned configuration changes:
[11:09:39] Setting                                         Current  →  New
[11:09:39] ------------------------------------------------------------------------
[11:09:39] max server memory (MB)                             1600  →  3072
[11:09:39] max degree of parallelism                             8  →  8  (no change)
[11:09:39] cost threshold for parallelism                        5  →  50
[11:09:39] backup compression default                            0  →  1
[11:09:39] optimize for ad hoc workloads                          0  →  1
[11:09:39] remote admin connections                              0  →  1

[11:09:39] WhatIf: 5 setting(s) would be applied.

Every one of these lines up exactly with the WARN findings from post-install-validation.ps1 run against the same instance minutes earlier, cost threshold, backup compression, and optimize for ad hoc workloads were all flagged there and all show up here as planned fixes. The two scripts genuinely close the loop on each other.

---

How To Run From The Repo

git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1

.\powershell\installation\install-sql.ps1 -SetupPath D:\SQL2022\setup.exe -WhatIf
.\powershell\installation\configure-sql.ps1 -WhatIf

These scripts live in the repo at:

• powershell/installation/install-sql.ps1
• powershell/installation/configure-sql.ps1

---

Understanding the Results

• A double-BOM file will fail with a confusing, unrelated-looking parser error pointing at plain English text inside a comment block, not at anything that looks like the real problem. Checking the first few bytes of the file is the fast way to confirm it.
• configure-sql.ps1‘s “(no change)” rows are informative, not noise. Seeing a setting already at the recommended value is useful confirmation, not just something to filter out.
• install-sql.ps1 requiring elevation even for -WhatIf is a real gap, not intentional caution, every sibling script in this pillar previews without it.

---

Best Practices

• Verify a script parses at all (pwsh -File script.ps1 -WhatIf or just opening it) before assuming a strange PowerShell parser error is your own typo, a corrupted BOM produces exactly this kind of misleading error.
• Always run configure-sql.ps1 with -WhatIf first and read the before/after table before applying anything, especially max server memory, a wrong value here affects every workload on the instance immediately.
• Pair configure-sql.ps1 with post-install-validation.ps1, run validation, feed its WARN findings into the configure script, re-run validation to confirm.
• Never log or persist the SA password; install-sql.ps1 already handles this correctly, don’t work around it.

---

Related Scripts

You may also find these scripts useful:

• SQL Server Installation and Patching (hub)
• Pre-Install and Post-Install Checks
• Uninstall SQL Server
• Get Instance Configuration Snapshot

---

Frequently Asked Questions

My script fails with a parser error that points at plain English text, what’s wrong?

Check the first bytes of the file for a duplicated UTF-8 BOM (ef bb bf appearing twice before the actual content starts). A second, unstripped BOM character sitting in front of a <# ... #> comment block can break PowerShell’s tokenizer badly enough to misreport the actual line at fault.

Why does install-sql.ps1 need Administrator even to preview with -WhatIf?

It’s a genuine inconsistency against the rest of this pillar’s scripts, all of which preview without elevation. Worth flagging if you’re scripting a fully unattended preview pipeline, since this one script in the set will still stop you.

---

Summary

Two scripts, install then configure, both logged and both safe to preview before committing. Testing them surfaced a real, repo-wide bug, a corrupted double BOM breaking every script in the whole powershell/installation/ folder, now fixed and confirmed working. configure-sql.ps1‘s real output against this lab instance closed the loop with the previous post’s validation findings exactly as intended: the WARNs on one side became the planned fixes on the other.