SQL Server 2008 : Policy-based management - Advanced policy-based management

The policies we've looked at so far have all been based on static server properties, such as checking the value of various surface area configuration properties to determine whether xp_cmdshell is enabled. In this section, we'll look at ways of enabling more advanced, or dynamic, policy checks. We'll also cover the benefits of combining policy-based management with PowerShell.

1. ExecuteWql() and ExecuteSql()

One of these policies is used to detect the presence of I/O delay messages in the Windows Event Log. Clearly, this is a very different type of policy from those we've covered so far.

Let's import the policy file Windows Event Log I_O Delay Warning.xml and have a look at its condition. As shown in figure 1, we can see that rather than use one of the familiar condition fields such as @AutoClose, it uses what appears to be a function call.

By clicking the ellipsis button next to the field, we enter the Advanced Edit mode, as shown in figure 2. Here, we can see that this policy uses the ExecuteWql() function to query the Windows Event Log for a particular error event code.

The ExecuteWql() function permits you to use Windows Management Instrumentation (WMI), specifically the WMI Query Language (WQL), to query the operating system for information. The usage shown here is one example of a nearly limitless number of possible uses.

Figure 1. The condition definition of the Windows Event Log I/O Delay Warning Check policy uses the ExecuteWql function.
 

Note in figure 2 the other functions available. Directly above ExecuteWql() is ExecuteSql(), which you can use to run a traditional T-SQL query. In a similar manner to ExecuteWql(), this function can run any T-SQL code, and can therefore be used to create flexible and powerful policies.

When using the ExecuteSql() function, you must keep in mind a few things. First, the value returned needs to be something that can be evaluated in the condition editor. For example, you can use a case statement to return a single numeric value and compare that to an expected value in the condition editor.

Figure 2. The Advanced Edit mode lets you use functions such as ExecuteWql() and ExecuteSql() to create advanced policies.
 

Figure 3. Use a custom expression to check for the existence of uncompressed tables.
 

Second, given the fact that ExecuteSql() accepts and runs any SQL (including delete statements), take into account the security implications. One of the new fixed database roles in the MSDB database is called PolicyAdministratorRole. Members of this role are able to execute policy checks. To prevent such users from executing ExecuteSql()-based policies that elevate their privileges, the ##MS_PolicyTsqlExecutionLogin## SQL Server login is used as a proxy account for the execution of this function. As such, not only does this login have to be enabled, the appropriate permissions need to granted on the objects referenced in the ExecuteSql() function.

Despite the wide range of facets and included policies, certain aspects of SQL Server instances and databases can't be checked in SQL Server 2008. An example is checking that all tables use data compression. Data compression is a partition property, and there's no facet for partitions in SQL Server 2008. As such, you could use the ExecuteSql() function to query for the existence of any tables that aren't compressed, using a function like this:

Executesql('Numeric', 'select count(*)
from sys.partitions p where p.data_compression=0')

If the results of this query return a nonzero value, that means tables are present that aren't compressed. You'd use such a function in a condition like the one shown in figure 3.

Both the ExecuteSql() and ExecuteWql() functions, fully documented in SQL Server BOL, enable you to create policies with almost limitless flexibility, and could potentially be used to check policy compliance of items completely unrelated to SQL Server.

2. PowerShell

Released in 2006 and included in Windows Server 2008, Windows PowerShell is a command line-based scripting language used to perform administrative tasks using cmdlets. SQL Server 2008 is PowerShell aware and exposes its management interface via its own cmdlets.

By registering a SQL Server 2005 instance with the 2008 Management Studio tool, you can right-click 2005 objects and manually evaluate policies. What you can't do (without using PowerShell) is store policies within a 2005 instance for scheduled evaluation as you can with a 2008 instance.

Enter PowerShell. Using the Invoke-PolicyEvaluation cmdlet, you can evaluate policies against SQL Server instances (2000, 2005, or 2008) as a PowerShell script. SQL Server 2008 also includes the ability to run PowerShell-based SQL Agent job steps, so the combination of these two features enables you to schedule policy evaluation against a variety of SQL Server versions.

Right-click a SQL Server 2008 instance in Management Studio and click Start PowerShell, to open a PowerShell interface from which you can (among other things) evaluate a policy. In the example shown in figure 4, after using the sl command to change directory to the location containing the policy files, we've used the Invoke-PolicyEvaluation cmdlet to evaluate a policy against a SQL Server 2005 instance using the PowerShell interface.

As you can see in the Result column, the server failed evaluation. One of the nice things about the Invoke-PolicyEvaluation cmdlet is the variety of parameters it takes, a few of which are as follows:

• The -Policy option is used to specify the required policy to execute. An alternate use of this option is to supply a comma-separated list of policies, allowing multiple policies to be executed as part of the one command.

• The gci option allows Invoke-PolicyEvaluation to receive input from a pipe. For example, gci | Invoke-PolicyEvaluation -TargetServer "BNE-SQL-PR-01\SQL2005" will evaluate every policy in the current directory against the specified server.

• -OutputXml allows you to direct the output of the evaluation to a file for later inspection. This option is particularly useful when running scheduled evaluations.

• -AdHocPolicyExecutionMode "Configure" implements the policy conditions. Should the evaluation fail, the server will be reconfigured according to the policy.

Figure 4. Using the Invoke-PolicyEvaluation cmdlet to evaluate a policy using the PowerShell interface
 

Figure 5. Creating a SQL Agent Job step to execute a PowerShell script enables the scheduled evaluation of policies against a SQL Server 2000/2005 instance.
 

So in order to schedule policy checks against earlier SQL Server versions, we can take our policy script and create a PowerShell-based SQL Server Agent job step, as shown in figure 5. Note that we formatted the script for visibility by adding extra line breaks.

We can optionally enhance the job step using the additional parameters described earlier to reconfigure the server in case it fails evaluation and/or to evaluate multiple policies at once.

In summary, the combination of policy-based management, central management servers, and PowerShell cmdlets enables a whole new level of powerful management possibilities for the enterprise DBA.