Implementing Policy-Based Management in Microsoft SQL Server

• Introducing Policy-Based Management

• Working with Policy-Based Management

• Configuring Central Management Servers

• Managing Policies Throughout the Enterprise

If you haven’t worked with Policy-Based Management technology before, one thing you’ll notice immediately is that the technology is fairly advanced and has many features. To help you manage this complex technology, I’ll start with an overview of Policy-Based Management and then explore its components.

Introducing Policy-Based Management

Just about every administrative task you perform can be affected by the policy-based framework in some way. The policy-based framework provides the ability to define policies that apply to server instances, databases, and other objects in your data environments. You use these policies to help you control and manage the configuration of data services throughout the enterprise. Through intelligent monitoring and proactive responses, you can prevent changes that deviate from the configurations you specify and want. You also can scale management across multiple servers, which makes enforcing consistent configuration policies easier.

Within the policy-based framework, you use the following objects to configure policy management:

• Facet. Defines a management area within the policy-based framework. Each management area has a fixed set of related properties that you can configure. For example, the Backup Device facet has the following properties: BackupDeviceType, Name, PhysicalLocation, and SkipTapeLabel.

• Condition. Defines the permitted states for one or more properties of a single facet. For example, you can create a condition called Limit Backup Devices to specify that for the Backup Device facet, BackupDeviceType can be set to hard disk or tape and SkipTapeLabel should always be set to True.

• Policy. Contains a single condition that you want to enforce. For example, you can create a policy named Standard Backup Device Policy that assigns the Limit Backup Devices condition.

• Category. Contains one or more policies that you want to enforce together. For example, you can create a category named Standard DB Policies that contains all the standard policies that you want to enforce within your Microsoft SQL Server databases.

• Target. Defines the servers, databases, or other database objects to which policies are applied. For example, a target set could include all the databases on an instance of SQL Server.

You can create and manage policies in several ways. In SQL Server Management Studio, you can create policies from scratch or import existing policy files. The policy creation process includes the following steps:

1. Select a facet that contains the properties you want to configure.

2. Define a condition that specifies the permitted states of the facet.

3. Define a policy that contains the condition and sets one of the evaluation modes listed in Table 3-1.

4. Determine whether an instance of SQL Server is in compliance with the policy, and then take appropriate action.

For scripting, the Microsoft.SqlServer.Management.Dmf namespace contains classes that represent policy-based management objects. You use the root of this namespace, the PolicyStore class, to work with policies. Consider the following example:

$comp = get-content c:\data\servers.txt
$cn = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection
("server='DbServer85';Trusted_Connection=True")

$ps = new-object Microsoft.SQLServer.Management.DMF.PolicyStore($cn)

foreach ($c in $comp) { foreach ($p in $ps.Policies) {
 #Invoke-PolicyEvaluation }
}

Here, you get a list of servers that you want to work with from a text file and then configure a connection to SQL Server. Once you’re connected to SQL Server, you access the policy store and work with the policies on each server in your list.

Table 3-1 Evaluation Modes for Policy-Based Management

Policy categories apply to databases and servers. At the database level, database owners can subscribe a database to a set of policy categories, and those policies govern the database. By default, all databases implicitly subscribe to the default policy category. At the server level, you can apply policy categories to all databases.

You can mark categories as Active or Inactive at the server or database level. Although you can classify policies into different policy categories, a policy can belong only to one policy category.

All objects defined on a SQL Server instance form a target hierarchy. Within a policy, you define a target when you apply filters to the target hierarchy. For example, a target set with a large scope could include all the databases on an instance of SQL Server, while a target set with a small scope could include only the tables and indexes owned by the Sales schema in the Customers database.

The effective policies of a target are those policies that govern the target. For a policy to be effective with regard to a target, the policy must be enabled and the target must be subject to the policy. Within your data services environments, you enforce Policy-Based Management by using configuration servers. A designated configuration server is responsible for monitoring and enforcing policies as assigned. By default, each instance of SQL Server acts as its own configuration server. This means that each SQL Server instance normally handles its own policy monitoring and enforcement.