One Query. Full Server Visibility.
🔎 Why This Script Is Important
In enterprise SQL Server environments, quick and accurate server visibility is critical.
During:
-
Production incidents
-
DR audits
-
Migration planning
-
Capacity reviews
-
Performance investigations
We often need to manually collect information from multiple DMVs, system views, and configuration settings.
That process is time-consuming and error-prone.
So I built a single, consolidated server assessment script that delivers a complete infrastructure snapshot in one execution.
What This Script Delivers
This automation generates a comprehensive SQL Server environment report, including:
🖥 Server & OS Information
-
Server name and instance name
-
SQL Server version and edition
-
KB patch level
-
Windows OS build detection
-
Collation settings
-
Clustered or standalone status
💾 Storage & Disk Capacity
Using secure DMV-based methods (no deprecated procedures), it:
-
Enumerates fixed drives
-
Calculates drive capacity (in GB)
-
Generates a clean, readable disk summary
-
Provides structured storage visibility
Perfect for capacity planning and audit reporting.
🧠 Memory & CPU Configuration
-
Total physical RAM
-
SQL Server max memory setting
-
Total CPU count
-
Server-level configuration validation
Ensures quick identification of misconfigured memory or underutilized hardware.
🗄 Database Inventory
-
Total number of user databases
-
Total database size (GB)
-
Consolidated database name list
Useful for consolidation reviews and infrastructure sizing.
🔐 TDE (Transparent Data Encryption) Status
-
Detects whether TDE certificates exist
-
Quickly confirms encryption posture
Critical for compliance and security audits.
🔁 Always On Availability Group Details
If AG is enabled, the script captures:
-
Availability Group name
-
Replica server names
-
Listener DNS name
-
Primary replica detection
-
AO status (AG / Standalone)
If AG is not configured, it clearly reports standalone status.
🛠 Technical Highlights
✔ Uses modern DMVs (no deprecated methods)
✔ Secure disk enumeration via sys.dm_os_enumerate_fixed_drives
✔ Aggregates results into clean single-row output
✔ Automatically detects clustering & HADR
✔ Production-safe read-only queries
✔ Designed for audit, DR, and infrastructure documentation
All critical server metadata — in one structured output.
🎯 Where This Script Helps Most
-
Pre-migration assessments
-
DR documentation
-
Audit readiness
-
Health check reports
-
Environment baseline capture
-
New server validation
-
Capacity planning
Instead of running 15 different queries, you run one.
You can find script by below link:
No comments:
Post a Comment