Easy way to create policies using SSMS

SSMS has an interesting feature that turns easy the creation of policies to check our servers compliance.

Right-clicking an item inside object explorer window, you will find ‘Facet’ item in the context menu. Clicking on it will open the ‘View Facets’ window.

facet context menu

In the ‘View Facets’ window, you can see all the facets related to the object you right-clicked. For example, the database object has a total of 5 facets.

database facets

Creating a new policy

Let’s use this feature to create a new policy:

  • Click the ‘Facet’ dropdown list in the ‘View Facets’ window and select ‘Database Performance’. You may notice all the properties of the facet ‘Database Performance’ inside ‘Facet Properties’ area.

database facet

  • Click the button ‘Export Current State as Policy’
  • Inside the ‘Export as Policy’ window, type ‘dbPerformancePolicy’ in the ‘Policy Definition Name’ textbox
  • Type ‘dbPerformanceCondition’ in the ‘Condition Name’ textbox

export policies

  • Inside ‘Export as Policy’ window, click ‘Ok’ button
  • In the ‘View Facets’ window, click ‘Ok’ button
  • Inside ‘Object Explorer’ window, expand ‘Management’->‘Policy Management’->‘Policies’ and also ‘Conditions’. You may notice the new policy and condition.

SSMS Policies

  • Double click the ‘dbPerformanceCondition’ object. You may notice several expressions in the condition.
  • Inside ‘Open Condition’ window, select the expressions from the 3rd to the last one and press ‘Delete’ keypolicy conditions
  • Click ‘Ok’ button inside ‘Open Condition’ window

As a result, you have a new policy to check the compliance of your databases.