book

Pro SQL Server 2008 Policy-Based Management

Name: Pro SQL Server 2008 Policy-Based Management
ISBN: 9781430229100

by Ken Simmons, Jorge Segarra, Colin Stasiuk

April 2010

Intermediate to advanced

268 pages

5h 20m

English

Apress

Read now

Unlock full access

Copyright
About the Authors
About the Technical Reviewers
Acknowledgments
Introduction
How This Book Is StructuredPrerequisitesContacting the Authors
1. Introduction to Policy-Based Management
1.1. What Is Policy-Based Management?1.2. Why Use Policy-Based Management?1.3. Policy-Based Management Requirements1.4. Policy-Based Management Components1.4.1. Targets1.4.2. Facets1.4.3. Conditions1.4.4. Policies1.5. Policy Behavior1.5.1. Evaluation Modes1.5.2. Server Restrictions1.6. Policy Management1.6.1. Categories1.6.2. Central Management Servers1.6.3. Enterprise Policy Management Framework1.6.4. Alerts1.7. Summary
2. Creating Policies
2.1. Manually Creating Policies2.1.1. Creating a Condition2.1.2. Creating a Policy2.1.3. Viewing Dependent Policies2.2. Importing Policies2.3. Exporting Policies2.3.1. Exporting Existing Policies2.3.2. Exporting Current State As Policy2.4. Creating Policies with T-SQL2.5. Managing Policy Categories2.5.1. Creating Policy Categories2.5.2. Subscribing to Categories2.6. Creating Advanced Conditions2.7. Defining Conditions for System Databases2.8. Summary
3. Evaluating Policies
3.1. Evaluation Modes3.2. Evaluation on Demand3.2.1. Evaluating a Single Policy on Demand3.2.2. Evaluating Multiple Policies on Demand3.2.3. Evaluating Policies Against a Different Instance3.3. Evaluation on Schedule3.3.1. Creating a Schedule3.3.2. Adding Policies to an Existing Schedule3.4. Evaluation on Change: Log Only3.5. Evaluation on Change: Prevent3.6. Using a Central Management Server3.6.1. Creating a Central Management Server3.6.2. Creating a Central Management Server Group3.6.3. Adding Servers to Central Management Server Groups3.6.3.1. Registering a Server to a Group3.6.3.2. Importing Registered Servers and Groups3.6.4. Evaluating Policies against a Central Management Server Group3.7. Summary
4. Policy-Based Management Using PowerShell
4.1. Creating a Basic PowerShell Script4.1.1. Using T-SQL4.1.2. Using SQL Server Management Objects4.1.3. Interrogating for Members and Properties4.2. Running a Policy Against a SQL Server Instance4.2.1. Invoking a Policy from a File4.2.2. Getting Detailed Results4.2.3. Invoking a Policy Defined on the Server4.3. Running Multiple Policies Against a SQL Server Instance4.3.1. Invoking Multiple Policies from the Cmdlet4.3.2. Invoking a Category of Policies from the File System4.3.3. Invoking a Category of Policies from an Instance4.4. Querying and Storing Policy Execution Results4.4.1. Creating a Staging Table4.4.2. Loading Policy Evaluation History4.4.3. Querying the History4.5. Evaluating Against a Central Management Server4.6. Summary
5. Receiving Alerts for Policy Violations
5.1. Configuring Database Mail5.1.1. Setting Up Database Mail5.1.2. Testing Database Mail5.1.3. Cleaning Up Database Mail History5.2. Creating SQL Server Agent Operators5.3. Enabling SQL Server Agent Notifications5.4. Creating Alerts5.5. Troubleshooting Policies5.5.1. Viewing Policy History5.5.2. Viewing History Based on Policies5.5.3. Viewing History Based on Objects5.5.4. General Troubleshooting5.6. Summary

6. Policy-Based Management Internals
6.1. Policy-Based Management Properties6.2. Policy-Based Management Architecture6.2.1. On Demand6.2.2. On Change: Prevent6.2.3. On Change: Log Only6.2.4. On Schedule6.3. Policy-Based Management Security Issues6.4. Policy-Based Management Tables and Views6.4.1. Tables6.4.1.1. Contents of System Policy Tables6.4.1.2. Checking for New Tables6.4.2. Views6.4.2.1. syspolicy_conditions6.4.2.2. syspolicy_configuration6.4.2.3. syspolicy_object_sets6.4.2.4. syspolicy_policies6.4.2.5. syspolicy_policy_categories6.4.2.6. syspolicy_policy_category_subscriptions6.4.2.7. syspolicy_policy_execution_history6.4.2.8. syspolicy_policy_execution_history_details6.4.2.9. syspolicy_system_health_state6.4.2.10. syspolicy_target_set_levels6.4.2.11. syspolicy_target_sets6.4.2.12. Combining Views6.4.2.13. Checking for New Views6.5. Stored Procedures6.6. Summary
7. Practical Uses of Policy-Based Management
7.1. A DBA Checklist7.2. Custom Policies7.2.1. Database Free Space7.2.2. Successful Transaction Log Backups7.2.3. SQL Server Agent Is Running7.2.4. All SQL Server Agent Jobs Have Notification on Failure7.2.5. Data Purity Flag Enabled7.3. Best Practices Policies7.4. Summary
8. Reporting
8.1. EPM Framework Prerequisites8.2. Setting Up the EPM Framework8.2.1. The Setup Script8.2.2. The PowerShell Script8.2.3. Reporting Services Reports8.3. Viewing EPM Framework Reports8.4. Automating the EPM Framework8.5. Summary
9. Enforcing Compliance
9.1. Compliance Overview9.2. Compliance Regulations9.2.1. Gramm-Leach-Bliley Act9.2.2. The Sarbanes-Oxley Act9.2.3. Health Insurance Portability and Accountability Act9.2.4. Payment Card Industry Data Security Standard9.3. Server Configuration9.3.1. Service Account9.3.2. Log Retention9.3.3. Surface Area Configuration9.4. Security9.4.1. Administrative Accounts9.4.1.1. Removing the Builtin\Administrators Login9.4.1.2. Disabling the sa Login9.4.2. Best Practice Security policies9.5. Encryption9.5.1. Transparent Data Encryption9.5.2. Extensible Key Management9.5.3. Best Practice Encryption Policies9.6. Auditing9.6.1. SQL Server Audit9.6.2. Login Auditing9.6.3. Default Trace9.6.4. Best Practice Audit Policy9.7. Summary
10. Where to Go from Here
10.1. Upcoming Releases10.2. SQL Server Web Sites10.3. Blogs10.4. White Papers10.5. Podcasts10.6. Free Training Events10.7. Social Networking10.8. Microsoft Support Options10.8.1. SQL Server Books Online10.8.2. Webcasts10.8.3. SQL Server Troubleshooting and Support Resources10.8.4. Microsoft Technical Communities10.8.5. Paid Support10.9. Summary
A. Microsoft SQL Server Best Practice Policies
A.1. Best Practice Policy DescriptionsA.2. Best Practice Policy Conditions and Facets

Overview

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781430229100Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills