New book: Microsoft SQL Server 2012 Pocket Consultant

Here it is! The Microsoft SQL Server 2012 Pocket Consultant (ISBN 9780735663763; 592 pages) is a concise and easy-to-use reference from William R. Stanek. As so many of you know, William is just the best at delivering what you need to know without all the frills. If you are a SQL Server administrator, this book will help you save time, minimize system downtime, and get the job done!

Below is an excerpt from Chapter 3: "Implementing Policy-Based Management."

CHAPTER 3

Implementing Policy-Based
Management

• Introducing Policy-Based Management 77
• Working with Policy-Based Management 80
• Configuring Central Management Servers 88
• Managing Policies Throughout the Enterprise 92

Policy-Based Management is an extensible and scalable configuration ­framework that you can use to manage servers, databases, and other objects in your data environments. As an administrator, you need to be very familiar with how Policy-Based Management technology works, and that’s exactly what this chapter is about. 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.

MORE INFO   Put another way, policy-based management is explicit declarative management that you configure using facets, conditions, categories, and targets. Facets are fixed lists of things you can set policy on. Conditions determine when policy applies. Categories group policies together for enforcement. Targets determine to which objects policies apply.

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

NOTE All facets support the On Demand and On Schedule modes. Facets support the On Change: Log Only mode only if the change of the facet state can be captured by related system events. Facets support the On Change: Prevent mode only if there is transactional support for the DDL statements that change the facet state. Only automatic policies can be enabled or disabled.

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.

REAL WORLD   To be notified when messages from automatically executed policies are written to the event logs, you can create alerts to detect these messages and perform necessary actions. The alerts should detect the messages according to their message number. Look for message numbers 34050, 34051, 34052, and 34053. You can configure alerts as discussed in the “Managing Alerts” section in Chapter 10, ­“Automating and Maintaining SQL Server 2012.”

When policies are executed automatically, they execute as a member of the sysadmin role. This allows the policy to write entries to the event logs and raise an alert. When policies are evaluated on demand, they execute in the security context of the current user. To write to the event log, the user must have ALTER TRACE permissions or be a member of the sysadmin fixed server role; otherwise, Windows will not write to the event log and will not fire an alert.

Working with Policy-Based Management

You must be a member of the PolicyAdministratorRole role in the msdb database to configure Policy-Based Management settings. This role has complete control of all policies and can create policies and conditions, edit policies and conditions, and enable or disable policies.

When working with policies, keep the following in mind:

• A system administrator or database owner can subscribe a database to a policy or policy group.
• On-demand policy execution occurs in the security context of the user.
• Members of the PolicyAdministratorRole role can create policies that they do not have permission to execute on an ad hoc basis.
• Members of the PolicyAdministratorRole role can enable or disable policies.
• Policies that are in the On Schedule mode use SQL Server Agent jobs that are owned by the sa login.

Although you can manage policies for each instance of SQL Server, you’ll likely reuse policies you’ve defined and then apply them to other instances of SQL Server. With Policy-Based Management, you can apply policies to multiple instances of SQL Server in several ways. As discussed in the “Importing and Exporting ­Policies” ­section later in this chapter, you can export the policies you’ve defined on a ­particular instance of SQL Server and then import the policies on another instance of SQL Server. During the import process, you can specify whether policies are ­enabled or disabled and whether to preserve the exported state of the policies.

Being able to export and import policies is useful. However, you don’t ­necessarily need to move policies around to enforce the policies on multiple ­computers ­running SQL Server. Instead, you can manage policies by using a central ­management server. A central management server is a special type of ­configuration server that is ­responsible for monitoring and enforcing policy on any instance of SQL Server ­registered as a subordinate server. As discussed in the “Configuring ­Central ­Management Servers” section later in this chapter, you designate central ­management servers and their subordinates by using SQL Server Management ­Studio. Because the central management architecture is already an execution ­environment for Transact-SQL (T-SQL) statements related to policies, you can ­execute T-SQL statements on multiple instances of SQL Server at the same time from a central management server.

REAL WORLD    Generally, SQL Server does more validation in the graphical user interface (GUI) than in the application programming interface (API). As a result, you may be allowed to create a policy in T-SQL but be restricted from creating the same policy in the GUI. Why? Because SQL Server tries to prevent you from creating policies that might impact performance when working in the GUI, while making it possible for advanced users to be able to work around this.

Because SQL Server stores policy-related data in the msdb database, you should back up msdb after you change conditions, policies, or categories. Policy history for policies evaluated in the current instance of the Database Engine is maintained in msdb system tables. Policy history for policies applied to other instances of the ­Database Engine or applied to Reporting Services or Analysis Services is not retained.

As summarized in Table 3-2, SQL Server 2012 includes several sets of predefined policies, including those for the Database Engine, Analysis Services, and Reporting Services. By default, the policies are stored as XML files in the following locations, and you must import them into SQL Server:

• Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033
• Micros  oft SQL Server\110\Tools\Policies\AnalysisServices\1033
• Microsoft SQL Server\110\Tools\Policies\ReportingServices\1033

NOTE   On 64-bit computers, policies are located under Program Files (x86) rather than Program Files when you install SQL Server in the default file system location. ­Surface area configuration is discussed in the “Managing SQL Server Component ­Feature Access” section in Chapter 2, “Managing SQL Server Services and Clients.”

TABLE 3-2   Important Predefined Policies for SQL Server 2012

Real World   Guest Permissions policy is meant for non-system databases only. Disabling guest access doesn’t apply to master, msdb, and tempb system databases. Some SQL Server features require the guest user to be enabled in the msdb database. If guest access is disabled in msdb, you may receive error 916 when you try to connect to SQL Server using SQL Server Management Studio or other applications.