Two servers built from the same checklist do not stay identical forever. Configuration drift appears through patches, emergency changes, and well-meant fixes that never reach the other instances.
Decide What Counts as Configuration Drift
A difference is only useful when it affects support, performance, security, or recovery. Server name and path differences are expected. Max server memory, MAXDOP, backup defaults, remote access, and authentication settings deserve review. Start with a small set of settings that your team actually owns. A report containing every property becomes a weekly exercise in ignoring a report.
I have seen a configuration comparison presented as a wall of red cells. Nobody knew which cell needed action. Define the baseline, the expected value, the owner, and the reason for exceptions. Then a change becomes a decision instead of a surprise. Which settings would make you stop a deployment if they differed today? Those belong near the top.
Capture Server Configuration
sys.configurations is a useful starting point. value is configured, while value_in_use is what the running engine currently uses. They differ when a setting needs a restart or a reconfigure action. Capture both. The query below filters to a few common settings so you can read the output without hunting through rows. Adjust the list for your environment.
Run the same query on each instance and save the instance name and capture time with the results. A screenshot without a timestamp is weak evidence. Do not assume a higher or lower number is automatically better. Hardware, workload, and server role govern the approved value.
SELECT @@SERVERNAME AS instance_name,
name,
value,
value_in_use,
is_dynamic
FROM sys.configurations
WHERE name IN
(
N'max server memory (MB)',
N'max degree of parallelism',
N'cost threshold for parallelism',
N'backup compression default'
)
ORDER BY name;Collect Database-Level Choices
Drift also lives inside databases. Recovery model, compatibility level, page verification, automatic close, and automatic shrink can vary after restores and migrations. An application that behaves differently between two environments can have a database setting difference rather than a code defect. Capture the database name and current state before drawing conclusions.
I use this inventory when a supposedly identical test server behaves differently. The output is short enough to compare and broad enough to catch several common traps. A new database restored from an old source deserves a fresh review. The database carried its settings along for the ride, whether anybody invited them or not.
SELECT @@SERVERNAME AS instance_name,
name,
compatibility_level,
recovery_model_desc,
page_verify_option_desc,
is_auto_close_on,
is_auto_shrink_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;Build a Baseline With Exceptions
Write one approved value for each role, such as production OLTP, reporting, or development. Record exceptions beside it with a reason, owner, and review date. That is more honest than forcing every server into one template. A small reporting instance and a large transaction system should not inherit the same memory cap merely because both run SQL Server.
The baseline needs a stable home that the operations team can read during an incident. Keep the review history there too. If a value changes, capture who approved the change and why. A baseline without a revision process turns into another outdated document. A configuration drift alert should point to the approved record rather than ask the on-call DBA to guess.
Separate Pending Changes
A difference between value and value_in_use on one server deserves special attention. It means the intended setting and live behavior are different. That can happen after a change that awaits restart, or after a command that did not apply as expected. Report this category separately from differences between servers. It calls for a different operational response.
Check the SQL Server error log for restart history when a change was expected to take effect. Confirm that maintenance windows and service restarts happened as planned. Do not restart merely to make a comparison green. First establish what else depends on the instance and when the change was approved. The comparison is an observation, not permission to disrupt service.
Investigate Configuration Drift Before Correcting
When a value differs, inspect the reason. A server can host an unusual workload, share memory with another service, or carry a temporary incident setting that should now be removed. A useful review asks when it changed, who owns it, and what evidence supports the current value. The answer can be that the baseline itself needs revision.
I treat an unexplained difference as an investigation item. Automatic correction is risky for settings that affect execution plans, memory, or access. If the value is wrong, script the intended change and test its effect in the appropriate environment. If the value is right, document the exception. Either outcome improves the next review.
Schedule a Calm Configuration Drift Comparison
Collect configuration snapshots on a regular schedule and after planned deployments. Compare each new snapshot with the last approved one. Keep collection read-only and small enough that it can run during normal hours. Include server build and service account context if those are part of your standard. The output should identify new changes, not repeat every known difference forever.
A practical alert names the instance, setting, old value, new value, and baseline status. It also gives the owner a path to acknowledge an approved exception. Without that workflow, the alerts slowly become background noise. Silence is not proof of consistency when everyone has muted the report.
Review the Baseline Itself
Standards age. SQL Server versions change defaults, hardware changes, and applications move. Review the baseline after upgrades and major workload changes. Confirm that each rule still protects a real requirement. Remove checks that no longer matter and add new ones when incidents reveal a blind spot. Keep exceptions from becoming permanent by accident.
The strongest configuration drift process is a short conversation backed by a clear diff. It tells you what changed and invites a decision. A dashboard full of identical green squares can be comforting, but it cannot explain whether the chosen standard is still right. That review belongs to people who know the workload.
Related reading on this blog: Monitoring Database Autogrowth Settings and SQL SERVER 2019: New Values in Sys.Configurations.
Configuration drift is not every difference, it is an unexplained difference from an approved standard.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
