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.

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

Let’s register our SQLSERVERMASTERSERVER 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 24-2.

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

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!

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 24-4. For further information on Always Encrypted, please see Chapter 12.

The Additional Connection Properties tab, shown in Figure 24-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.

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

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 24-7 shows the Registered Servers screen after the servers have been added.

This query returns the results displayed in Figure 24-8.

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

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

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

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 website 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 24-4.

CREATE DATABASE BrokenPolicy ;

Listing 24-4

Creating a BrokenPolicy Database

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.

Tip

You can manually evaluate the policy even if it is disabled.

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

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 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 24-5; however, they are turned on by default.

EXEC sp_configure 'nested triggers', 1 ;

RECONFIGURE

Listing 24-5

Enabling Nested Triggers

After creating the policy, you need to create a condition. When creating the condition, as illustrated in Figure 24-15, we use the @IsEncrypted property of the StoredProcedure facet.

In the Create New Policy dialog box, illustrated in Figure 24-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 SQLSERVERMASTERSERVER instance if is obfuscated. 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 24-6.

CREATE PROCEDURE ObfuscatedProc

WITH ENCRYPTION

AS

BEGIN

        SELECT *

        FROM sys.tables

END

Listing 24-6

Creating a Stored Procedure with NOLOCK

Figure 24-17 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 24-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%' ;

Listing 24-7

Checking to Make Sure SQL Server Agent Is Running with T-SQL

Or alternatively, you can achieve the same result by using the WQL query in Listing 24-8.

Note

You can find an WQL reference at https://msdn.microsoft.com/en-us/library/aa394606(v=vs.85).aspx.

SELECT State FROM Win32_Service  WHERE Name ="SQLSERVERAGENT$MASTERSERVER"

Listing 24-8

Checking That SQL Server Agent Is Running with WQL

To use the T-SQL version of the query, you need to use the ExecuteSql() function, which accepts the parameters in Table 24-1.

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

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

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

Therefore, if you are using the T-SQL approach, your condition would use the script in Listing 24-9 in the Conditions editor of PBM (it will not work directly in SSMS).

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

Listing 24-9

ExecuteSQL()

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

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

Listing 24-10

ExecuteWQL()

Figure 24-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. 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 24-19.

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

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

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

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

sl "C:UsersAdministratorDocumentsSQL Server Management StudioPolicies"

Invoke-PolicyEvaluation -Policy "C:UsersAdministratorDocumentsSQL Server Management StudioPoliciesDatabaseNameConvention.xml" -TargetServerName ".MASTERSERVER"

Listing 24-11

Evaluating a Policy with PowerShell

The output of this policy evaluation is shown in Figure 24-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.

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.