Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 282

The best practices are implemented as policies that are based on a predefined list of SQL
Server recommendations as policy files. The evaluation of policies against a target set
includes instances, databases, or database objects. The policies are used to determine
whether there are potential issues in the database environment or instance meets best
practices guidelines and recommendations.
Once a policy or multiple policies are implemented, they can be evaluated on the target
system. The evaluation mode can be automated or performed manually by the DBA to reduce
any manual intervention in administering and increase the ease of deployment.
In this recipe, we will go through the process of implementing policy-based management
features on an existing SQL Server 2008 R2 instance.
Getting ready
SQL Server 2008 R2 fully supports the policy-based management and the database engine
is shipped with several predefined policies that are stored under %:\Program Files\
Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033\% .
Furthermore, the policies for Reporting Services and Analysis Services are also stored under
the subdirectory of the Policies directory, but the PBM is unavailable for SSRS and SSAS
services. Only SQL Express and SQL Express with Advanced Services do not have the capability
of policy automation, as there is no SQL Server Agent service available for these editions.
The generic terms and concepts used in Policy-Based management are facets, conditions,
policies, categories, targets, and execution mode. Let us look at how to implement policy-
based management. In this recipe, we will implement a Database Security facet to expose
properties of the database object that encapsulate security aspects of a database.
Before implementing this facet, let's create a user and set a database owner on the
AdventureWorks2008R2 database:
USE [master]
GO
CREATE LOGIN [DBIA-SSQA\tseuG] FROM WINDOWS WITH DEFAULT_
DATABASE=[master]
GO
USE [Adventureworks2008R2]
GO
CREATE USER [DBIA-SSQA\tseuG] FOR LOGIN [DBIA-SSQA\tseuG]
GO
USE [Adventureworks2008R2]
GO
EXEC sp_addrolemember N'db_owner', N'DBIA-SSQA\tseuG'
GO