SQLOpsDB Architecture

Over the years I have tried to minimize number of moving parts in the architecture. The overall architecture can be broken down into three primary components.

1. SQL Server Instance
   • Central Management Server (CMS) – Provides list of target servers.
   • SQLOpsDB – Central database to record all the policy evaluation results and SQLOpsDB data collection. I have integrated the Enterprise Policy Management Framework database and objects required into single database.
   • SQL Server Policies – Microsoft Best Practices for SQL Server configuration.
2. Report Server
3. PowerShell & Jobs
   • SQLOpsDB Powershell Module – Custom model containing all the command-lets for the solution. Command lets are generally broken into two categories (SI – SQL Infrastructure Call or SQLOp – SQLOpsDB Database call).
   • SQLOpsDB_DataCollection – The primary data collector driver, which leverages SQLOpsDB module to retrieve information from target servers and write back to SQLOpsDB.
   • EPMExecution – This is a wrapper script that calls the EPM_EnterpriseEvaluation_5 script. The script looks at the groups to be monitored as configured on SQLOpsDB and passes each group name to EPM for policy evaluation.
   • EPM_EnterpriseEvaluation_5 – This script is part of the Enterprise Policy Management Framework (link). The original script has been modified for SQLOpsDB.

Last requirement is Extended Properties on Target Servers.

All three primary components can be deployed on separate servers, however, for easy of management current deployment script assumes all three are on a single server deployment server.

The lines represent data movement.