Two servers are called identical until someone compares the settings. Configuration as code makes those differences visible and gives the team a repeatable way to apply approved values.
Write Down the Desired State as Configuration Code
Start with a short list of instance settings the team actually owns. Max server memory, MAXDOP, cost threshold for parallelism and backup compression are examples, but the desired value depends on the host and workload. Store the intended values in a reviewed script or configuration file under source control. Include a reason, owner and effective date for each choice. A number with no context is just a future argument waiting for a calendar invitation.
I prefer a small explicit baseline over a giant export of every sp_configure option. Most default settings need no local declaration. The baseline should distinguish policy from machine-specific sizing. Which values should match every instance, and which should be calculated or approved separately? That boundary is the central design decision. Configuration as code does not mean copying the same memory cap onto servers with different memory.
Capture the Current Values
Before applying anything, query the current instance. sys.configurations shows configured values and values in use. The difference matters because some options take effect immediately and others need additional action. Save the read-only output with the host identity and collection time. This gives a reviewable before state and a way to spot drift after a rebuild or upgrade.
I use the first query below during handovers because it makes hidden choices visible. A server can run successfully with a value nobody intended, and success alone does not explain why it was chosen. Reading configuration is safe; setting it is a change. Keep those operations separate so that an audit script can run without surprising the operator.
SELECT name,
value AS configured_value,
value_in_use AS running_value,
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;Keep Environment Values Explicit
A development instance and a production instance can follow the same configuration process while using different approved values. Put shared rules in one place and environment values in a reviewed parameter set. Do not hide production values in someone else’s command history. Keep secrets out of source-controlled scripts; those scripts should reference an approved secret delivery method when credentials are involved.
I label any deliberate exception with a reason and a review date. Without that, a drift report cannot tell an approved exception from an accidental change. The goal is the same process everywhere, not identical numbers everywhere. Reproducibility is about knowing how a value was selected and applied. A copied file with unexplained constants is reproducible confusion.
Keep the Configuration Code Fix Narrow
The application script should be narrow and idempotent where practical. Review the planned values, apply them in the appropriate maintenance process and verify the running values afterward. sp_configure with RECONFIGURE handles many instance settings, but some changes have extra requirements or restart implications. Do not turn every detected difference into an automatic production write.
This sample shows the form for one approved value. Replace 32768 with the environment’s reviewed maximum memory setting before execution. The script is intentionally separate from the inventory query. I do not trust a deployment that can only say it ran; it should say what it changed, what took effect and whether the observed state matches the approved baseline.
EXEC sys.sp_configure
@configname = N'max server memory (MB)',
@configvalue = 32768;
RECONFIGURE;
SELECT name,
value AS configured_value,
value_in_use AS running_value
FROM sys.configurations
WHERE name = N'max server memory (MB)';Review Configuration as Code in Source Control
Keep the baseline, environment values and change notes in source control so the team can review a proposed setting before it reaches an instance. A change should explain why the value is appropriate, which systems receive it, how it was tested and how to reverse it. Review history also helps distinguish intentional tuning from a one-off troubleshooting change that was never reverted.
I ask reviewers to read the value and its evidence, not only the script syntax. A perfectly valid sp_configure call can still be a poor setting for the machine. Source control provides a shared record and review path; it does not certify the operational judgment. The database still needs post-change observation under its real workload. Versioned configuration is a conversation with evidence attached.
Detect Drift Without Auto-Fixing It
Schedule or run a read-only comparison between approved values and observed values. Report both configured and running values so a pending restart is not confused with compliance. A drift report should identify the instance, option, expected value, observed value and approved exception if one exists. Then a human or controlled deployment process decides whether to change the server or update the baseline.
I have seen emergency troubleshooting values quietly become permanent. A drift check catches that class of mistake without guessing why the change happened. It also catches rebuilds where a standard setting was omitted. If a difference is intentional, record it. If not, plan the correction. An alert that fires every morning for an accepted exception is not governance; it is a snooze-button exercise.
Verify the Result and the Rollback
After applying a change, rerun the inventory query and check the relevant workload or maintenance behavior. For memory and parallelism settings, allow enough observation to judge the effect; one quick query proves little. Record the previous value so rollback is concrete. If a setting does not behave as expected, restore the earlier approved state through the same controlled path and document what was learned.
I keep the process plain: define desired state, review it, apply it, verify it and watch for drift. The script is only one piece. Ownership, environment context and evidence make it reliable. When another DBA asks why an instance differs, the answer should be in the record rather than in somebody’s memory. That is the real payoff of treating server configuration as code.
A baseline also needs a change cadence. Review it after upgrades, hardware changes and major workload shifts. A setting chosen for an earlier machine can become inappropriate when memory or CPU topology changes. I keep the reason beside the value so the next review can test the original assumption. If the assumption no longer holds, update the approved baseline and apply the revision through the same process.
Do not let drift detection become a stream of unowned messages. Route differences to a team that can identify intentional exceptions, investigate unplanned changes and close the record. A controlled configuration process has a beginning and an end: proposed value, review, deployment, verification and later reassessment. Source control preserves the history, but people still have to make the judgment.
Related reading on this blog: Collecting Server Facts Into One Table Every Night and SQL SERVER 2019: New Values in Sys.Configurations.
Configuration as code is not copying settings everywhere, it is recording and applying deliberate choices the same way.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
