DBA Scripts: Server and Configuration

Peter Whyte

–

🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.

What This Server Is, and How It Got That Way

Both pillars here answer one underlying question: before performance, before security, before anything else, what is this SQL Server instance, and how did it come to be configured the way it is.

Server Inventory answers the first half, a snapshot of what’s running right now, version, hardware, configuration, licensing, connections.

Installation and Patching answers the second half, the lifecycle actions that put a server in that state and keep it there: install, configure, patch, and eventually uninstall.

Start Here

What version and edition am I looking at?Version and EditionIs it actually patched?Patch LevelWhat has been changed from the defaults?Configuration SnapshotHow does this server score against best practice?Configuration ScoreWhat hardware is underneath it?OS and Hardware Info

The Pillars

📁 Server Inventory

The 6 questions to answer before touching an unfamiliar server: version, hardware, configuration, licensing, connections, databases.

💻 Installation and Patching

Install, configure, patch, and uninstall SQL Server and SSMS, scripted across the whole lifecycle.

Why They Belong Together

  • Inventory is a snapshot, Installation and Patching is the process that produced it. Run Server Inventory on a server today and it tells you the version, edition, and configuration as they stand. Installation and Patching’s scripts are how that state was reached, and how it changes going forward: an unattended install with validated parameters, sensible sp_configure defaults applied on top, and a repeatable patch path to keep the version current.
  • Both are the first thing you touch, not the last. Every other area on this site (performance, security, HA) assumes you already know what server you’re looking at and that it’s configured sanely. These pillars are what make that assumption safe to make.

How They Fit Together

A server’s lifecycle through both pillars, roughly in order:

1
BEFORE INSTALLPre-Install ChecksConfirms the machine is actually ready.
2
INSTALLInstall and Configure SQL Server then the post-install checkDoes the unattended install and applies sensible defaults, then validates that the result actually matches what was intended.
3
NOW IT EXISTSServer InventoryBecomes the ongoing reference: what version, what hardware, what’s configured, what’s connected. Run it any time you inherit the server, before a migration, or just to confirm nothing’s drifted.
4
KEEP IT CURRENTPatch SQL Server and Check StatusHandles the ongoing CU cadence, and the SSMS install and update scripts do the same for the client tooling DBAs actually use day to day.
5
EVENTUALLY, DECOMMISSIONUninstall SQL ServerRemoves it cleanly when its life is over.

Best Practices Across This Area

  • Run Server Inventory as the very first step on any server you didn’t build yourself, before performance tuning, before security review, before anything.
  • Treat a post-install validation failure the same as an install failure, a server that “installed” but isn’t configured as intended is not actually done.
  • Re-run Server Inventory after any patch or configuration change, not just at the start, drift is the thing this pillar exists to catch.
  • Keep SSMS current on DBA workstations with the same discipline as the engine itself, an outdated client is its own quiet source of confusing bugs.

Frequently Asked Questions

What should I check first on a server I have never seen?

Version and edition, then whatever has been changed from the defaults. Edition determines which features are even legal to use, and non-default configuration is where the surprises live: MAXDOP, max memory, and trace flags someone set years ago for a reason nobody recorded.

Why does edition matter so much?

Because it silently governs capability. Online index rebuilds, Resource Governor and readable secondaries are Enterprise-only on every version, so a script that works on one instance can fail on another for licensing reasons rather than technical ones. Check the version before assuming the rest, because several features moved: compression, partitioning, CDC and database snapshots came to Standard in SQL Server 2016 SP1, and TDE followed in 2019.

Is a default MAXDOP of 0 a problem?

Usually yes on a modern multi-core server. It lets a single query use every scheduler, which turns one heavy query into a server-wide event. The right value depends on core count and NUMA layout, which is exactly what the CPU topology script reports.

Should I care about trace flags I did not set?

Yes, and especially the ones nobody can explain. Trace flags change engine behaviour globally and often outlive the problem they were added for, so an unexplained flag is worth researching before it becomes load-bearing by accident.

Summary

Server Inventory and Installation and Patching are two sides of the same question, what a server is, and how it got that way. Between them they run from pre-install readiness through to clean uninstall, with Server Inventory as the recurring checkpoint in between. Start here on any server you didn’t build yourself.

Related

Major pillars
DBA ScriptsEvery script, one page each SSMS Complete GuideInstall, shortcuts, git, Copilot, dark mode Error LibraryLook up an exact message, cause and fix Wait Types LibraryWhat the server was waiting on Site MapHow the pillars, areas and scripts connect
Quick scripts
Get-ActiveSessions Get-UserPermissionsAudit Get-DiskSpace Get-DatabaseFreeSpaceSummary Get-IndexFragmentation Get-AvailabilityGroupLatency Get-BackupCoverage Get-BackupRestoreProgress
Patch week
SQL Server BuildsLatest CU per version, with support dates Patch LevelWhat your server is running
Health check & AI
Full Health CheckCollect 45 diagnostics, apply rules, then an AI assessment sqldba MCP ServerGive an AI assistant a verified SQL Server reference
Script areas
Server & Configuration Storage & Capacity Backup & Recovery High Availability Performance & Troubleshooting Security Maintenance & Automation Migration & Deployment
Latest posts
Browse by topic

Comments

Leave a Reply

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