CHAPTER 23
Policy-Based Management
Policy-Based Management (PBM) is a system DBAs can use to report on or enforce
standards across the enterprise, when used with a central management server. This
chapter first introduces you to the concepts used by PBM and then demonstrates how to
use PBM to effectively manage an estate through the GUI and with PowerShell.
PBM Concepts
Policy-Based Management uses the concepts of targets, facets, conditions, and policies.
Targets are entities PBM manages, such as databases or tables. Facets are collections of
properties that relate to a target. For example, the database facet includes a property
relating to the name of the database. Conditions are Boolean expressions that can be
evaluated against a property. Policies bind conditions to targets. The following sections
discuss each of these concepts.
Facets
A facet is a collection of properties that relate to a type of target, such as View, which
has properties including IsSchemaBound, HasIndex, and HasAfterTrigger; Database
Role, which has properties including Name Owner and IsFixedRole; and Index, which
has properties including IsClustered, IsPartitioned, and IsUnique. The Index facet
also exposes properties relating to geospatial indexes, memory-optimized indexes, XML
indexes, and full-text indexes. Other notable facets include Database, StoredProcedure,
SurfaceAreaConfiguration, LinkedServer, and Audit. SQL Server 2019 provides 96
facets in all, and you can find a complete list within the “Evaluation Modes” section of
this chapter. You can also access a list of facets by running the command in Listing 23-1.

Listing 23-1.  Finding a List of Facets
SELECT name
FROM msdb.dbo.syspolicy_management_facets ;
Conditions
A condition is a Boolean expression that is evaluated against an object property to
determine whether or not it matches your requirement. Each facet contains multiple
properties that you can create conditions against, but each condition can only access
properties from a single facet. Conditions can be evaluated against the following
operators:
•
=
•
!=
•
LIKE
•
NOT LIKE
•
IN
•
NOT IN
For example, you can use the LIKE operator to ensure that all database names begin
with Chapter by using the following expression Database.Name LIKE 'Chapter%'.
Targets
A target is an entity to which a policy can be applied. This can be a table, a database,
an entire instance, or most other objects within SQL Server. When adding targets to a
policy, you can use conditions to limit the number of targets. This means, for example, if
you create a policy to enforce database naming conventions on an instance, you can use
a condition to avoid checking the policy against database names that contain the words
“SharePoint,” “bdc,” or “wss,” since these are your SharePoint databases and they may
contain GUIDs that may be disallowed under your standard naming conventions.

Policies
A policy contains one condition and binds it to one or more targets (targets may also be
filtered by separate conditions) and an evaluation mode. Depending on the evaluation
mode you select, the policy may also contain a schedule on which you would like the
policy to be checked. Policies support four evaluation modes, which are discussed in the
following section.
Evaluation Modes
Policies support between one and four evaluation modes, depending on which facet you
use within the condition. The following are the evaluation modes:
•
On Demand
•
On Schedule
•
On Change: Log Only
•
On Change: Prevent
If the evaluation mode is configured as On Demand, then the policies are only
evaluated when you (the DBA) manually evaluate them. If the evaluation mode is
configured as On Schedule, then you create a schedule when you create the policy; this
causes the policy to be evaluated periodically.
Tip  A policy can be evaluated On Demand even if it has been configured with a
different evaluation mode.
If you select the On Change: Log Only evaluation mode, then whenever the relevant
property of a target changes, the result of the policy validation is logged to the SQL
Server log. In the event that the policy is fired but not validated, a message is generated
in the log. This occurs when a target has been configured in such a way that one of your
policies is violated. If the policy is violated, then Error 34053 is thrown with a severity
level of 16 (meaning the problem can be fixed by the user).
Tip  When you create an object, this causes the properties to be evaluated in the
same way that they are when an existing object’s properties are altered.

If you choose On Change: Prevent as the evaluation mode, then when a property is
changed, SQL Server evaluates the property, and if there is a violation, an error message
is thrown and the statement that caused the policy violation is rolled back.
Because policies work based on DDL events being fired, depending on the properties
within the facet, not all evaluation modes can be implemented for all facets. The rules
for working out the evaluation modes supported by a specific facet are rather opaque, so
you can discover them by running the query in Listing 23-2.
Listing 23-2.  Listing Supported Execution Types per Facet
SELECT
       name ,
           'Yes' AS on_demand,
       CASE
           WHEN (CONVERT(BIT, execution_mode & 4)) = 1
           THEN 'Yes'
           ELSE 'No'
           END  AS on_schedule,
           CASE
           WHEN (CONVERT(BIT, execution_mode & 2)) = 1
           THEN 'Yes'
           ELSE 'No'
           END  AS on_change_log,
       CASE
           WHEN (CONVERT(BIT, execution_mode & 1)) = 1
           THEN 'Yes'
           ELSE 'No'
           END  AS on_change_prevent
FROM msdb.dbo.syspolicy_management_facets ;
Central Management Servers
SQL Server Management Studio provides a feature called a central management server.
This feature allows you to register an instance as a central management server and then
register other instances as registered servers of this central management server. Once

you have registered servers under a central management server, you can run queries
against all servers in the group or run policies against all servers within a group.
Tip  CMS is a great feature, when used with Policy-Based Management, but also
in its own right. I always implement CMS when I am looking after medium or large
SQL Server estates, for purposes such as running ad hoc queries against multiple
servers. This allows me to quickly answer management and capacity questions,
such as “How many databases do we have in our estate?”
To register a central management server, select Registered Servers from the View
menu in SQL Server Management Studio. This causes the Registered Servers window to
appear, which is illustrated in Figure 23-1.
Let’s register our SQLSERVER\MASTERSERVER instance (which is the server\instance
name we use in the demonstrations within this section) as a central management server
by selecting Register Central Management Server from the context menu of Central
Management Servers. This causes the General tab of the New Server Registration dialog
box to display, as illustrated in Figure 23-2.
Figure 23-1.  The Registered Servers window

On this tab, we enter the server\instance name of the central management server in
the Server Name box. This causes the Registered Server Name field to update, but you
can edit this manually to give it a new name if you wish. Optionally, you can also add a
description for the instance.
On the Connection Properties tab, displayed in Figure 23-3, we specify our
preferences for connecting to the instance.
Figure 23-2.  The General tab

On this tab, we enter a database as a landing zone. If we leave the option as Default,
then the connection is made to our default database. In the Network section of the tab,
you can specify a specific network protocol to use or leave the setting as Default, which is
what we have done here. Leaving this as Default causes the connection to use the highest
priority protocol specified in the instance’s network configuration. Although changing
the network packet size is not normally advised, because in most scenarios, it will have a
negative effect, doing so can improve performance in atypical scenarios by allowing the
connection to benefit from jumbo frames, which are Ethernet frames that can support a
larger payload and therefore cause less fragmentation of traffic.
Figure 23-3.  The Connection Properties tab

In the Connection section of the screen, we specify durations for connection
timeouts and execution timeouts. You can also specify whether to encrypt connections
made to the central management server. If you are managing multiple instances within
a single instance of SQL Server Management Studio, the Use Custom Color option is
very useful for color coding the instance. Checking this option and specifying a color
helps avoid queries accidently being run against an incorrect server. I find color coding
instances particularly useful when I’m troubleshooting failed code releases, since I don’t
want to accidently run Dev/Test code against production!
The Always Encrypted tab allows you to enable Always Encrypted for the connection
and specify the appropriate attestation server. This tab is illustrated in Figure 23-4. For
further information on Always Encrypted, please see Chapter 11.
Figure 23-4.  Always Encrypted tab

The Additional Connection Parameters tab, shown in Figure 23-5, allows you to
specify connection string properties manually. You should be aware, however, that if
you enter connection properties that you have already specified on the other tabs, the
manually specified properties will override your selections in the other tabs.
Clicking the Test button at the bottom of the New Server Registration window allows
you to test the connection to the instance before you save it. This is always a good idea
because it helps you avoid unnecessary troubleshooting at a later date.
Figure 23-5.  Additional Connection Parameters tab

Once we have registered the central management server, we can choose to
either register servers directly below the central management server or create server
groups below the central management server. Base the strategy you choose here on
the requirements of your environment. For example, if all servers that the central
management server manages should have the same policies applied, it is probably
sufficient to register the servers directly below the central management server. If your
central management server will manage servers from different environments, however,
such as Prod and Dev/Test, then you probably want to enforce different sets of policies
against different environments; in such cases, it makes sense to create different server
groups. Selecting New Server Group from the context menu of your newly created
central management server invokes the New Server Group Properties dialog box, as
illustrated in Figure 23-6.
You can see that we are using this dialog box to enter the name and description of
the server group that will group our Dev/Test servers together. After exiting the dialog
box, we repeat the process to create a server group for our production servers, which we
name Prod.
Tip  You can also nest server groups. Therefore, in more complex topologies, you
can have a server group for each geographical region, which contains a server
group for each environment.
Figure 23-6.  New Server Group Properties dialog box

Now let’s choose the New Server Registration option from the context menu of
each server group to add our instances to the appropriate groups. We add ­SQLSERVERTARGETSERVER1 and SQLSERVER\TARGETSERVER2 to the Prod group and add the default
instance of SQLSERVER to the DevTest group. You can add the servers using the same New
Server Registration dialog box that you used to register the central management server.
Figure 23-7 shows the Registered Servers screen after the servers have been added.
One very useful feature of central management servers is their ability to run queries
against all servers within a server group or against all servers they manage. For example,
we can select New Query from the context menu of the Prod Server Group and run the
query in Listing 23-3.
Listing 23-3.  Listing All Database in the Server Group
SELECT name
FROM sys.Databases ;
This query returns the results displayed in Figure 23-8.
Figure 23-7.  The Registered Servers window