Whilst it may be more exciting as a DBA to rush around fixing broken databases, it is far better to forestall problems by making sure that your servers conform with best-practices. It is even better if you can also manage your servers centrally, and monitor that they are all adhering to company policies.

You may find that you only get to discover the various incorrect configurations in your servers as a consequence of investigating script failures, poor performance, or worse, a security breach; a process of exception-based management of Database servers. A better way is to prevent, or alert on, any deviations from the standard configurations by defining and applying standard configurations to groups of server instances. This is typically called intent-based management, and as you’ve probably guessed, that’s exactly what we can now achieve using the new policy-based management feature in SQL Server 2008.

Policy-based management for ensuring consistent configuration

Policy-Based Management takes a role that is analogous to Active Directory for SQL Server. Active Directory is used in simplifying the process of administering thousands of domain users and computers. Similarly, policy-based management is a way of ensuring that your SQL Server configurations are consistent, and like Active Directory, the approach becomes more valuable in as the number of server instances grows.

The entities managed by a policy are referred to as ‘targets’ and may be SQL Server instances, databases, tables, and so forth. In the example in figure 2, the target chosen for a table name policy is every table in every database.

Policies in action

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. As well as importing these policies, you can create new policies and export them to file for later use on other server instances. This enables you to Importing policies from file

Before the arrival of SQL Server 2008, tools such as Best Practices Analyzer and Baseline Security Analyzer could be used to check a SQL Server instance occasionally. 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. This provides a more active approach than is possible with Best Practices Analyzer, and it can be customized.

It is easy to import an existing policy using SQL Server Management Studio . You should 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 a number 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, there 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 that can be monitored by these 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 set to a percentage-based value rather than fixed size.

Although policies can be defined and executed against versions of SQL Server prior to 2008, there are some restrictions, and we’ll cover these, and some workarounds, later in this article.

In the example shown in figure 5, 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: it will be enabled on import if the policy is enabled in the definition file. Alternatively, we can explicitly enable or disable the policy as part of the import process.

Full article...