SQL Server 2008 Best Practice

  • Article

In SQL Server 2000 and 2005 there is a really useful utility called the Best Practice Analyzer which you can download here.  Essentially it looks at your database configuration and matches this against a set of rules to inform you you of what improvements to make, based on experience gained by Microsoft Consultants and Premier Field Engineers.

However this tool is not going to exist as such in SQL Server 2008. Instead there will be a set of policies that you can import and use in Policy Management (btw this feature was previously known as the Declarative Management Framework -DMF).

Policies comprise three things:

  • A Facet. This is what you are trying to manage e.g. the CLR.
  • A Condition, which you want the facet to be in e.g. you want the CLR to be on because you have written some cool string functions using the CLR that you need to implement.  
  • An execution mode.   This is how you want to test for the condition and the four options are:
    • On Demand. This mode evaluates the policy when directly specified by the user.
    • On Change - Prevent. This automated mode uses DDL triggers to prevent policy violations.
    • On Change - Log Only. This automated mode uses event notification to evaluate a policy when a relevant change occurs and logs policy violations.
    • On Schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy. The mode logs policy violations.

You can export and import policies as xml files and then apply them to all of your servers to maintain consistency among them, but that is a needlessly manual exercise and there is a better way, by using a configuration server to run commands against a group of SQL Server instances (which I will cover in a later post).

If you have installed CTP6 of SQL Server 2008 you will have access to a whole bunch of policies which you can import and use. They’re in:

Program Files\Microsoft SQL Server\100\Tools\Policies

It is these polices which replace the Best Practices Analyzer.  Policies also partly replace the Surface Area Configuration introduced in SQL Server 2005, the ability to turn the CLR off and on being a good example of this. 

This policy based approach is very similar to how systems administrators work on the windows servers that SQL Server runs on and thus provides a consistent way of working wether you have to do both roles in a smaller organisation or collaborate with a systems administrator in a larger one.

Finally to answer one FAQ, You can only use Policy Management on SQL Server 2008 i.e. NOT SQL Server 2000 or 2005

Technorati Tags: SQL Server 2008,Policy Management