Streamlining SQL Server management: DETECT

This article covers the first step in the D-BART method, a structured approach to managing SQL Server environments.

What’s in it for you?

Better decision-making: Know what you’re running before you tune, patch, or upgrade.
Faster troubleshooting: Spot risky settings and inconsistencies quickly.
Improved security and compliance: Identify misconfiguration and exposed features.
Operational consistency: Ensure environments are aligned (Dev ≠ Prod)
Foundation for automation: A complete, trusted inventory makes scripting and reporting far easier.

Detect, is all about building a detailed inventory of your SQL Server assets and settings. Whether you’re managing a few instances or an enterprise-wide estate, you need to know exactly what you’re running, how it’s configured, and whether it aligns with your operational and security standards. This discovery step forms the foundation for everything that follows in the D-BART approach.

As we’re talking about SQL Server, what better place is there to store this inventory than a SQL Server database itself? The good news: we don’t need complex tools to get started. SQL Server already exposes the relevant data natively through system views.

Below are the key datasets I gather from all SQL servers in the environment.

Instance-Level Inventory: SERVERPROPERTY()

Gathering the results from querying SERVERPROPERTY gives you your SQL Server instance fingerprint, a fast, reliable way to answer:

· What am I running? (edition, version, build, licensing)

· What’s turned on? (HADR, PolyBase, XTP, Full-Text)

· Where’s my stuff? (default data/log/backup paths)

· Is it safe & supported? (clustered? external authentication? deprecated features?)

· Does it match other environments? (Dev ≠ Prod? Why?)

Database-Level Insight: sys.databases

SELECT * FROM sys.databases

1	SELECT * FROM sys.databases

SELECT * FROM sys.databases

1	SELECT * FROM sys.databases

This view is your database radar, helping you quickly identify:

· Problems: Risky settings like AUTO_CLOSE, AUTO_SHRINK, or databases in suspect state

· Configs: Ownership, read-only status, containment, user access

· Misconfigurations: Wrong recovery model, collation mismatch, outdated compatibility levels

· Security flags: Trustworthy bit, encryption settings

· Isolation level setup: READ_COMMITTED_SNAPSHOT, SNAPSHOT_ISOLATION

Hardware & Host Info: sys.dm_os_sys_info

SELECT * FROM sys.dm_os_sys_info

1	SELECT * FROM sys.dm_os_sys_info

This is your hardware view, essential for:

· Understanding compute resources: CPU count, logical cores, memory, NUMA nodes

· Virtualization awareness: Physical or VM? (virtual_machine_type_desc)

Service-Level View: sys.dm_server_services

SELECT * FROM sys.dm_server_services

1	SELECT * FROM sys.dm_server_services

Your service dashboard, letting you verify operational state and configuration:

· What services are running? SQL Engine, Agent, Full-Text, Launchpad, etc.

· How are they started? Auto vs Manual vs Disabled

· Who are they running as? Helps validate least privilege principles

· Are they configured correctly? (Agent not auto-starting? Service account mismatches?)

Engine Configuration: sys.configurations

SELECT * FROM sys.configurations

1	SELECT * FROM sys.configurations

This is your behavior blueprint—the actual runtime settings SQL Server follows:

· Performance tuning: max degree of parallelism, cost threshold, memory caps

· Security hardening: xp_cmdshell, clr enabled, remote access

· Best practices enforcement: optimize for ad hoc workloads, backup compression default, default trace enabled

Database Design

As you collect all of this information, you’ll naturally design a central repository, a SQL Server database that stores your inventory data. Think structured tables by server, instance, database, configuration, and services, refreshed regularly via automated jobs or PowerShell scripts.

Is That Everything?

Not quite. Depending on your environment, you may also want to gather metadata about:

· High Availability and Always On Availability Groups

· SQL Server Agent Jobs

· Clustering

· Backup history, user logins, permissions, and more

Conclusion

Having a central, queryable SQL Server inventory is fundamental to efficient database administration. It enables proactive monitoring, ensures compliance, and supports informed decisions on upgrades, performance tuning, and security hardening. Start simple and let SQL Server tell you everything you need to know.