SQL Server: Understanding, Implementing and Managing Policy-Based Management
1. Introduction
Policy-Based Management was introduced with the release of SQL Server 2008 and has remained one of the favorite features of DBAs in SQL Server ever since. It makes a DBA’s life so much easier by allowing policies enforcement across the SQL Server environment in just a few clicks using the SQL Server Management Studio GUI. In this article, we will start off with what Policy-based Management is, what it can be used for, and its components that make up the feature. We will then implement Policy-Based Management on an SQL instance to demonstrate how a policy is configured. We will then look at how policy is exported and imported and finally, conclude with a summary of the feature and things covered in the article.
**Note: ** For the sake of simplicity, we will refer to this feature by its acronym PBM henceforth.
2. What is Policy-Based Management?
PBM is a multi-instance management feature that allows a SQL Server professional to define, implement, and evaluate policies across one or more SQL Server instances. With the hardware becoming cheaper and an increase in the demand, businesses are trying to build more servers and expand. With more servers comes more management and maintenance responsibilities thereby causing more work for the Database Professionals. With more management work, a DBA needs a way to automate some of their work. Of course, you can create SQL agent jobs to automate most of the day-to-day activities such as backups, refreshes, etc. However, ensuring that the integrity of the SQL Server environment is not violated requires special attention. Protecting integrity requires the enforcement of standards. A standard is a rule that you, as a DBA, set across the SQL environment so that no action that violates the rule is allowed. An example of such a rule is the MAXDOP setting. Say you have a bunch of OLTP servers where you want to keep the MAXDOP setting to 8. Without PBM, you wouldn’t easily know if someone on the team changes the setting back to the default 0 unless you have some auditing in place. With PBM, you can create a policy that you can evaluate against the target servers to ensure that the setting is not changed.
Implementing PBM ensures that things do not go out of compliance. Imagine there is a hard requirement that all table names have a prefix “tbl_”. Now if there is no PBM in place, the naming convention could potentially be violated and it may go unnoticed whereas with PBM enabled and a policy to check for all table names in place, there won’t be any violation since the policy will either prevent any nonconforming action from happening or allow it to go through but log the violation in the SQL logs so a DBA could check the violations later. Either way, PBM lets you monitor and makes you aware of the policy violations that occur across the SQL Server infrastructure.
Note: Since PBM was introduced in SQL Server 2008, it cannot be configured on older versions of SQL Server. However, you can create a policy on the newer versions and run the policy evaluations against the older SQL versions. Bear in mind that not all policies can be evaluated against the older SQL versions since some policies are feature-specific and if the feature that the policy is created on doesn’t exist in an older SQL version then the policy evaluation against that feature on that old instance will fail.
3. Why Policy-Based Management?
The primary intent of Policy-Based Management is to provide the DBAs a multi-instance management capability and to allow them to automate management across several SQL Servers while ensuring they do not violate their pre-defined standards. The main benefits of PBM include reduced cost of maintaining multiple SQL instances, enforcing pre-defined policies across target servers in just a few clicks using SSMS, it can capture nonconforming system configuration changes or policy violations and ensures compliance across the SQL Server infrastructure.
PBM allows you to set a standard across the target SQL instances and protects the integrity of the SQL Server environment. PBM helps organizations adhere to the audit and compliance requirements such as HIPPA, PCI, and Sarbanes-Oxley. You can create policies that follow the audit requirements and be compliant. You can test the policies against the target servers and see how many of them have violated the policies. As mentioned above, you can either choose to prevent an action that would cause a policy violation or let it execute but capture the violation so a DBA can review it later. You will see later in the article about how policy is created and how you can implement it on targets and evaluate.
4. Components of Policy-Based Management
Now that we understand PBM, let’s discuss the components that make up the PBM framework. They include Facets, Conditions, Policies, Targets, Categories, and Evaluation modes. It is important that we thoroughly understand these key concepts before we learn about how a policy is enforced against a target. Let’s discuss them one-by-one.
1. Facets
A facet is nothing but a collection or logical grouping of properties of SQL Server configuration settings. A facet is tied to a condition to form a policy or, conversely, a policy comprises a condition, which is in turn tied to one or more facet properties. We can then apply the policy to SQL servers (targets) that are in the scope of the management. Some facets include AlwaysOn capabilities such as Availability Group, Availability Database, Database File, Surface Area Configuration, Database Security, Database performance, etc. A facet is a group of properties that are related to a target. As an example, a facet called “Database” has properties such as ReadOnly, Recovery Model, PageVerify, etc. A “Data File” facet has properties such as IsOffline, IsReadOnly, FileName, etc.
To get the list of all facets available in SQL Server, connect to the SQL instance you want to create the policy on —> Expand the Management folder in object explorer—>Expand Policy Management—>Expand the Facets folder. A partial screenshot is shown below. You can also check what facets are available for a database by right-clicking a database and selecting Facets.
Alternatively, the following query retrieves all facets available in SQL Server that we can create a policy against. SQL Server 2016 and 2017 provide 96 facets.
SELECT name FROM msdb.dbo.syspolicy_management_facets
2. Conditions
A condition is a Boolean value that is evaluated against one or more facet properties and determines whether it matches the policy requirement or not. It is to be noted that while a condition can access multiple properties of a single facet, it can only be built on a single facet. In other words, you cannot build one condition and have it access to multiple facets’ properties. However, each condition can access multiple properties of a single facet. So, you can create a condition upon a facet and it will have access to all properties of that facet.
Conditions include the following operators: LIKE, NOT LIKE, IN, NOT IN, =, !=. Not all operators are available for every property of a facet. For example, in the case of facet "Data File”, only two operators = and != are available against the property “IsReadOnly”. It is understandable because IsReadOnly can evaluate to ‘Yes’ or ‘No’ value and requires none other operators such as LIKE, etc. On the same facet “Data File”, if you select the facet property “FileName” instead, then the other operators such as IN, NOT IN, LIKE, NOT LIKE become available to the condition to choose from. The settings in a condition are based on the types of facet properties.
3. Policies
A policy is a standard or rule you define against one more facet properties through a condition. An example of a policy is enforcing data files naming convention, transaction log file placement, enforcing recovery model standard, etc. Note that a policy can contain only one condition. A policy contains evaluation modes which dictate how you like the policy to be evaluated against one or more targets. We discuss evaluation modes in the next section.
4. Evaluation modes
Evaluation modes are determined by the facet that is used within the condition. Not all evaluation modes are available for all facets. An evaluation mode dictates how a policy is evaluated against one or more targets. The policies in SQL Server supports the following four evaluation modes.
- On Demand: As the name suggests, an on-demand evaluation mode requires manual intervention I.e. a DBA has to evaluate the policy manually. This is done by right-clicking the policy in the SSMS object explorer under the Policy-Based Management folder and selecting Evaluate. The policy will be evaluated against all targets specified in the policy.
- On Schedule: Opposite of the on-demand mode. This causes the policy to be evaluated at the defined schedule.
- On Change Log Only: Under this mode, a nonconforming action may take place, but the result of the policy violation is captured in the SQL Server logs. This is to let the DBA review the logs later. It doesn’t prevent out-of-compliance changes from taking place.
- On Change Prevent: This evaluation mode prevents a nonconforming change or action from taking place. When this mode is selected, any change that violates the policy will be terminated and rolled back and an error is thrown showing that a policy has been violated.
5. Targets
A target is a place upon which a policy is enforced. After you create a condition acting on appropriate facet properties, you are ready to apply the policy against something. This something could either be a database, an entire SQL instance or a table etc. all these are nothing but targets. A good thing about PBM is it allows you to use conditions to limit the targets. As an example, if you create a policy that no database should have the word ”DBA” in their names, but you know there is a database called “Maintenance_DBA” on every server that houses all index and other DBA maintenance objects. Here, you can create a condition to skip the policy check against databases that contain the word “maintenance”. That way, there won’t be any policy violations.
As mentioned earlier, while you cannot create policies on versions older than SQL 2008, you can add them as targets and apply the policies as long as the facets used in the policy are supported on those old versions.
6. Categories
This allows the grouping of policies against specified targets. For example, you can group which policies a database or a SQL instance should be subscribed to and group them into categories. You can then have the targets subscribe to the category.
5. Implementing Policy-Based Management
Now that we have a good understanding of what all ingredients are required to create a policy, let’s create a simple policy using SSMS. Note you can also create policies using T-SQL.
Pro-Tip: Instead of working on finding the best standards for your environment, you can leverage the tried-and-true pre-defined policies that Microsoft provides with every SQL installation. If you have not changed the default installation directory, the policies are present under the 32-bit path — C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Policies\DatabaseEngine\1033. Below is a partial screenshot from my SQL Server 2017 installation directory. Later in the article, we will see how we can export and import a policy.
1. Creating a new policy
Let's continue with the example policy we briefly discussed under the "Targets" section above. The policy that we are about to create on the target SQL instance will ensure that:
- No database has the word "DBA" in its name
- SA is the owner of every database
- Any database with the word "Maintenance" in its name be skipped from the policy check. This is to allow databases such as "Maintenance_DBA" that are DBAs owned and which store maintenance scripts.
Once the policy is created, we will evaluate it in the very next section. Also, note that this policy allows only two execution modes - On Demand or On Schedule. This is due to the fact that execution modes are driven by the type of Facet and in our case, the facet "Database" only allows those two modes.
Remember, a policy can only be created on one condition but that condition can span multiple properties of a single facet.
As the very first step, let's create a new condition that meets the above two requirements. After connecting to the SQL instance in SSMS, expand Policy Management under Management and right-click Conditions --> Click New Condition.
Give an appropriate name to the condition. We will call it "DatabaseNameCheck". Select the facet "Database" in the Facet dropdown. Notice that we are using two expressions against two properties with "OR" relation between them. The first expression uses the filed @Name with operator NOT LIKE and value %DBA%. This means there should be no database with 'DBA' in the name or there would be a policy violation.
The second expression uses the value @Owner ensuring that the database owner is always the "SA" login.
Next, we create a new policy by right-clicking "Policies" and selecting "New Policy". In the "Check condition" dropdown, select the condition "DatabaseNameCheck" that we create in the previous step.
Remember, our goal is to enforce policy on the SQL instance while ensuring the databases with the word "Maintenance" in their names are exempted. To do this, we will create a new condition, this time, by clicking "New condition" in the "Against targets" tab of the new policy wizard.
Once the create condition window opens, give a name to it. We will name it "ExcludeMaintenanceDB". Under Expression section, select the field @Name with the operator "NOT LIKE" and value %Maintenance%. This condition will skip enforcing policy on databases that have "Maintenance" in their names even when they have DBA in their names. Hit OK and it will take you back to the create policy window.
Now that we have exception defined, under Against targets section, select the condition we just created i.e. ExcludeMaintenanceDB. Thus, the target for this policy would be all databases except the ones that have "Maintenance" in their names.
Notice only two evaluation modes are available to choose from. This is because the facet "Database" only allows On Demand and On Schedule modes. We will select On-Demand and we will go with 'none' under server restriction. Hit OK to create the new policy.
2. Evaluating a policy
Before proceeding to run the policy check against the target SQL instance, let us create two databases to create a scenario the policy can be tested against. We will create two databases called "DBA_STAR" and "Maintenance_DBA" using the simple T-SQL below with DB owners "Mohsin" and "SA" respectively.
CREATE DATABASE DBA_STAR CREATE DATABASE Maintenance_DBA
The expected evaluation result is that the Maintenance_DBA should be skipped from the check while DBA_STAR fails both conditions i.e. Database Name check and non-SA owner check, since it has DBA in the name as well as the owner is not the SA login.
Alright, we now have a policy ready to be evaluated against the target SQL Server. Since we selected "On-Demand" as the evaluation mode, the only way the policy can be checked is by manually evaluating it. That is done by right-clicking the said policy and clicking "Evaluate".
Notice the policy is disabled (indicated by the red 'x' icon), however, a policy can be evaluated even if it's disabled. Also, the evaluation type on-demand doesn't support enabling a policy. Attempting to enable a policy that runs only on-demand results in the following error.
Alright, back to policy evaluation, click Evaluate.
You will see that the evaluation skipped the "Maintenance_DBA" from being checked, but ran against all remaining databases. However, it returned a failure message for the check against DBA_STAR. This is expected.
Let's go back to the policy, right-click it and select 'View History'. It will display the evaluation history.
Notice the failure icon in the evaluation history. Click the hyperlink in the Details column and it will take us to the "Results Detailed View" window where we can see what all the violations were. As expected, both conditions failed in the case of "DBA_STAR" database i.e. the name had "DBA" and also the owner was a non-SA login. See the 'Expect Value' and 'Actual Value' columns for what was expected and what is actually the case.
6. Exporting and Importing a Policy
SQL Server allows exporting a policy to XML so that it can be ported to other servers that need the same standard set. In this section, we will see how a policy is exported to an XML file and then imported into another server. The screenshots are pretty much self-explanatory.
1. Export
Right-click the policy to be exported and select "Export Policy". In our case, we will select the policy we created i.e. DatabaseCheck.
Browse to the location you want to save the policy as an XML file and hit Save to complete exporting the policy.
2. Import
To import a policy, login to the target SQL instance you want to import the policy to. Right-click the "Policies" folder under Policy Management option and select "Import Policy".
On the import window, click the ellipses button to browse to the location where the policy (to be imported) is present and hit Open.
You can choose to overwrite the existing policy if it has the same name as the one being imported. Select the appropriate Policy state and hit OK. The policy will then show up under Policies folder.
7. Conclusion
Policy-Based Management is a great way to enforce standards across SQL Server infrastructure and to ensure developers/DBAs adhere to the defined policies. Depending on the facet, you can choose between one and four evaluation modes. Each SQL installation comes with several pre-defined policies that you can choose from, instead of having to create your own. Implementation of policies can easily be done via SQL Server Management Studio or T-SQL. You can export a policy created on one instance and port it to another, giving you greater flexibility of managing policies. You can leverage the Central Management Server to evaluate a policy against multiple SQL instances at the same time in one go as opposed to checking it one-by-one. SQL Server 2017 provides 96 facets with several properties with each facet. All in all, PBM has remained one of the great features that a DBA can easily learn and quickly implement in the environment.