SQL Server 2008 R2 : Policy-Based Management Concepts

Introduction to Policy-Based Management

A data explosion has been occurring over the past several years. In a 2006 study, International Data Corporation (IDC; http://www.idc.com) reported that 5 exabytes of digital media (5 billion gigabytes) were stored in 2003, and in 2006 this had ballooned to 161 exabytes. Not only is more data being stored, but users are accessing more data than before. Part of this data growth is a result of the need for business intelligence (BI) systems to deliver actionable insights becoming more critical in the enterprise. Obtaining these insights requires large data volumes for trending and forecasting. As a result, data warehouses are becoming more critical in every enterprise.

This data explosion frequently results in a proliferation of SQL Servers.

Essentially, DBAs are being required to do more, frequently with less. In addition, the increasing complexities of the SQL Server product set are forcing DBAs to focus on efficient, scalable management and standardization. Due to the large numbers of SQL Servers involved, management by automation becomes critical as well to lessen the administrative burden. Monitoring also becomes more important to provide proactive support.

A well-managed SQL Server enterprise that follows best practices offers the following advantages:

• Standardization— Every SQL Server will have a common disk layout and settings, as well as consistent naming standards. As a result, DBAs moving from one SQL Server to another will not be surprised by different disk layouts or unusual settings that could account for a performance problem.

• Best practices— Microsoft internal studies have shown that 80% of the support calls to their Customer Service and Support (CSS) could have been avoided if the customer had been following best practices. Best practices not only offer performance advantages but also lead to fewer failure events caused by poorly configured SQL Servers, and security breaches due to SQL Servers that have not been hardened (security holes not locked down).

• Ease of deployment— A well-managed data center will have automated procedures for building SQL Servers (that is, unattended installations using configuration files) that require less time to build and minimal administrative interaction, resulting in fewer mistakes in a build and a reduction in administrative tasks.

• Regulatory compliance— By maintaining controlled and standardized settings, organizations can easily adhere to the demanding requirements of regulations such as Sarbanes-Oxley, the Health Insurance Portability and Accountability Act (HIPAA), and Payment Card Industry (PCI) standards.

The intent of Policy-Based Management is to provide a management framework that allows DBAs to automate management in their enterprise according to their own set of predefined standards. By implementing Policy-Based Management within a SQL Server infrastructure, organizations can reap the following benefits: total cost of ownership associated with managing SQL Server systems will be reduced, configuration changes to the SQL Server system can be monitored, unwanted system configuration changes can be prevented, and policies will ensure compliance.

The stated goals of Policy-Based Management fall into three categories:

• Management by intent— Allows DBAs to enforce standards and best practices from the start rather than in response to a performance problem or failure event

• Intelligent monitoring— Allows DBAs to detect changes that have been made to their SQL Server environments that deviate from the desired configuration

• Virtualized management— Provides a scalable framework that allows for management across the enterprise

Microsoft SQL Server 2008 and SQL Server 2008 R2 also ship with several predefined policies. These policies are not automatically imported into a default installation of SQL Server 2008. However, you can manually import them into SQL Server and use them as is or as a foundation for defining your own similar policies. These sample policies can be found in C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033. Note that there are also policies for Reporting Services and Analysis Services, which can be found in the ReportingServices and AnalysisServices subdirectories of the Policies directory. Also note that Policy-Based Management can be used to manage SQL 2005 and 2000 servers.

Policy-Based Management Concepts

Before we start learning about enforcing Policy-Based Management, there are a few key concepts DBAs must understand. These concepts include

• Facets

• Conditions

• Policies

• Categories

• Targets

• Execution mode

• Central Management Servers

Facets

A facet is a logical grouping of predefined SQL Server 2008 configuration settings. When a facet is coupled with a condition, a policy is formed and can be applied to one or more SQL Server instances and systems. Common facets include Surface Area Configuration, Server Audit, Database File, and Databases. Table 1 illustrates the complete list of predefined facets that can be selected, along with an indication of how each facet can be automated. Check On Schedule uses a SQL Server Agent job to evaluate a policy. Check On Change uses event notification to evaluate based on when changes occur. Facets are included with SQL Server 2008 and cannot be modified.

The complete list of facets can be viewed in SQL Server 2008 Management Studio by expanding the Management folder, the Policy-Based Management node, and then the Facets folder. Alternatively, to view facets applied to a specific database, you can right-click the database and select Facets.

Conditions

A condition is a Boolean expression that dictates an outcome or desired state of a specific management condition, also known as a facet. Condition settings are based on properties, comparative operators, and values such as String, equal, not equal, LIKE, NOT LIKE, IN, or NOT IN. For example, a check condition could verify that data and log files reside on separate drives, that the state of the database recovery model is set to Full Recovery, that database file sizes are not larger than a predefined value, and that database mail is disabled.

Policies

A policy is a standard for a single setting of an object. It ultimately acts as a verification mechanism of one or more conditions of the required state of SQL Server targets. Typical scenarios for creating policies include imposing Surface Area Configuration settings, enforcing naming conventions on database objects, enforcing database and transaction log placement, and controlling recovery models. As mentioned earlier, a tremendous number of policies can be created against SQL Server 2008 systems. Surface Area Configurations are a very common policy, especially because the SQL Server 2005 Surface Area Configuration tool has been deprecated in SQL Server 2008.

Categories

Microsoft recognized that although you may want to implement a set of rigid standards for your internal SQL Server development or deployments, your enterprise may have to host third-party software that does not follow your standards. Although your internally developed user databases will subscribe to your own policies, the third-party user applications will subscribe to their own categories. To provide flexibility, you can select which policies you want a table, database, or server to subscribe to and group them into groups called categories, and then have a database subscribe to a category and unsubscribe from a group of other policies if necessary. A policy can belong to only one policy category.

Targets

A target is one or more SQL Server instances, databases, or database objects that you want to apply your categories or policies to. Targets can be only SQL Server 2008 R2, 2008, 2005, or 2000 systems. All targets in a server instance form a target hierarchy. A target set is the set of targets that results from applying a set of target filters to the target hierarchy—for example, all the tables in a database contained in a specific schema.

Execution Modes

When you are implementing policies, there are three types of execution modes. The On Change mode has two variations:

• On Demand— The On Demand policy ensures that a target or targets are in compliance. This task is invoked manually by right-clicking on the policy in the Management folder, Policy Management folder, Policy folder, and selecting Evaluate. The policy is not enforced and is only verified against all targets that have been subscribed to that policy. You can evaluate a policy also by right-clicking on the database and selecting Policies and Evaluate.

• On Schedule— Policies can be evaluated on a schedule. For example, a policy can be scheduled to check all SQL Server 2008 systems once a day. If any anomalies arise, these out-of-compliance policies are logged to a file. This file should be reviewed on a periodic basis. In addition, whenever a policy fails, the complete tree in SQL Server Management Studio displays a downward-pointing arrow next to the policy, as shown in Figure 1.

  Figure 1. SQL Server management tree illustrating failed policies for table name.

  

• On Change Prevent— The On Change Prevent execution mode prevents changes to server, server object, database, or database objects that would make them out of compliance. For example, if you select a policy that restricts table names to only those that begin with the prefix tbl, and you attempt to create a table called MyTable, you get the following error message, and your table is not be created:

  Policy 'table name' has been violated by
  '/Server/(local)/Database/iFTS/Table/dbo.mytable'.
  This transaction will be rolled back.
  Policy description: ''
  Additional help: '' : ''.
  Msg 3609, Level 16, State 1, Procedure sp_syspolicy_
  dispatch_event, Line 50
  The transaction ended in the trigger.
  The batch has been aborted.
  

• On Change Log Only— If you select On Change Log Only, a policy condition that is evaluated as failed is logged in the SQL Server Error log. The change does not prevent out-of-compliance changes.

Central Management Servers

In large enterprises, organizations most likely have more than one SQL Server system they want to effectively manage from a Policy-Based Management perspective. Therefore, if DBAs want to implement policies to multiple servers, they have two options. The first option includes exporting the policy and then importing it into different SQL Server systems. After the policy is imported, it must be configured to be evaluated on demand, on schedule, or on change.

The second option includes creating one or more Central Management Servers in SQL Server 2008. Basically, by registering one or more SQL Servers with a Central Management Server, a DBA can deploy multiserver policies and administration from a central system.

For example, you could create two Central Management Servers, one called OLAP and another called OLTP, and then register servers into each Central Management Server, import the different policies into each Central Management Server, and then evaluate the polices on each different Central Management Server. So, on your OLTP Central Management Server, the servers OLTP1, OLTP2, OLTP3, which are registered in the OLTP Central Management Server, would have the OLTP policies evaluated on them.

Creating a Central Management Server

Follow these steps to register a Central Management Server:

Registering SQL Server Instances in a Central Management Server

The next task registers SQL Server instances to be associated with a Central Management Server. The following steps outline this task:

Figure 2 illustrates a Central Management Server with one Server Group and two SQL Server instances registered.

Importing and Evaluating Polices to the Central Management Server

After the Central Management Server is established, the Server Group is created, and the desired SQL Server instances are registered, it is time to import and evaluate policies. You can import policies for multiple instances by right-clicking the Central Management Server or Server Group and selecting Import Policies. After the policies are imported, the next step is to evaluate the policies by right-clicking the Central Management Server or Server Group and selecting Evaluate. The output indicates the status of policies associated with all the SQL Server instances associated with the Central Management Server or Server Group.