SQL Server: Policy based Management

Policy-Based Management (PBM) in SQL Server is a feature that allows administrators to define and enforce policies for SQL Server instances to ensure consistent configuration, security, and compliance across the server environment.

Here’s a breakdown of how PBM works and its components:

Key Components:

1. Policies:
A policy defines the rules or standards you want to enforce. It is based on a condition that specifies how the policy should be applied.

2. Conditions:

Conditions are the criteria used to evaluate whether a policy is being met. They use a set of expressions or logic to determine compliance. For example, a condition might check if a database has encryption enabled.

3. Facets:

Facets are predefined groups of properties or settings that are used to define the conditions. They categorize different types of settings and properties that can be managed. For example, the "Database" facet covers properties related to databases, such as recovery models and file sizes.

4. Policies Evaluation:

Policies can be evaluated manually or scheduled to run automatically. When a policy is evaluated, it checks the target instances against the defined conditions to determine compliance.

5. Target Sets:

Policies can be applied to one or more SQL Server instances, databases, or objects. You can create target sets to group the instances or objects where a policy should be enforced.

Common Uses:

1. Configuration Management:
Ensure that SQL Server instances are configured consistently according to organizational standards, such as setting specific database options or ensuring proper file growth settings.

2. Compliance:

Enforce security and compliance standards, such as requiring that certain security features like encryption or auditing are enabled.

3. Best Practices:

Implement best practices, such as making sure that backups are being taken regularly or that databases have appropriate recovery models configured.

How to Implement PBM?

1. Create Conditions:

Define the rules for your policy using the available facets.

2. Create Policies:
Combine conditions with appropriate targets to form policies. 

3. Evaluate Policies:
Run evaluations to check if the policies are being met. This can be done on-demand or scheduled.

4. Enforce Policies:

Some policies can be set to automatically remediate issues when they are found, while others might require manual intervention.

Example Scenario: Ensuring All Databases Use Full Recovery Model

1. Create a Condition:
A condition defines the criteria that the policy will enforce. In this case, we want to check if the recovery model of a database is set to "Full."

1. Open SQL Server Management Studio (SSMS).

2. Connect to your SQL Server instance.

3. Navigate to Management  > Policy Management > Conditions.

4. Right-click Conditions and select "New Condition".

   - Name: "Database Recovery Model Full"

   - Facet: "Database"

   - Expression: Select "RecoveryModel" and set it to "Full".

T-SQL: 

USE master;

GO

EXEC msdb.dbo.sp_add_policy_condition

       @name = N'Database Recovery Model Full',

       @facet = N'Database',

       @expression = N'([RecoveryModel] = 1)';

This example uses the "RecoveryModel" property, where "1" corresponds to the Full Recovery Model.

2. Create a Policy:

Once the condition is created, you need to create a policy that uses this condition.

1. Navigate to Management > Policy Management > Policies.

2. Right-click Policies and select "New Policy".

   - Name: "Ensure Full Recovery Model"

   - Condition : Select the condition you created earlier ("Database Recovery Model Full").

   - Evaluation Mode: Choose how often the policy should be evaluated. Options include "On Change - Prevent", "On Change - Log", "On Schedule", etc. For this example, select "On Schedule" and set a schedule if desired.

   - Target: Set the target to apply this policy to the relevant databases. You can choose to apply it to all databases or specify particular ones.

T-SQL:

USE msdb

GO

EXEC msdb.dbo.sp_add_policy

       @name = N'Ensure Full Recovery Model',

       @condition_name = N'Database Recovery Model Full',

       @target = N'All Databases',

       @evaluation_mode = N'On Schedule';

3. Apply and Evaluate the Policy:

1. After creating the policy, you need to evaluate it to see if any databases do not comply with the policy.

   - Go to Management > Policy Management > Policies.

   - Right-click the policy you created ("Ensure Full Recovery Model") and select Evaluate.

SSMS will run the evaluation and show you which databases are compliant and which are not.

2. If you have configured automatic remediation, the policy will attempt to correct any issues it finds. Otherwise, you may need to manually adjust the databases that do not comply.

For manual adjustment, you can use the following T-SQL script to change the recovery model of a database:

USE [YourDatabaseName]
GO

ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;
GO

Conclusion

In this example, you created a condition to check if the recovery model of databases is set to Full, created a policy to enforce this condition, and then evaluated the policy. This ensures that all databases on your SQL Server instance are configured with the Full Recovery Model, helping you maintain consistency and adhere to best practices.

Have a good day!