Simmons / Stasiuk / Segarra Pro SQL Server 2008 Policy-Based Management

1. Auflage 2010
ISBN: 978-1-4302-2911-7
Verlag: Apress
Format: PDF
Kopierschutz: Adobe DRM (»Systemvoraussetzungen)

E-Book, Englisch, 268 Seiten

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

Autoren/Hrsg.

Simmons, Ken

Stasiuk, Colin

Segarra, Jorge

Inhaltsverzeichnis

1;Contents at a Glance;5
2;Table of Contents;6
3;About the Authors;13
4;About the Technical Reviewers;14
5;Acknowledgments;15
6;Introduction;16
6.1;How This Book Is Structured;16
6.2;Prerequisites;17
6.3;Contacting the Authors;17
7;CHAPTER 1 Introduction to Policy-Based Management;18
7.1;What Is Policy-Based Management?;18
7.2;Why Use Policy-Based Management?;18
7.3;Policy-Based Management Requirements;19
7.4;Policy-Based Management Components;20
7.4.1;Targets;20
7.4.2;Facets;21
7.4.3;Conditions;22
7.4.4;Policies;23
7.5;Policy Behavior;24
7.5.1;Evaluation Modes;24
7.5.2;Server Restrictions;25
7.6;Policy Management;26
7.6.1;Categories;26
7.6.2;Central Management Servers;26
7.6.3;Enterprise Policy Management Framework;27
7.6.4;Alerts;28
7.7;Summary;28
8;CHAPTER 2 Creating Policies;29
8.1;Manually Creating Policies;29
8.1.1;Creating a Condition;29
8.1.2;Creating a Policy;32
8.1.3;Viewing Dependent Policies;37
8.2;Importing Policies;40
8.3;Exporting Policies;43
8.3.1;Exporting Existing Policies;44
8.3.2;Exporting Current State As Policy;49
8.4;Creating Policies with T-SQL;51
8.5;Managing Policy Categories;53
8.5.1;Creating Policy Categories;53
8.5.2;Subscribing to Categories;55
8.6;Creating Advanced Conditions;56
8.7;Defining Conditions for System Databases;60
8.8;Summary;63
9;CHAPTER 3 Evaluating Policies;64
9.1;Evaluation Modes;64
9.2;Evaluation on Demand;65
9.2.1;Evaluating a Single Policy on Demand;65
9.2.2;Evaluating Multiple Policies on Demand;70
9.2.3;Evaluating Policies Against a Different Instance;72
9.3;Evaluation on Schedule;75
9.3.1;Creating a Schedule;76
9.3.2;Adding Policies to an Existing Schedule;81
9.4;Evaluation on Change: Log Only;82
9.5;Evaluation on Change: Prevent;89
9.6;Using a Central Management Server;91
9.6.1;Creating a Central Management Server;92
9.6.2;Creating a Central Management Server Group;93
9.6.3;Adding Servers to Central Management Server Groups;95
9.6.3.1;Registering a Server to a Group;95
9.6.3.2;Importing Registered Servers and Groups;96
9.6.4;Evaluating Policies against a Central Management Server Group;99
9.7;Summary;102
10;CHAPTER 4 Policy-Based Management Using PowerShell;103
10.1;Creating a Basic PowerShell Script;103
10.1.1;Using T-SQL;103
10.1.2;Using SQL Server Management Objects;105
10.1.3;Interrogating for Members and Properties;107
10.2;Running a Policy Against a SQL Server Instance;109
10.2.1;Invoking a Policy from a File;109
10.2.2;Getting Detailed Results;110
10.2.3;Invoking a Policy Defined on the Server;111
10.3;Running Multiple Policies Against a SQL Server Instance;112
10.3.1;Invoking Multiple Policies from the Cmdlet;112
10.3.2;Invoking a Category of Policies from the File System;113
10.3.3;Invoking a Category of Policies from an Instance;114
10.4;Querying and Storing Policy Execution Results;115
10.4.1;Creating a Staging Table;116
10.4.2;Loading Policy Evaluation History;116
10.4.3;Querying the History;117
10.5;Evaluating Against a Central Management Server;119
10.6;Summary;122
11;CHAPTER 5 Receiving Alerts forPolicy Violations;123
11.1;Configuring Database Mail;123
11.1.1;Setting Up Database Mail;123
11.1.2;Testing Database Mail;125
11.1.3;Cleaning Up Database Mail History;127
11.2;Creating SQL Server Agent Operators;130
11.3;Enabling SQL Server Agent Notifications;132
11.4;Creating Alerts;134
11.5;Troubleshooting Policies;140
11.5.1;Viewing Policy History;140
11.5.2;Viewing History Based on Policies;140
11.5.3;Viewing History Based on Objects;141
11.5.4;General Troubleshooting;143
11.6;Summary;144
12;CHAPTER 6 Policy-Based Management Internals;145
12.1;Policy-Based Management Properties;145
12.2;Policy-Based Management Architecture;148
12.2.1;On Demand;148
12.2.2;On Change: Prevent;149
12.2.3;On Change: Log Only;149
12.2.4;On Schedule;149
12.3;Policy-Based Management Security Issues;150
12.4;Policy-Based Management Tables and Views;151
12.4.1;Tables;151
12.4.1.1;Contents of System Policy Tables;152
12.4.1.1.1;Checking for New Tables;155
12.4.2;Views;155
12.4.2.1;syspolicy_conditions;155
12.4.2.2;syspolicy_configuration;156
12.4.2.3;syspolicy_policies;156
12.4.2.4;syspolicy_policy_categories;157
12.4.2.5;syspolicy_policy_category_subscriptions;157
12.4.2.6;syspolicy_policy_execution_history;157
12.4.2.7;syspolicy_policy_execution_history_details;158
12.4.2.8;syspolicy_system_health_state;158
12.4.2.9;syspolicy_target_set_levels;158
12.4.2.10;syspolicy_target_sets;158
12.4.2.11;Combining Views;159
12.4.2.12;Checking for New Views;160
12.5;Stored Procedures;160
12.6;Summary;162
13;CHAPTER 7 Practical Uses of Policy-Based Management;163
13.1;A DBA Checklist;163
13.2;Custom Policies;164
13.2.1;Database Free Space;164
13.2.2;Successful Transaction Log Backups;167
13.2.3;SQL Server Agent Is Running;172
13.2.4;All SQL Server Agent Jobs Have Notification on Failure;174
13.2.5;Data Purity Flag Enabled;177
13.3;Best Practices Policies;180
13.4;Summary;182
14;CHAPTER 8 Reporting;183
14.1;EPM Framework Prerequisites;183
14.2;Setting Up the EPM Framework;184
14.2.1;The Setup Script;184
14.2.2;The PowerShell Script;185
14.2.3;Reporting Services Reports;189
14.3;Viewing EPM Framework Reports;193
14.4;Automating the EPM Framework;196
14.5;Summary;197
15;CHAPTER 9 Enforcing Compliance;198
15.1;Compliance Overview;198
15.2;Compliance Regulations;199
15.2.1;Gramm-Leach-Bliley Act;199
15.2.2;The Sarbanes-Oxley Act;200
15.2.3;Health Insurance Portability and Accountability Act;200
15.2.4;Payment Card Industry Data Security Standard;200
15.3;Server Configuration;201
15.3.1;Service Account;202
15.3.2;Log Retention;204
15.3.3;Surface Area Configuration;207
15.4;Security;208
15.4.1;Administrative Accounts;209
15.4.1.1;Removing the BuiltinAdministrators Login;210
15.4.1.2;Disabling the sa Login;210
15.4.2;Best Practice Security Policies;211
15.5;Encryption;212
15.5.1;Transparent Data Encryption;212
15.5.2;Extensible Key Management;214
15.5.3;Best Practice Encryption Policies;215
15.6;Auditing;215
15.6.1;SQL Server Audit;216
15.6.2;Login Auditing;216
15.6.3;Default Trace;217
15.6.4;Best Practice Audit Policy;219
15.7;Summary;219
16;CHAPTER 10 Where to Go from Here;220
16.1;Upcoming Releases;220
16.2;SQL Server Web Sites;220
16.3;Blogs;221
16.4;White Papers;221
16.5;Podcasts;222
16.6;Free Training Events;222
16.7;Social Networking;223
16.8;Microsoft Support Options;224
16.8.1;SQL Server Books Online;224
16.8.2;Webcasts;224
16.8.3;SQL Server Troubleshooting and Support Resources;225
16.8.4;Microsoft Technical Communities;225
16.8.5;Paid Support;225
16.9;Summary;226
17;APPENDIX Microsoft SQL Server Best Practice Policies;227
17.1;Best Practice Policy Descriptions;227
17.2;Best Practice Policy Conditions and Facets;242
18;Index;246