SQL Server 2008 introduced Policy Based Management feature to ease the managing and monitoring SQL Server instances. The Policy-Based Management feature assists you to monitor and enforce rules for the SQL Server Database Engine. The policies can be applied to one or more SQL Server instances. The Policy based management provides a multi-instance management capability to protect the integrity of the SQL Server environment through the organizations.
Policy Based Management Concepts
The PBM includes these key concepts:
- Facets. A pre-defined set of logical properties of SQL Server configuration. The following query shows all available facets:
SELECT name FROM msdb.dbo.syspolicy_management_facets
- Conditions. A Boolean expression that is evaluated against facet properties.
- Policies. A standard you define against one or more facets through a condition.
- Evaluation modes. Dictates how a policy will be evaluated against one or more targets. The facet determines evaluation mode within the condition. SQL Server supports these four evaluation modes:
- On Demand: a manual evaluation by DBA.
- On Schedule: the policy will be evaluated at the defined schedule.
- On Change–Log Only: the result of the policy violation is captured in the SQL Server log.
- On Change–Prevent: it prevents out-of-compliant changes that violates the policy from taking place.
- Targets. A place which a policy is enforced. This could either be a database or a table or entire SQL instance.
- Categories. To group the policies against specified target.
SQL Server Policy Based Management Best practices
Microsoft provides pre-defined policies based on best standards with SQL Server installation. By default, these policies are present in the following path:C:Program Files (x86)Microsoft SQL Server140ToolsPoliciesDatabaseEngine1033
Import Policy Based Management Best Practices
To use these pre-defined policies inside SSMS, follow these steps
- Navigate to the Management node in Object Explorer and expand it.
- Expand Policy Management node.
- Right-click Policies, and select Import Policy…
- Click on (…) and navigate to the files path as mentioned before. You can choose all files or specific file per your requirement.
- You can change the policy state to enable or disable or preserve the policy state on import
- Once you click on OK policy/policies will be present under Policies node.
Above policies are in common for all SQL Server environments. Sometimes your company has its own policies that need to implement and monitor. For example, based on company policies, no login should start with number, but you know that you are able to create logins which start with numbers. So, let’s start with the policy ensures that no logins start with numbers.
Create a New Condition
In the first step, we need to create a new condition to meet our requirement. In SSMS, expand Policy Management under Management and right-click Conditions, click New Condition.Choose a proper name for the condition. We call it “LoginNameCheck”. Select the facet “Login Options”.[info]Bear in mind that if you choose “Logins” for facet, “Logins” facet does not support “On change” evaluation modes. As result, you only have “On Demand” and “On Schedule” evaluation modes. [info]In expression grid we need to define our logical expression. We use @Name as field with “NOT LIKE” operator and value ‘[0-9]%’. This means there should be no login with number in the start of its name.
Create a new Policy
Next, we need to create a new policy. In SSMS, expand Policy Management under Management and right-click Policies, click New Policy. We name it “LoginNamePolicy”. In “Check condition” dropdown, select the condition “LoginNameCheck”, which we created in previous step.Our goal is to enforce this policy against every login. So, we leave the “Against targets” to “Every login”. Though, you can change the “Every” to “New condition” to create your own condition to exclude logins that you want to exempt from this policy.In “Evaluation Mode” dropdown box, we change it to “On change: prevent”, to prevent an action of creating a login that violate our policy. Just make sure you tick the enabled button. Otherwise, the policy will be created in disabled mode and does not prevent any violation.We leave the “Server restriction” to stay “None”. In description tab you can add additional help to make it clearer for users.To check whether our policy is working or not, we are going to create a login starts with a number. And we encounter this error:
Evaluate a policy
Now we have a policy ready to be evaluated against the target. For the above example, we can trace what violations happened and also if users might create logins that violate the policy prior to creating the policy. Those policies that are “On-Demand” as the evaluation mode, the only way the policy can be checked is by manually evaluating it. It is done by right-clicking the mentioned policy and clicking “Evaluate”.As I created a login which is started with number (here is the login name as ‘123’) before creating the policy, the evaluation returns a failure message for the check against this login.To see the evaluation history, go back to the policy, right-click policies and select “View History”. failure icon in the evaluation history demonstrate the violations against this policy. To see what all the violations were, Click the hyperlink in the Details column to see the “Results Detailed View” window. Here, condition failed in the case of “123” login i.e. the login’s name start with a number. The ‘Expect Value’ and ‘Actual Value’ columns show what was expected and what was actually the case.
Export and Import a Policy
Sometimes you need to implement the same policies in multiple servers. SQL Server allows you to export a policy to XML and import it to the other server. In our case, we will export the policy that we created in previous step to an XML file and then import it into another server.
To export a policy, right-click on a policy you want to be exported and select “Export Policy”.Navigate to the location that you want to save the policy as XML file and click Save to complete this step.
To import a policy, login to the target SQL Server instance. Right-click the Policies under Policy Management and select Import Policy (same as what we have done to import Microsoft best practices). On the import window, click the browse button (…) to browse to the location where the policy is present and click Open.You can choose to overwrite the existing policy with the one being imported. Select the appropriate Policy state and click OK. The policy will be under Policies folder.So far we learnt how the Policy-Based Management is a great feature to enforce standards across SQL Server infrastructure. 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.