It's been a few weeks since the last edition.  Life happens--between releasing Ansible for SQL Server DBAs, celebrating Thanksgiving, preparing for Christmas, and juggling the day-to-day demands of being a full-time consultant, it's easy to feel stretched thin. 

Have you ever felt that way?

But here's a question:  Are we just staying busy or moving toward our goals (we like to think so)?  Are our daily tasks setting us up for long-term success or just stealing our focus? 

I recently heard a great quote from Rory Vaden:

"Automation is to your time exactly what compounding interest is to your money."

As DBAs, we're fortunate that a lot of our tasks can be automated so we can focus more on strategic tasks to help move closer to our goals and increase efficiency.

Over the past few editions, we reviewed a hidden gem in SQL Server - Policy-Based Management.  PBM is one way to reduce those interruptions that steal our time.  By implementing PBM or a similar tool, we can avoid scrambling when the auditors show up or when asked if a specific database has been configured to our desired state (is database X using the full recovery model?).

Today, we'll review the open-sourced Enterprise Policy-Based Management Framework (specifically version 5.0).

What Is the Enterprise Policy Management Framework?

EPM is a solution that builds upon SQL Server's native Policy-Based Management feature, extending its capabilities to make it more scalable for enterprise use.  It's open-source and available on GitHub.  EPM provides a centralized way to define, deploy, and enforce policies across multiple SQL Server instances. 

The documentation is a bit dated, but it works with older versions of SQL Server and the latest (2022).

Key Components of EPM

The framework consists of several key components that enable it to scale SQL Server's native Policy-Based Management features.  Here's a breakdown:

• Policies
  
  • Rules or conditions define how SQL Server instances should be configured or behave.
  • Examples: 
    • Enforce that databases must have a specific recovery model.
    • Ensure that databases do not have AutoShrink enabled.
• Conditions
  
  • Logical expressions that evaluate the state of a SQL Server instance or its objects.
  • Examples: 
    • A condition to check if all databases are in full recovery mode.  A condition ensuring xp_cmdshell is disabled.
    • Conditions are used within policies to specify the criteria that must be met.
• Categories
  • Logical groupings of policies to organize them by purpose or scope.
  • Examples: A category for Microsoft Best Practices: Performance.
• Policy Targets
  • The scope of objects the policies apply to, such as servers, databases, or tables.
  • Examples: 
    • Targeting all databases on an instance. 
    • Targeting specific SQL Server instances in a production environment.
• Central Management Server (CMS)
  • A SQL Server instance configured to act as the central hub for managing multiple servers.
  • Role in EPM: 
    • Distributes and evaluates policies across multiple SQL Server instances. 
    • Collects compliance results for centralized reporting.
• Predefined Policies and Templates
  • The framework provides a set of curated policies to enforce SQL Server best practices.  These can be imported and applied as-is or customized for specific needs.
  • Examples:
    • Policies for configuring tempdb optimally.
• Automation Scripts
  
  • Scripts provided by the EPM Framework to automate tasks like:
    • Evaluating policies across multiple instances.
    • Generating compliance reports.
  • These are written in PowerShell and can be scheduled via the SQL Server Agent.
• Centralized Policy Management Database
  
  • Acts as a single source of truth for policies, conditions, and evaluation results.
  • Centralizes management to avoid duplicating policies across multiple servers.
  • Provides historical data for auditing and compliance reporting.
• Reporting and Compliance
  • Tools and scripts to generate reports showing the compliance status of SQL Server instances.

What Problem Does it Solve?

DBAs often struggle with the following:

• Maintaining consistency across environments.
• Ensuring compliance with internal or external standards.
• Addressing configuration drift caused by manual changes.
• Simplifying audits and reporting processes.

This framework simplifies these challenges by:

• Centralizing policy management.
• Automating policy deployment and evaluation.
• Providing pre-built templates for common SQL Server standards and best practices.
• Integrating with automation tools to keep your environments in check with minimal effort.

Where can I get it?

It's available on GitHub in the sql-server-samples repo.  I've found that the easiest way to get started is by using SSMS 21 Preview 2 and the built-in git integration.  This will download all samples in the repo, not just EPM.

1. Open SSMS 21.
2. Click Git, then Clone Repository.

3. Enter the URL: https://github.com/microsoft/sql-server-samples.git.

5. Once cloned, open the path in File Explorer and navigate to samples\features\epm-framework\5.0.  Here, you'll find the EPM Configuration Documentation v5.docx file.  Open this file to get started.

In SSMS 21, you can review all files and examples available in this repository.  Open the Solutions Explorer - folder view.  Look around, as numerous examples exist (outside the scope of EPM).

Additional Tools

To deploy the SSRS reports, you'll need Visual Studio (I used VS 2022) and the Microsoft Reporting Services Projects 2022 extensions.

Sandbox Environment

I'd recommend getting familiar with EPM in a sandbox environment first. 

Multiple policies can be imported to the instance serving as the Central Management Server, but some may not apply to your environment.  Or you may need to make adjustments before deploying it to your production environment.

My environment consists of:

• 2 SQL Server 2022 instances.
  • SQL1 - Central Management Server.  SSRS is also installed here.
  • SQL2 - SQL Server 2022 instance.  
• If you deploy the example policies, remember to deploy these to the CMS instance.
• One server group - Sandbox

Helping Others and Sharing Your Results

Using frameworks like EPM can help DBAs be more efficient and reduce the toil of manually performing these types of reviews. 

What challenges are you aiming to overcome in 2025?  Can EPM or automation in general help?

If you have tips or experiences to share, please post them in the comments or tag me on LinkedIn with the #dbachallenges hashtag.  

Feedback

If there's a DBA Challenge you'd like to see, let me know by replying to this email.

Feel free to reply to this email with any thoughts or questions.

Good luck, and I look forward to seeing your results!

Luke