Policy-Based Management (PBM) is a system DBAs can use to report on or enforce standards across the enterprise. 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. A binds 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 provides 93 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 22-1.

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.

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.

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 Table 22-1 provides a list of facets and the evaluation modes each supports.

You can reproduce this list in SQL Server by running the query in Listing 22-2.

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.

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 22-1.

Let’s register our SQLSERVERMASTERSERVER instance (which is the serverinstance 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 22-2.

On this tab, we enter the ServerInstance 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 22-3, we specify our preferences for connecting to the instance.

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, 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.

In the Connection section of the screen, we specify durations for connection time-outs and execution time-outs. 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! 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.

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 22-4.

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.

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 SQLSERVERTARGETSERVER2 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 22-5 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 22-3.

SELECT name
FROM sys.Databases ;

This query returns the results displayed in Figure 22-6.

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 SQLSERVERTARGETSERVER1 or SQLSERVERTARGETSERVER2, 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.

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 22-7 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 22-8.

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 22-9.

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 SQLSERVERMASTERSERVER instance, however, we do not need to do this. Instead, we navigate to the Description page, illustrated in Figure 22-10.

On this page, we use the New button to create a new category, CodeRelease, which help 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 website address or e-mail link.

The final view of the policy is illustrated in Figure 22-11.

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 22-4.

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.

In the Evaluate Policies dialog box, shown in Figure 22-12, 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.

Click the View link in the Details column to invoke the Results Detailed View dialog box, as illustrated in Figure 22-13. 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.

Creating a Policy That Prevents Unwanted Activity

Another very useful simple policy is one that helps you prevent developers from using NOLOCK in their stored procedures as a perceived performance enhancement. In this instance, rather than just evaluating the policy on an ad-hoc basis, we want to prevent stored procedures that contain NOLOCK from being created. If this succeeds, during code releases, you do not need to review every stored procedure for the NOLOCK or READUNCOMMITTED syntax. Instead, you can expect the policy to be evaluated and the CREATE PROCEDURE statement to be rolled back.

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, which we discuss in Chapter 24, and nested triggers are a hard technical requirement for this functionality. You can enable nested triggers using sp_configure, with the script in Listing 22-5; however, they are turned on by default.

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 22-14, we use the UPPER function to convert the @MethodName property of the StoredProcedure facet to uppercase. We do this so that it can be evaluated to see if it contains the strings NOLOCK or READUNCOMMITTED, which when specified as a query hint, have the same effect as each other. By converting the @MethodName property to uppercase before the evaluation, we avoid issues with case-sensitivity if our instance is running a case-sensitive collation.

In the Create New Policy dialog box, illustrated in Figure 22-15, we use the Against Targets area to configure which targets should be evaluated by the policy; the settings default to Every Stored Procedure In Every Database. 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 SQLSERVERMASTERSERVER instance if the definition contains the words NOLOCK or READUNCOMMITTED. We also make sure to check the Enabled box so that the policy is enabled when it is created.

To demonstrate the prevention in action, we attempt to create a stored procedure using the script in Listing 22-6.

CREATE PROCEDURE ReadWithNOLOCK
AS
BEGIN
        SELECT *
        FROM sys.tables WITH (NOLOCK)
END

Figure 22-16 shows the error that is thrown when we attempt to run this CREATE PROCEDURE statement.

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 22-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.

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 22-8.

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 22-2.

To use the WQL version of the query, you need to use ExecuteWql(), which accepts the parameters described in Table 22-3.

Therefore, if you are using the T-SQL approach, your condition would use the script in Listing 22-9.

ExecuteSql(’string’, ’SELECT status_desc FROM sys.dm_server_services WHERE servicename LIKE ’’SQL Server Agent%’’’)

If you use the WQL approach, your condition needs to use the script in Listing 22-10.

ExecuteWql(’String’, ’rootCIMV2’, ’SELECT State FROM Win32_Service  WHERE Name ="SQLSERVERAGENT$MASTERSERVER"’)

Figure 22-17 shows how we would create the condition using the WQL approach.

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. 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

SQL Server provides a folder structure for storing policies under the 32-bit installation path. Assuming that you have installed SQL Server using the default installation directory, the full path would be C:Program Files (x86)Microsoft SQL Server120ToolsPolicies. In this folder, you will find three additional folders; DatabaseEngine, AnalysisServices, and ReportingServices.

If we want to export our DatabaseNameConvention policy to the DatabaseEngine folder, we select Export Policy from the context menu of the DatabaseNameConvention policy in Object Explorer and navigate to the DatabaseEngine folder in the Export Policy dialog box before we choose a name for the file and click Save, as shown in Figure 22-18.

We now import the policy into our SQLSERVERTARGETSERVER1 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 22-19.

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 SQLSERVERMASTERSERVER instance as a central management server. To do this, we drill through Central Management Servers | SQLSERVERMASTERSERVER 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 22-20.

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 22-21. 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.

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 22-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 inline with our policy.

sl "C:Program Files (x86)Microsoft SQL Server120ToolsPoliciesDatabaseEngine"

Invoke-PolicyEvaluation -Policy " C:Program Files (x86)Microsoft SQL Server
120ToolsPoliciesDatabaseEngineDatabaseNameConvention.xml DatabaseNameConvention.xml" -TargetServer" .MASTERSERVER"

The output of this policy evaluation is shown in Figure 22-22.

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.

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.