The first thing you notice is that the status bar below the query results is pink instead
of yellow. This indicates that the query has been run against multiple servers. Second,
instead of displaying an instance name, the status bar displays the server group that the
query has been run against; in our case, this is Prod. Finally, notice that an additional
column has been added to the result set. This column is called Server Name, and it
indicates which instance within the server group the row returned from. Because no user
databases exist on SQLSERVER\TARGETSERVER1 or SQLSERVER\TARGETSERVER2, the four
system databases have been returned from each instance.
Creating Policies
You can create policies using either SQL Server Management Studio or T-SQL. The
following sections discuss how to create a simple static policy, before they go on to
discuss how to create advanced, dynamic policies.
Creating Simple Policies
PBM offers a great deal of flexibility within its predefined facets, properties, and
conditions. You can use this flexibility to create a comprehensive set of policies for your
enterprise. The following sections discuss how to use PBM’s built-in functionality to
create simple policies.
Figure 23-8.  Results of listing all servers in the server group

Creating a Policy That You Can Manually Evaluate
As you’ve probably noticed, example databases in this book use the name format of
Chapter<ChapterNumber>. Therefore, here we create a policy that enforces this naming
convention by causing any policy that violates this policy to roll back and generate
an error. To do this, we invoke the Create New Policy dialog box by drilling through
Management ➤ Policy Management in Object Explorer on the Master server and then
selecting New Policy from the Policies context menu. Figure 23-9 displays the General
page of the dialog box.
On this page, we give the policy a name but find that the Against Targets and
Evaluation Mode options are not accessible. This is because we have not yet created
a condition. Therefore, our next step is to use the Check Condition drop-down box to
select New Condition. This causes the General page of the Create New Condition dialog
box to display, illustrated in Figure 23-10.
Figure 23-9.  Create New Policy dialog box, General page

On this page, we give the condition a name and select the Database facet. In the
Expression area of the screen, we select that the @Name field should be LIKE 'Chapter%',
where % is a zero-or-more-character wildcard. On the Description page, we are
optionally able to specify a textual description for the condition.
Back on the General page of the Create New Policy dialog box, we ensure that the
Evaluation Mode drop-down is set to select On Demand, which means that the policy
is not evaluated unless we explicitly evaluate it. The only other option available is to
schedule the evaluation. This is because the Database facet does not support the On
Change: Log Only or On Change: Prevent evaluation modes.
Our policy obviously does not apply to system databases. This matters because
we can use our policy to check existing databases as well as new databases we create.
Therefore, in the Against Targets section of the page, we use the drop-down box to enter
the Create New Condition dialog box and create a condition that excludes databases that
have a database ID of four or less, as shown in Figure 23-11.
Figure 23-10.  Create New Condition dialog box, General page

Back in the Create New Policy dialog box, we can create a condition to enforce a
server restriction, which filters the instances that the policy is evaluated against. Because
we are only evaluating the policy against our SQLSERVER\MASTERSERVER instance,
however, we do not need to do this. Instead, we navigate to the Description page,
illustrated in Figure 23-12.
Figure 23-11.  Create an ExcludeSystemDatabases condition

On this page, we use the New button to create a new category, CodeRelease, which
helps us check code quality in a UAT (user acceptance testing) or OAT (operational
acceptance testing) environment before the code is promoted to production. Optionally,
we can also add a free text description of the policy and a help hyperlink, alongside a
web site address or e-mail link.
Manually Evaluating a Policy
Before evaluating our policy, we first create a database that does not match our naming
convention by executing the command in Listing 23-4.
Listing 23-4.  Creating a BrokenPolicy Database
CREATE DATABASE BrokenPolicy ;
We can evaluate our new policy against our instance by using the Evaluate Policies
dialog box, which we can invoke by drilling through Management ➤ Policy Management
➤ Policies and by selecting Evaluate from the context menu of our policy.
Figure 23-12.  The Description page

Tip  You can manually evaluate the policy even if it is disabled.
In the Evaluate Policies dialog box, shown in Figure 23-13, you see a list of policies
that have been evaluated in the top half of the window; a status indicator informs you if
any policies have been broken. In the bottom half of the window, you see a list of targets
that the highlighted policy was evaluated against; here a status indicator informs you of
the policy’s status on a target-by-target basis.
Tip  If you wish to evaluate multiple policies, select Evaluate from the context
menu of the Policies folder in Object Explorer, and then select which policies you
would like to evaluate. All selected policies are then evaluated and displayed in the
Evaluation Results page.
Tip  We created the Chapter22 database in Chapter 22 of this book. If you do
not have a Chapter22 database, you can create it using the statement CREATE
DATABASE Chapter22 ;
Click the View link in the Details column to invoke the Results Detailed View dialog
box, as illustrated in Figure 23-14. This information is useful for failed policy evaluations
because it provides the details of the actual value that did not meet the policy’s condition.
Figure 23-13.  The Evaluate Policies dialog box

Creating a Policy That Prevents Unwanted Activity
Another very useful simple policy is one that helps you prevent developers from
obfuscating their stored procedures. Procedure obfuscation arguably has a place in
third-party software, in order to prevent the theft of intellectual property. For in-house
applications, however, there is no need to use obfuscation, and doing so can lead to
issues with diagnosing performance issues. Additionally, if the development teams are
not using source control, it can lead to the loss of code, in the event of a disaster. In this
instance, rather than just evaluating the policy on an ad hoc basis, we want to prevent
stored procedures that are obfuscated from being created. This means that during code
releases, you do not need to review every stored procedure for the WITH ENCRYPTION
syntax. Instead, you can expect the policy to be evaluated and the CREATE PROCEDURE
statement to be rolled back, preventing this from occurring.
Before we create this policy, we need to ensure that nested triggers are enabled on
the instance. This is because the policy will be enforced using DDL triggers, and nested
Figure 23-14.  The Results Detailed View dialog box

triggers are a hard technical requirement for the On Change: Prevent mode. You can
enable nested triggers using sp_configure, with the script in Listing 23-5; however, they
are turned on by default.
Listing 23-5.  Enabling Nested Triggers
EXEC sp_configure 'nested triggers', 1 ;
RECONFIGURE
After creating the policy, you need to create a condition. When creating the
condition, as illustrated in Figure 23-15, we use the @IsEncrypted property of the
StoredProcedure facet.
In the Create New Policy dialog box, illustrated in Figure 23-16, we could use the
Against Targets area to configure which targets should be evaluated by the policy; the
setting defaults to Every Stored Procedure In Every Database, however. This suits our
needs, so we do not need to create a condition. In the Evaluation Mode drop-down, we
select On Change: Prevent; this makes it so it is not possible to create stored procedures
on our SQLSERVER\MASTERSERVER instance if it is obfuscated. We also make sure to check
the Enabled box so that the policy is enabled when it is created.
Figure 23-15.  The Create New Condition dialog box

To demonstrate the prevention in action, we attempt to create a stored procedure
using the script in Listing 23-6.
Listing 23-6.  Creating a Stored Procedure with NOLOCK
CREATE PROCEDURE ObfuscatedProc
WITH ENCRYPTION
AS
BEGIN
        SELECT *
        FROM sys.tables
END
Figure 23-17 shows the error that is thrown when we attempt to run this CREATE
PROCEDURE statement.
Figure 23-16.  The Create New Policy dialog box

Creating an Advanced Policy
PBM is extensible, and if you can’t create the required condition using the built-in facet
properties, the Expression Advanced Editor allows you to use a wide range of functions.
These functions include ExecuteSql() and ExecuteWql(), which allow you to build your
own SQL and WQL (Windows Query Language), respectively. The ExecuteSql() and
ExecuteWql() functions are not T-SQL functions. They are part of the PBM framework.
You can use these functions to write queries against either the Database Engine or
Windows and evaluate the result. The functions are called once for each target. So, for
example, if they are used with the Server facet, they only run once, but if they are used
against the Table facet, they are evaluated for every target table. If multiple columns
are returned when you are using ExecuteSql(), then the first column of the first row is
evaluated. If multiple columns are returned when you are using ExecuteWql(), then an
error is thrown. For example, imagine that you want to ensure that the SQL Server Agent
service starts. You can achieve this in T-SQL by running the query in Listing 23-7. This
query uses the LIKE operator because the servicename column also includes the name
of the service, and the LIKE operator makes the query generic so that it can be run on
any instance, without needing to be modified.
Listing 23-7.  Checking to Make Sure SQL Server Agent Is Running with T-SQL
SELECT status_desc
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server Agent%' ;
Or alternatively, you can achieve the same result by using the WQL query in
Listing 23-8.
Figure 23-17.  The error thrown by the policy trigger

Note  You can find an WQL reference at https://msdn.microsoft.com/en-­
us/library/aa394606(v=vs.85).aspx.
Listing 23-8.  Checking That SQL Server Agent Is Running with WQL
SELECT State FROM Win32_Service  WHERE Name ="SQLSERVERAGENT$MASTERSERVER"
To use the T-SQL version of the query, you need to use the ExecuteSql() function,
which accepts the parameters in Table 23-1.
To use the WQL version of the query, you need to use ExecuteWql(), which accepts
the parameters described in Table 23-2.
Therefore, if you are using the T-SQL approach, your condition would use the script
in Listing 23-9 in the Conditions editor of PBM (it will not work directly in SSMS).
Listing 23-9.  ExecuteSQL()
ExecuteSql('string', 'SELECT status_desc FROM sys.dm_server_services WHERE
servicename LIKE "SQL Server Agent%"')
Table 23-1.  ExecuteSQL() Parameters
Parameter
Description
returnType
Specifies the return type expected from the query. Acceptable values are Numeric,
String, Bool, DateTime, Array, and GUID.
sqlQuery
Specifies the query that should run.
Table 23-2.  ExecuteWQL() Parameters
Parameter
Description
returnType
Specifies the return type expected from the query. Acceptable values are Numeric,
String, Bool, DateTime, Array, and GUID.
namespace
Specifies the WQL namespace that the query should be executed against.
wqlQuery
Specifies the query that should run.

Tip  It is important to note here that we had to escape the single quotes in our
query, to ensure that they are recognized during execution.
If you use the WQL approach, your condition needs to use the script in Listing 23-10.
Listing 23-10.  ExecuteWQL()
ExecuteWql('String', 'root\CIMV2', 'SELECT State FROM Win32_Service  WHERE
Name ="SQLSERVERAGENT$MASTERSERVER"')
Figure 23-18 shows how we would create the condition using the WQL approach.
Caution  Because of the power and flexibility of the ExecuteWql() and
ExecuteSql() functions, it is possible that they will be abused to create
security holes. Therefore, make sure you carefully control who has permissions
to create policies.
Managing Policies
Policies are installed on an instance of SQL Server, but you can export them to XML
files, which in turn allows them to be ported to other servers or to central management
servers so that they can be evaluated against multiple instances at the same time.
Figure 23-18.  Creating the condition with ExecuteWql()

The following sections discuss how to import and export policies, as well as how to
use policies in conjunction with central management servers. We also discuss how to
manage policies with PowerShell.
Importing and Exporting Policies
Policies can be exported to and imported from the file system, as XML files. To export
our DatabaseNameConvention policy to the default file location, we select Export
Policy from the context menu of the DatabaseNameConvention policy in Object Explorer,
causing the Export Policy dialog box before to be invoked. Here, we can simply choose a
name for the file and click Save, as shown in Figure 23-19.
We now import the policy into our SQLSERVER\TARGETSERVER1 instance. To do this,
we connect to the TARGETSERVER1 instance in Object Explorer and then drill through
Management ➤ Policy-Based Management, before selecting Import Policy from the
Policies context menu. This invokes the Import dialog box, as displayed in Figure 23-20.
Figure 23-19.  The Export Policy dialog box

In this dialog box, we use the Files To Import ellipses button to select our
DatabaseNameConvention policy. We can also choose the state of the policy after it is
imported from the Policy State drop-down and specify whether policies that already exist
on the instance with the same name should be overwritten.
Enterprise Management with Policies
Although being able to evaluate a policy against a single instance of SQL Server is useful,
to maximize the power of PBM, you can combine policies with central management
servers so that the policy can be evaluated against the SQL Server Enterprise in a single
execution.
For example, imagine that we want to evaluate the DatabaseNameConvention
policy against all servers within the Prod group that we created when we registered the
SQLSERVER\MASTERSERVER instance as a central management server. To do this, we drill
through Central Management Servers ➤ SQLSERVER\MASTERSERVER in the Registered
Servers window before we select Evaluate Policies from the Prod context menu.
This invokes the Evaluate Policies dialog box. Here, you can use the Source ellipses
button to invoke the Select Source dialog box and choose the policy or policies that you
would like to evaluate against the group, as shown in Figure 23-21.
Figure 23-20.  The Import dialog box

In the Select Source dialog box, either select policies stored as XML files from the file
system or specify the connection details of an instance where the policy is installed. In
our case, we select the DatabaseNameConvention by clicking the Files ellipses button.
Selected policies then display in the Policies section of the screen, as shown in
Figure 23-22. If you selected a source with multiple policies, you can use the check boxes
to define which policies to evaluate. Clicking the Evaluate button causes the selected
policies to be evaluated against all servers in the group.
Figure 23-21.  The Evaluate Policies dialog box

Evaluating Policies with PowerShell
When policies are installed on an instance, they can be evaluated using the methods
already described in this chapter. If your policies are stored as XML files, however,
then you can still evaluate them using PowerShell. This can be helpful if your SQL
Server enterprise includes SQL Server 2000 or 2005 instances, as many still do. Because
PBM was only introduced in SQL Server 2008, policies cannot be imported into older
instances, but PowerShell offers a useful workaround for this issue.
To evaluate our DatabaseNameConvention policy against our SQLSERVERMASTERSERVER instance, from the XML file using PowerShell, we need to run the script in
Listing 23-11. The first line of this script changes the path to the folder where the policy is
stored. The second line actually evaluates the policy.
If the property we were configuring was settable and deterministic (which ours
is not), then we could add the -AdHocPolicyExecutionMode parameter and set it to
"Configure". This would cause the setting to change to fall in line with our policy.
Figure 23-22.  The Evaluate Policies dialog box

Listing 23-11.  Evaluating a Policy with PowerShell
sl "C:\Users\Administrator\Documents\SQL Server Management Studio\Policies"
Invoke-PolicyEvaluation -Policy "C:\Users\Administrator\DocumentsSQL Server Management Studio\Policies\DatabaseNameConvention.xml"
-TargetServerName ".\MASTERSERVER"
The output of this policy evaluation is shown in Figure 23-23.
Tip  To evaluate multiple properties, provide a comma-separated list for the
-Policy parameter.
Summary
Policy-Based Management (PBM) offers a powerful and flexible method for ensuring
coding standards and hosting standards are met across your enterprise. A target is an
entity managed by PBM. A condition is a Boolean expression that the policy evaluates
against the targets, and a facet is a collection of properties that relate to a specific type
of target.
Depending on the facet you use, a policy offers up to four policy evaluation modes:
On Demand, On Schedule, On Change: Log Only, and On Change: Prevent. On Demand,
On Schedule, and On Change: Log Only can be thought of as reactive, whereas On
Change: Prevent can be thought of as proactive, since it actively stops a configuration
from being made, which violates a policy. Because On Change modes rely on DDL
triggers, you must enable nested triggers at the instance level, and they are not available
for all facets.
Figure 23-23.  Results of policy evaluation

Policies are extensible, through the use of the ExecuteSql() and ExecuteWql()
functions, which allow you to evaluate the results of T-SQL or WQL queries. These
functions offer massive flexibility, but their power can also cause security holes to be
opened, so exercise caution when granting permissions to create policies.
An instance can be registered as a central management server, and other servers can
be registered underneath it, either directly or in groups. This gives DBAs the ability to
run a query across multiple instances at the same time, and it also offers them the ability
to evaluate policies against multiple servers at the same time. This means that you can
use Policy-Based Management at the Enterprise level to enforce standards.
You can evaluate policies from within SQL Server or using PowerShell with the
-InvokePolicyEvaluation cmdlet. This offers you increased flexibility for managing
estates that have older SQL Server instances, such as 2000 or 2005. This is because
PowerShell allows DBAs to evaluate the policies from XML files, instead of only being
able to evaluate them after importing them to MSDB.