Mistake #41: treating configuration management as unrelated to SQL Server. A scripted build creates consistency only at deployment; later changes can produce configuration drift and live security vulnerabilities. section 8.9 section 8.11
The xp_cmdshell drift scenario
A SQL Server instance is built to CIS level 1, but a junior DBA enables xp_cmdshell several days later. Because xp_cmdshell permits operating-system commands from SQL Server, the instance leaves its desired state and exposes a security vulnerability. Periodic evaluation can detect and automatically correct the drift; a 30-minute interval limits the exposure to between zero and thirty minutes. section 8.9
Configuration management applies the desired configuration when a server is built, evaluates it periodically, and fixes differences automatically. A resource focuses on one configuration unit, such as a registry key, folder, or software installation: its test method checks the current state and its set method corrects it. section 8.9
Use configuration management to:
- Reduce administrative overhead and maintain a compliant SQL Server estate throughout the application lifecycle. section 8.11
- Choose only required SQL Server features, reducing resource consumption and the security footprint. section 8.11
- Automate installation and ongoing configuration with PowerShell DSC, Puppet, or Ansible. section 8.11 section 8.9
Figure 8.3 Configuration management process
The process figure helps visualize the cycle from applying the desired state through periodic evaluation and automatic correction. section 8.9
