SQL Server 2008 : Policy-based management - Policies in action (part 1) - Importing policies from file, Evaluating policies

9/19/2013 7:31:12 PM

SQL Server 2008 ships with a number of predefined policies that can be imported and evaluated. These policies encapsulate best practices such as those for securing the surface area of a SQL instance. In addition to importing these policies, new policies can be created and exported to file for later use on other server instances.

In this section, we'll start off by importing an existing policy and looking at the various evaluation options. We'll then walk through the process of creating a new policy from scratch and exporting it to file.

1. Importing policies from file

In SQL Server 2005 and earlier, tools such as Best Practices Analyzer and Baseline Security Analyzer were used to periodically check a SQL Server instance for adherence to various best practices. In SQL Server 2008, policy-based management can be used to import predefined policies that encapsulate best practice settings.

Once imported, depending on the evaluation mode, the policies remain in place, actively checking, preventing, and/or logging violations. As such, they're a stronger, more active version of previous-generation tools such as Best Practices Analyzer, and can be customized to suit a particular environment's requirements.

Importing an existing policy is straightforward. In SQL Server Management Studio simply right-click the Policies menu under Policy Management, choose Import Policy, and specify the location of the policy definition file. SQL Server 2008 ships with anumber of predefined policies that can be imported. These policies are located in C:\Program Files\Microsoft SQL Server\100\Tools\Policies.

In this directory (or the equivalent installation directory) are three subdirectories containing polices for the Database Engine, together with Reporting Services and Analysis Services. The policies for Reporting and Analysis Services are limited to surface area configuration checks, and the Database Engine directory contains approximately 50 policies covering a wide variety of best practices. Here are some examples of best practices addressed by the supplied policies:

  • Backup files must be on separate devices from the database files.

  • Data and log files should be on separate drives.

  • The default trace should be enabled.

  • Max Degree of Parallelism should be less than 8.

  • No unexpected system failures should be detected.

  • Backups should be performed frequently.

  • No I/O delay messages should be detected.

One of the nice things about the supplied policies is that some of them can be used with previous versions of SQL Server. For example, the File Growth for SQL Server 2000 policy can be used to check for the existence of SQL Server 2000 databases larger than 1GB whose AutoGrowth property is percentage based rather than a fixed size. Although policies can be defined and executed against versions of SQL Server prior to 2008, there are some restrictions.

In the example shown in figure 1, we'll import the supplied Surface Area Configuration for Database Engine 2008 Features.

Once the file is selected, the only other option we need to specify is Policy State. By default, the policy state is preserved on import—that is, if the policy is enabled in the definition file, it will be enabled on import. Alternatively, we can explicitly enable or disable the policy as part of the import process.

Figure 1. You can import existing policies to check SQL instances for compliance based on predefined configuration files.

Now that we've imported a policy, let's look at the process of evaluating it.

2. Evaluating policies

One of the most powerful features of policy-based management is the variety of ways in which checks and violations can be defined and managed at an individual policy level.

In the previous section we covered the four evaluation modes: On Change – Prevent, On Change – Log Only, On Schedule, and On Demand. Let's take a look at an example of each of these methods, starting with On Demand.

On Demand

When you create a policy using the On Demand evaluation mode, the policy is created in a disabled state. You can then use it in an ad hoc manner as required by right-clicking the policy and choosing Evaluate. Let's do this for the Surface Area Configuration policy we imported earlier. Figure 2 shows the evaluation results of this policy. In this example, the evaluation failed because the target server has Database Mail enabled.

In addition to clicking View to see the details of the evaluation, you can click Apply, which will reconfigure the server to be compliant with the policy.

On Change – Prevent

You may wish to enforce certain policies so that violations are prevented from occurring. Unfortunately, this is only possible for a certain class of conditions, specifically those able to be rolled back with DDL triggers.

As an example, figure 3 contains the error message returned when a table create statement violates a table name condition specifying that tables must be created with a tbl_ prefix.

Figure 2. You can manually evaluate a policy by right-clicking it and choosing Evaluate.

Figure 3. The On Change – Prevent evaluation mode will actively prevent changes that violate policy conditions.

On Change – Log Only

Like On Change – Prevent, On Change – Log Only actively monitors for policy violations, but rather than roll back the violation, it logs the violation to the SQL Server log. Regardless of the evaluation mode, all policy failures are logged, enabling custom policy failure alerts to be set up. Figure 4 shows such a policy failure error in the SQL Server log.

Figure 4. All policy violations are recorded in the SQL Server log.

On Schedule

The On Schedule evaluation mode lets you enable policies to be checked on a scheduled basis. This mode ensures that the overhead of active policy checking doesn't impact performance. When you choose this mode, the policy creator selects a schedule, which creates SQL Agent jobs to run the scheduled policy checks.

Now that we've looked at the process of importing policies and covered the evaluation modes, let's walk through the process of creating a new policy to check database properties such as AutoClose and AutoShrink.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone