Policy-Based Management, Central Management Concepts, and Configuration Drift

Learning outcomes

ServiceHub now operates more than one SQL Server instance. A security option is disabled in one environment, a database recovery model differs in another, and an emergency change was never reflected in automation. This is configuration drift: actual state has diverged from approved desired state. SQL Server offers Policy-Based Management (PBM) and Central Management Server (CMS) concepts, but neither replaces source control, change review, identity boundaries, or a modern configuration-management pipeline.

1. Desired state must be explicit before tools can enforce it

A PBM facet exposes a set of manageable properties; a condition is a Boolean expression over a facet; a policy combines one condition with targets, category/restriction, and an evaluation mode. PBM policies live in msdb. The important operational question is not “can PBM express this?” but “who owns this desired state, how is it reviewed, and what happens when the policy would change a production system?”

PBM has four evaluation modes: on demand; on change—prevent; on change—log only; and on schedule. Not every facet supports every mode. On-change prevent relies on DDL triggers and can break if required trigger behavior is disabled. On-schedule evaluation uses SQL Server Agent. SQL Server 2025 Express lacks policy automation, so automated PBM enforcement/scheduling belongs to Standard/Enterprise (or their free Developer counterparts).

USE master;GOIF DB_ID(N'ServiceHubOpsLab') IS NULLBEGIN    CREATE DATABASE ServiceHubOpsLab;END;GOALTER DATABASE ServiceHubOpsLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubOpsLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubOpsLab;GOIF SCHEMA_ID(N'lab21') IS NULL EXEC(N'CREATE SCHEMA lab21 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab21.RunAudit', N'U') IS NULLBEGIN  CREATE TABLE lab21.RunAudit  (    run_id bigint IDENTITY PRIMARY KEY,    run_name sysname NOT NULL,    started_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    finished_at datetime2(0) NULL,    outcome varchar(16) NOT NULL DEFAULT 'STARTED',    detail nvarchar(1000) NULL  );END;GOIF OBJECT_ID(N'lab21.WorkQueue', N'U') IS NULLBEGIN  CREATE TABLE lab21.WorkQueue  (    work_id bigint IDENTITY PRIMARY KEY,    status varchar(16) NOT NULL,    created_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    processed_at datetime2(0) NULL,    payload nvarchar(200) NULL  );  INSERT lab21.WorkQueue(status,payload)  SELECT TOP (5000)    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5 = 0 THEN 'READY' ELSE 'DONE' END,    CONCAT(N'work-',ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))  FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;END;GOUSE ServiceHubOpsLab;GOIF OBJECT_ID(N'lab21.ConfigurationBaseline',N'U') IS NULLBEGIN  CREATE TABLE lab21.ConfigurationBaseline  (    setting_scope varchar(16) NOT NULL,    setting_name sysname NOT NULL,    expected_value nvarchar(128) NOT NULL,    CONSTRAINT PK_ConfigurationBaseline PRIMARY KEY(setting_scope,setting_name)  );END;UPDATE lab21.ConfigurationBaselineSET expected_value=CASE setting_name  WHEN 'compatibility_level' THEN '170'  WHEN 'recovery_model_desc' THEN 'SIMPLE'  WHEN 'page_verify_option_desc' THEN 'CHECKSUM' ENDWHERE setting_scope='DATABASE'  AND setting_name IN ('compatibility_level','recovery_model_desc','page_verify_option_desc');INSERT lab21.ConfigurationBaseline(setting_scope,setting_name,expected_value)SELECT v.setting_scope,v.setting_name,v.expected_valueFROM (VALUES ('DATABASE','compatibility_level','170'), ('DATABASE','recovery_model_desc','SIMPLE'), ('DATABASE','page_verify_option_desc','CHECKSUM')) v(setting_scope,setting_name,expected_value)WHERE NOT EXISTS(SELECT 1 FROM lab21.ConfigurationBaseline b WHERE b.setting_scope=v.setting_scope AND b.setting_name=v.setting_name);GOSELECT setting_name,expected_value,actual_value,       CASE WHEN expected_value=actual_value THEN 'COMPLIANT' ELSE 'DRIFT' END AS stateFROM(  SELECT b.setting_name,b.expected_value,    CASE b.setting_name      WHEN 'compatibility_level' THEN CONVERT(nvarchar(128),d.compatibility_level)      WHEN 'recovery_model_desc' THEN d.recovery_model_desc      WHEN 'page_verify_option_desc' THEN d.page_verify_option_desc    END AS actual_value  FROM lab21.ConfigurationBaseline AS b  CROSS JOIN sys.databases AS d  WHERE b.setting_scope='DATABASE' AND d.name=DB_NAME()) AS x;GO

This table is intentionally simple but demonstrates the contract: desired state is data, actual state is collected, and the diff is explicit. In production, the desired state belongs in source control or another approved policy repository—not only in a GUI on one DBA laptop.

2. Evaluation is not the same as enforcement

An on-demand PBM evaluation generates a compliance report; it does not automatically reconfigure every failing target. Some noncompliant properties can be changed when an administrator chooses Apply. On-change prevent can reject DDL for supported facets, while log-only records noncompliance. Scheduled policies report periodically via Agent. Treat these as different risk levels.

A policy such as “every table must have an index” can be actively harmful: Microsoft warns that enforcing such a simplistic rule can break features such as CDC and transactional replication because internal system tables do not obey that assumption. Governance needs target filters, exclusions and feature awareness.

USE msdb;GOSELECT policy_id,name,is_enabled,evaluation_mode,condition_idFROM dbo.syspolicy_policiesORDER BY name;SELECT condition_id,name,facet,expressionFROM dbo.syspolicy_conditionsORDER BY name;GO-- Empty results simply mean no custom policies/conditions are stored here.

Creating/editing PBM conditions and policies normally requires membership in PolicyAdministratorRole in msdb. Do not grant sysadmin to a configuration-management identity merely because it is convenient.

3. Registered Servers and CMS coordinate connections, not permissions

SSMS Registered Servers stores connection registrations and can organize server groups. A Central Management Server stores groups in a SQL Server instance and allows multi-server queries or policy evaluation against the group. CMS does not create one super-credential. Connections execute in the user’s context, so effective permissions can differ across targets.

That is both useful and dangerous. A multi-server query such as SELECT @@SERVERNAME is a harmless inventory action. A multi-server ALTER SERVER CONFIGURATION can have a massive blast radius. Separate read-only inventory groups from change-capable workflows, review target membership, and require change-control gates for writes.

SELECT  @@SERVERNAME AS server_name,  SERVERPROPERTY('ProductVersion') AS product_version,  SERVERPROPERTY('Edition') AS edition,  SERVERPROPERTY('EngineEdition') AS engine_edition,  SYSUTCDATETIME() AS collected_utc;GO-- In an SSMS CMS group, results include a server-name column for each target.-- Do not convert this example into an estate-wide ALTER statement without-- explicit target review, permissions, rollback, and change approval.

CMS management permissions are stored in msdb roles such as ServerGroupAdministratorRole and ServerGroupReaderRole. CMS itself is not a secrets vault or configuration deployment system.

4. Drift control works best as source-controlled desired state plus evidence

PBM is strongest where SQL Server exposes a stable facet and you need SQL-native evaluation/prevention. Source-controlled T-SQL/PowerShell is often better for complex multi-step configuration, external dependencies, certificates, files, Agent objects, or cross-instance orchestration. A modern estate may combine both: PBM for a small set of invariant guardrails; configuration-as-code for deployable state; CMS/Registered Servers for inventory; and a central observability system for evidence.

INSERT lab21.RunAudit(run_name,finished_at,outcome,detail)SELECT N'ConfigurationDriftCheck',SYSUTCDATETIME(),'SUCCEEDED',       CONCAT(N'db=',DB_NAME(),N'; compat=',compatibility_level,              N'; recovery=',recovery_model_desc,              N'; page_verify=',page_verify_option_desc)FROM sys.databases WHERE name=DB_NAME();GOSELECT TOP (5) * FROM lab21.RunAudit ORDER BY run_id DESC;GO

The evidence row can be exported to your operations telemetry. Remediation should be a separate action with its own approval and postcondition, especially for settings with restart, availability, performance or durability effects.

5. Production judgment

Define a configuration owner and source of truth. For every rule, record scope, supported versions/editions, detection query, remediation, restart requirement, exception process and rollback. Do not assume that a PBM green state covers application configuration, OS/kernel settings, storage layout, certificates, SQL Agent service identity, firewall rules, drivers or HA topology. Those lie outside individual facets or outside SQL Server entirely.

The final lesson turns this governance model into executable runbooks: preconditions, scoped concurrency control, idempotent actions, structured evidence, postconditions and safe reruns from either Agent or an external scheduler.

Authoritative references

• Administer servers by using Policy-Based Management
• Create a Policy-Based Management policy
• Working with Policy-Based Management facets
• Registered Servers in SSMS
• Create a Central Management Server
• SQL Server 2025 editions and supported features