Pro SQL Server 2008 Policy-Based Management

✍ Scribed by Ken Simmons, Colin Stasiuk, Jorge Segarra

• Publisher: Apress
• Year: 2010
• Tongue: English
• Leaves: 270
• Category: Library

✦ Synopsis

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 needsWhat you’ll learn Learn about policy-based management and why you should implement it. Create policies to maintain a consistent, predictable environment throughout your organization. Audit organizational compliance with policies automatically, across any number of servers. Help ensure compliance with PCI rules, HIPAA rules, and other government regulations. Configure and receive alerts for policy violations.* View and report on the state of your policies.Who this book is forThis book is for database administrators who wish to apply consistent management practices across dozens and even hundreds of database servers

✦ Table of Contents

Prelim......Page 1
Contents at a Glance......Page 6
Contents......Page 7
About the Authors......Page 14
About the Technical Reviewers......Page 15
Acknowledgments......Page 16
How This Book Is Structured......Page 17
Contacting the Authors......Page 18
Why Use Policy-Based Management?......Page 19
Policy-Based Management Requirements......Page 20
Targets......Page 21
Facets......Page 22
Conditions......Page 23
Policies......Page 24
Evaluation Modes......Page 25
Server Restrictions......Page 26
Central Management Servers......Page 27
Enterprise Policy Management Framework......Page 28
Summary......Page 29
Creating a Condition......Page 31
Creating a Policy......Page 34
Viewing Dependent Policies......Page 39
Importing Policies......Page 42
Exporting Policies......Page 45
Exporting Existing Policies......Page 46
Exporting Current State As Policy......Page 51
Creating Policies with T-SQL......Page 53
Creating Policy Categories......Page 55
Subscribing to Categories......Page 57
Creating Advanced Conditions......Page 58
Defining Conditions for System Databases......Page 62
Summary......Page 65
Evaluation Modes......Page 67
Evaluating a Single Policy on Demand......Page 68
Evaluating Multiple Policies on Demand......Page 73
Evaluating Policies Against a Different Instance......Page 75
Evaluation on Schedule......Page 78
Creating a Schedule......Page 79
Adding Policies to an Existing Schedule......Page 84
Evaluation on Change: Log Only......Page 85
Evaluation on Change: Prevent......Page 92
Using a Central Management Server......Page 94
Creating a Central Management Server......Page 95
Creating a Central Management Server Group......Page 96
Registering a Server to a Group......Page 98
Importing Registered Servers and Groups......Page 99
Evaluating Policies against a Central Management Server Group......Page 102
Summary......Page 105
Using T-SQL......Page 107
Using SQL Server Management Objects......Page 109
Interrogating for Members and Properties......Page 111
Invoking a Policy from a File......Page 113
Getting Detailed Results......Page 114
Invoking a Policy Defined on the Server......Page 115
Invoking Multiple Policies from the Cmdlet......Page 116
Invoking a Category of Policies from the File System......Page 117
Invoking a Category of Policies from an Instance......Page 118
Querying and Storing Policy Execution Results......Page 119
Loading Policy Evaluation History......Page 120
Querying the History......Page 121
Evaluating Against a Central Management Server......Page 123
Summary......Page 126
Setting Up Database Mail......Page 127
Testing Database Mail......Page 129
Cleaning Up Database Mail History......Page 131
Creating SQL Server Agent Operators......Page 134
Enabling SQL Server Agent Notifications......Page 136
Creating Alerts......Page 138
Viewing History Based on Policies......Page 144
Viewing History Based on Objects......Page 145
General Troubleshooting......Page 147
Summary......Page 148
Policy-Based Management Properties......Page 149
On Demand......Page 152
On Schedule......Page 153
Policy-Based Management Security Issues......Page 154
Tables......Page 155
Contents of System Policy Tables......Page 156
syspolicy_conditions......Page 159
syspolicy_policies......Page 160
syspolicy_policy_execution_history......Page 161
syspolicy_target_sets......Page 162
Combining Views......Page 163
Stored Procedures......Page 164
Summary......Page 166
A DBA Checklist......Page 167
Database Free Space......Page 168
Successful Transaction Log Backups......Page 171
SQL Server Agent Is Running......Page 176
All SQL Server Agent Jobs Have Notification on Failure......Page 178
Data Purity Flag Enabled......Page 181
Best Practices Policies......Page 184
Summary......Page 186
EPM Framework Prerequisites......Page 187
The Setup Script......Page 188
The PowerShell Script......Page 189
Reporting Services Reports......Page 193
Viewing EPM Framework Reports......Page 197
Automating the EPM Framework......Page 200
Summary......Page 201
Compliance Overview......Page 203
Gramm-Leach-Bliley Act......Page 204
Payment Card Industry Data Security Standard......Page 205
Server Configuration......Page 206
Service Account......Page 207
Log Retention......Page 209
Surface Area Configuration......Page 212
Security......Page 213
Administrative Accounts......Page 214
Disabling the sa Login......Page 215
Best Practice Security Policies......Page 216
Transparent Data Encryption......Page 217
Extensible Key Management......Page 219
Auditing......Page 220
Login Auditing......Page 221
Default Trace......Page 222
Summary......Page 224
SQL Server Web Sites......Page 225
White Papers......Page 226
Free Training Events......Page 227
Social Networking......Page 228
Webcasts......Page 229
Paid Support......Page 230
Summary......Page 231
Best Practice Policy Descriptions......Page 233
Best Practice Policy Conditions and Facets......Page 248
B......Page 253
C......Page 254
D......Page 255
E......Page 257
F......Page 258
L......Page 259
M......Page 260
N......Page 261
P......Page 262
S......Page 265
T......Page 268
V......Page 269
X......Page 270