O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Pro SQL Server 2008 Policy-Based Management

Pro SQL Server 2008 Policy-Based Management

by Colin Stasiuk, Jorge Segarra, Ken Simmons
Publisher: Apress
Release Date: April 2010
ISBN: 9781430229100
View table of contents
Start reading

Book Description

Pro SQL Server 2008 Policy-Based Management is critical for database administrators seeking in-depth knowledge on administering servers using the new policy-based management features introduced in SQL Server 2008. This book will cover everything from a basic introduction to policy-based management to creating your own custom policies to enforce consistent rules across your organization.

  • Provides in-depth treatment of policy-based management in a single source

  • Provides practical usage scenarios for policy-based management

  • Provides guidance to help meet growing regulatory compliance needs

Table of Contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewers
  4. Acknowledgments
  5. Introduction
    1. How This Book Is Structured
    2. Prerequisites
    3. Contacting the Authors
  6. 1. Introduction to Policy-Based Management
    1. 1.1. What Is Policy-Based Management?
    2. 1.2. Why Use Policy-Based Management?
    3. 1.3. Policy-Based Management Requirements
    4. 1.4. Policy-Based Management Components
      1. 1.4.1. Targets
      2. 1.4.2. Facets
      3. 1.4.3. Conditions
      4. 1.4.4. Policies
    5. 1.5. Policy Behavior
      1. 1.5.1. Evaluation Modes
      2. 1.5.2. Server Restrictions
    6. 1.6. Policy Management
      1. 1.6.1. Categories
      2. 1.6.2. Central Management Servers
      3. 1.6.3. Enterprise Policy Management Framework
      4. 1.6.4. Alerts
    7. 1.7. Summary
  7. 2. Creating Policies
    1. 2.1. Manually Creating Policies
      1. 2.1.1. Creating a Condition
      2. 2.1.2. Creating a Policy
      3. 2.1.3. Viewing Dependent Policies
    2. 2.2. Importing Policies
    3. 2.3. Exporting Policies
      1. 2.3.1. Exporting Existing Policies
      2. 2.3.2. Exporting Current State As Policy
    4. 2.4. Creating Policies with T-SQL
    5. 2.5. Managing Policy Categories
      1. 2.5.1. Creating Policy Categories
      2. 2.5.2. Subscribing to Categories
    6. 2.6. Creating Advanced Conditions
    7. 2.7. Defining Conditions for System Databases
    8. 2.8. Summary
  8. 3. Evaluating Policies
    1. 3.1. Evaluation Modes
    2. 3.2. Evaluation on Demand
      1. 3.2.1. Evaluating a Single Policy on Demand
      2. 3.2.2. Evaluating Multiple Policies on Demand
      3. 3.2.3. Evaluating Policies Against a Different Instance
    3. 3.3. Evaluation on Schedule
      1. 3.3.1. Creating a Schedule
      2. 3.3.2. Adding Policies to an Existing Schedule
    4. 3.4. Evaluation on Change: Log Only
    5. 3.5. Evaluation on Change: Prevent
    6. 3.6. Using a Central Management Server
      1. 3.6.1. Creating a Central Management Server
      2. 3.6.2. Creating a Central Management Server Group
      3. 3.6.3. Adding Servers to Central Management Server Groups
        1. 3.6.3.1. Registering a Server to a Group
        2. 3.6.3.2. Importing Registered Servers and Groups
      4. 3.6.4. Evaluating Policies against a Central Management Server Group
    7. 3.7. Summary
  9. 4. Policy-Based Management Using PowerShell
    1. 4.1. Creating a Basic PowerShell Script
      1. 4.1.1. Using T-SQL
      2. 4.1.2. Using SQL Server Management Objects
      3. 4.1.3. Interrogating for Members and Properties
    2. 4.2. Running a Policy Against a SQL Server Instance
      1. 4.2.1. Invoking a Policy from a File
      2. 4.2.2. Getting Detailed Results
      3. 4.2.3. Invoking a Policy Defined on the Server
    3. 4.3. Running Multiple Policies Against a SQL Server Instance
      1. 4.3.1. Invoking Multiple Policies from the Cmdlet
      2. 4.3.2. Invoking a Category of Policies from the File System
      3. 4.3.3. Invoking a Category of Policies from an Instance
    4. 4.4. Querying and Storing Policy Execution Results
      1. 4.4.1. Creating a Staging Table
      2. 4.4.2. Loading Policy Evaluation History
      3. 4.4.3. Querying the History
    5. 4.5. Evaluating Against a Central Management Server
    6. 4.6. Summary
  10. 5. Receiving Alerts for Policy Violations
    1. 5.1. Configuring Database Mail
      1. 5.1.1. Setting Up Database Mail
      2. 5.1.2. Testing Database Mail
      3. 5.1.3. Cleaning Up Database Mail History
    2. 5.2. Creating SQL Server Agent Operators
    3. 5.3. Enabling SQL Server Agent Notifications
    4. 5.4. Creating Alerts
    5. 5.5. Troubleshooting Policies
      1. 5.5.1. Viewing Policy History
      2. 5.5.2. Viewing History Based on Policies
      3. 5.5.3. Viewing History Based on Objects
      4. 5.5.4. General Troubleshooting
    6. 5.6. Summary
  11. 6. Policy-Based Management Internals
    1. 6.1. Policy-Based Management Properties
    2. 6.2. Policy-Based Management Architecture
      1. 6.2.1. On Demand
      2. 6.2.2. On Change: Prevent
      3. 6.2.3. On Change: Log Only
      4. 6.2.4. On Schedule
    3. 6.3. Policy-Based Management Security Issues
    4. 6.4. Policy-Based Management Tables and Views
      1. 6.4.1. Tables
        1. 6.4.1.1. Contents of System Policy Tables
        2. 6.4.1.2. Checking for New Tables
      2. 6.4.2. Views
        1. 6.4.2.1. syspolicy_conditions
        2. 6.4.2.2. syspolicy_configuration
        3. 6.4.2.3. syspolicy_object_sets
        4. 6.4.2.4. syspolicy_policies
        5. 6.4.2.5. syspolicy_policy_categories
        6. 6.4.2.6. syspolicy_policy_category_subscriptions
        7. 6.4.2.7. syspolicy_policy_execution_history
        8. 6.4.2.8. syspolicy_policy_execution_history_details
        9. 6.4.2.9. syspolicy_system_health_state
        10. 6.4.2.10. syspolicy_target_set_levels
        11. 6.4.2.11. syspolicy_target_sets
        12. 6.4.2.12. Combining Views
        13. 6.4.2.13. Checking for New Views
    5. 6.5. Stored Procedures
    6. 6.6. Summary
  12. 7. Practical Uses of Policy-Based Management
    1. 7.1. A DBA Checklist
    2. 7.2. Custom Policies
      1. 7.2.1. Database Free Space
      2. 7.2.2. Successful Transaction Log Backups
      3. 7.2.3. SQL Server Agent Is Running
      4. 7.2.4. All SQL Server Agent Jobs Have Notification on Failure
      5. 7.2.5. Data Purity Flag Enabled
    3. 7.3. Best Practices Policies
    4. 7.4. Summary
  13. 8. Reporting
    1. 8.1. EPM Framework Prerequisites
    2. 8.2. Setting Up the EPM Framework
      1. 8.2.1. The Setup Script
      2. 8.2.2. The PowerShell Script
      3. 8.2.3. Reporting Services Reports
    3. 8.3. Viewing EPM Framework Reports
    4. 8.4. Automating the EPM Framework
    5. 8.5. Summary
  14. 9. Enforcing Compliance
    1. 9.1. Compliance Overview
    2. 9.2. Compliance Regulations
      1. 9.2.1. Gramm-Leach-Bliley Act
      2. 9.2.2. The Sarbanes-Oxley Act
      3. 9.2.3. Health Insurance Portability and Accountability Act
      4. 9.2.4. Payment Card Industry Data Security Standard
    3. 9.3. Server Configuration
      1. 9.3.1. Service Account
      2. 9.3.2. Log Retention
      3. 9.3.3. Surface Area Configuration
    4. 9.4. Security
      1. 9.4.1. Administrative Accounts
        1. 9.4.1.1. Removing the Builtin\Administrators Login
        2. 9.4.1.2. Disabling the sa Login
      2. 9.4.2. Best Practice Security policies
    5. 9.5. Encryption
      1. 9.5.1. Transparent Data Encryption
      2. 9.5.2. Extensible Key Management
      3. 9.5.3. Best Practice Encryption Policies
    6. 9.6. Auditing
      1. 9.6.1. SQL Server Audit
      2. 9.6.2. Login Auditing
      3. 9.6.3. Default Trace
      4. 9.6.4. Best Practice Audit Policy
    7. 9.7. Summary
  15. 10. Where to Go from Here
    1. 10.1. Upcoming Releases
    2. 10.2. SQL Server Web Sites
    3. 10.3. Blogs
    4. 10.4. White Papers
    5. 10.5. Podcasts
    6. 10.6. Free Training Events
    7. 10.7. Social Networking
    8. 10.8. Microsoft Support Options
      1. 10.8.1. SQL Server Books Online
      2. 10.8.2. Webcasts
      3. 10.8.3. SQL Server Troubleshooting and Support Resources
      4. 10.8.4. Microsoft Technical Communities
      5. 10.8.5. Paid Support
    9. 10.9. Summary
  16. A. Microsoft SQL Server Best Practice Policies
    1. A.1. Best Practice Policy Descriptions
    2. A.2. Best Practice Policy Conditions and Facets