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

#012 Introduction to Policy-Based Management

by Luke Campbell
Nov 09, 2024

 

Policy-based management is often an overlooked feature in SQL Server.  It's been around for years.  I've used it to help enforce STIG controls on SQL Servers, which needed to be DOD-compliant.

No DBA wants to build an instance and then guess if it's still in compliance right before audit season.  With PBM, you can be sure it stays compliant once released into the wild.

This week, I'm starting a series of challenges focusing on PMB.

This first challenge has three objectives.

  1. Learn what Policy-Based Management is and how it fits into SQL Server administration.
  2. Discover how PBM simplifies managing multiple SQL Server instances.
  3. Familiarize yourself with the PBM interface within SQL Server Management Studio.

 

You'll need at least two SQL Server 2022 instances (developer edition is fine) joined to a Windows domain.

Ready?


Estimated time to complete: Less than 1 hour.


Step 1:  Exploring PBM Concepts

Objective: Learn what Policy-Based Management is and how it fits into SQL Server administration.

What is Policy-Based Management?

PBM is a system for managing one or more instances of SQL Server.  It provides a framework for DBAs to create policies that manage entity configurations on the instance, ensuring compliance with organizational standards.

Key Components:

  • Facets: Predefined sets of logical properties that model the behavior or characteristics of certain management areas in SQL Server (e.g., Surface Area Configuration, Database Options).
  • Conditions: Expressions that define a state or set of states for a Facet. Conditions are built using the properties exposed by Facets.
  • Policies: The rules that enforce Conditions on Targets.  A policy specifies the Condition to check, the Targets to evaluate, and the evaluation mode.
  • Targets: The SQL Server objects (server, databases, tables, etc.) upon which the Policies are enforced.

Action Item:

Read: Review the Microsoft docs on PBM to get a deeper understanding. 

Administer servers using Policy-Based Management - SQL Server

Learn how to use Policy-Based Management to manage one or more instances of SQL Server.

learn.microsoft.com

Think: How could PBM fit into your current workflow of managing SQL Server instances?  Is your organization held to specific compliance standards such as DOD or SOC 2?  Do you need to ensure developer environments also follow best practices?

Step 2: Benefits of PBM for DBAs

Objective: Discover how PBM simplifies managing multiple SQL Server instances.

Centralized Management:

Manage configurations across all your SQL Server instances from a single point, reducing the overhead of manual checks.  One of my favorite tools to use for SQL Server deployments is Ansible.  Ansible allows you to automate the deployment of each and every instance to ensure a consistent state.  PBM allows you to enforce the state throughout the life of the instance.

Consistency and Compliance:

Ensure all instances adhere to company policies, industry regulations (e.g., DOD, SOC2), or best practices, minimizing configuration drift.

Automated Enforcement:

PBM can automatically enforce policies, correct non-compliant settings, or alert you when issues arise.

Real-World Scenarios:

  • Security Compliance: Enforce password policies or encryption standards.
  • Performance Optimization: Data and log files are on separate logical volumes, or certain features are enabled/disabled.
  • Standardization: Maintain consistent settings for backups, recovery models, or naming conventions.

Action Item:

Reflect:  Identify areas where inconsistent configurations have caused issues in your current environment.

Document: List potential policies that could address these issues.

Step 3: Navigating PBM in SSMS

Objective: Familiarize yourself with the PBM interface within SQL Server Management Studio.

Accessing PBM:

  • Open SSMS and connect to your SQL Server instance (later in this series, we'll create a Central Management Server to centralize PBM).
  • In Object Explorer, expand the Management node.
  • Find and expand the Policy Management folder.

Folder Descriptions:

  • Policies: Contains all the policies you have created or imported. 
  • Conditions: Houses the conditions used by your policies.
  • Facets: Lists all available facets you can use to define conditions.

Exploring Facets:

  • Right-click on a specific facet, such as Database Performance, and then properties to view all available properties for that facet.

Action Item:

Hands-On Exploration: Spend time clicking through the PBM folders to become comfortable with where everything is.

Helping Others and Sharing Your Results

How can PBM address your specific challenges in managing multiple SQL Server instances?

Which facets and conditions seem most applicable to your environment?

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

Next week, we'll create a policy using PBM.

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-...
#014 Introduction to the Enterprise Policy-Based Management Framework
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 o...

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.