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

1. ed
ISBN: 978-1-4302-2911-7
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 268 Seiten

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

Ken Simmons is a database administrator and developer specializing in Microsoft SQL Server and .NET. He is currently working with a global electronic payment service provider in Columbus, Georgia. He has been working in the IT industry since 2000 and holds certifications for MCP, MCAD, MCSD, MCDBA, and MCTS for SQL 2005. Ken is highly active in the online community and often participates in the SQL forums on MSDN and SQLServerCentral. He enjoys sharing tips that he discovers by writing articles for SQLServerCentral and MSSQLTips.com. He has also formed an SQL Server Users Group in Columbus, Georgia, for SQL Server professionals to come together to share knowledge and resources. When he is not working, Ken enjoys traveling with his wife, Susan, and son, Nathan, and can often be found on a cruise ship, at a Disney resort, or at the beach in his hometown of Pensacola, Florida.

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