0

Ansible for SQL Server DBAs. Available Now!

Header Logo
Blog DBA Challenges
Courses
Automated Sandbox Framework Ansible for SQL Server DBAs: Level 1
Log In
← Back to all posts
Connect
Share to…
Share

#014 Introduction to the Enterprise Policy-Based Management Framework

by Luke Campbell
Dec 14, 2024

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

Responses

Join the conversation
t("newsletters.loading")
Loading...
#016 Leveraging Ansible Variables to Configure Lock Pages in Memory
You've probably seen it happen.  During peak load on a SQL Server instance, the operating system occasionally pages out parts of SQL Server's memory to disk, causing performance slowdowns.  And you've probably read how Lock Pages in Memory can help address this issue.   Granting this user right can be time-consuming and error-prone when done manually especially if you need to grant the permissi...
#015 Introduction to the Automated Sandbox Framework
2025 is just around the corner, and it's shaping up to be an exciting year for DBAs.  With Windows Server 2025 already here and SQL Server 2025 on the horizon, there's no better time to sharpen your skills and prepare for what's ahead. This year, I've focused on solving a common challenge for DBAs: how to quickly and consistently build test environments for learning and experimentation. In mid-...
#013 Creating and Implementing Your First Policy
Usually, right after building a SQL Server instance, it's in pretty good shape.  You've configured settings like maxdop, cost threshold for parallelism, max memory, etc. You've likely set up your standard maintenance jobs and set up users and roles appropriately.   But let's say it's a year or two later, and you've been disengaged from that instance.  Are the settings you applied still intact? ...

DBA Challenges

Helping you become a more efficient SQL Server Database Administrator through real-world challenges and automation.
Footer Logo
Policies Contact
© 2025 AUTOMATESQL LLC

Five Steps to Spot Automation Opportunities

Wondering if that task you're working on should be automated?  With your free Time Tracker Excel workbook, you're able to easily track and analyze how you spend your time throughout the day.

 

When you signup, we'll send you periodic emails with additional free content. Unsubscribe anytime.