Pro SQL Server 2008 Policy-Based Management (eBook)

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.

Contents at a Glance 5
Table of Contents 6
About the Authors 13
About the Technical Reviewers 14
Acknowledgments 15
Introduction 16
How This Book Is Structured 16
Prerequisites 17
Contacting the Authors 17
CHAPTER 1 Introduction to Policy-Based Management 18
What Is Policy-Based Management? 18
Why Use Policy-Based Management? 18
Policy-Based Management Requirements 19
Policy-Based Management Components 20
Targets 20
Facets 21
Conditions 22
Policies 23
Policy Behavior 24
Evaluation Modes 24
Server Restrictions 25
Policy Management 26
Categories 26
Central Management Servers 26
Enterprise Policy Management Framework 27
Alerts 28
Summary 28
CHAPTER 2 Creating Policies 29
Manually Creating Policies 29
Creating a Condition 29
Creating a Policy 32
Viewing Dependent Policies 37
Importing Policies 40
Exporting Policies 43
Exporting Existing Policies 44
Exporting Current State As Policy 49
Creating Policies with T-SQL 51
Managing Policy Categories 53
Creating Policy Categories 53
Subscribing to Categories 55
Creating Advanced Conditions 56
Defining Conditions for System Databases 60
Summary 63
CHAPTER 3 Evaluating Policies 64
Evaluation Modes 64
Evaluation on Demand 65
Evaluating a Single Policy on Demand 65
Evaluating Multiple Policies on Demand 70
Evaluating Policies Against a Different Instance 72
Evaluation on Schedule 75
Creating a Schedule 76
Adding Policies to an Existing Schedule 81
Evaluation on Change: Log Only 82
Evaluation on Change: Prevent 89
Using a Central Management Server 91
Creating a Central Management Server 92
Creating a Central Management Server Group 93
Adding Servers to Central Management Server Groups 95
Registering a Server to a Group 95
Importing Registered Servers and Groups 96
Evaluating Policies against a Central Management Server Group 99
Summary 102
CHAPTER 4 Policy-Based Management Using PowerShell 103
Creating a Basic PowerShell Script 103
Using T-SQL 103
Using SQL Server Management Objects 105
Interrogating for Members and Properties 107
Running a Policy Against a SQL Server Instance 109
Invoking a Policy from a File 109
Getting Detailed Results 110
Invoking a Policy Defined on the Server 111
Running Multiple Policies Against a SQL Server Instance 112
Invoking Multiple Policies from the Cmdlet 112
Invoking a Category of Policies from the File System 113
Invoking a Category of Policies from an Instance 114
Querying and Storing Policy Execution Results 115
Creating a Staging Table 116
Loading Policy Evaluation History 116
Querying the History 117
Evaluating Against a Central Management Server 119
Summary 122
CHAPTER 5 Receiving Alerts forPolicy Violations 123
Configuring Database Mail 123
Setting Up Database Mail 123
Testing Database Mail 125
Cleaning Up Database Mail History 127
Creating SQL Server Agent Operators 130
Enabling SQL Server Agent Notifications 132
Creating Alerts 134
Troubleshooting Policies 140
Viewing Policy History 140
Viewing History Based on Policies 140
Viewing History Based on Objects 141
General Troubleshooting 143
Summary 144
CHAPTER 6 Policy-Based Management Internals 145
Policy-Based Management Properties 145
Policy-Based Management Architecture 148
On Demand 148
On Change: Prevent 149
On Change: Log Only 149
On Schedule 149
Policy-Based Management Security Issues 150
Policy-Based Management Tables and Views 151
Tables 151
Contents of System Policy Tables 152
Checking for New Tables 155
Views 155
syspolicy_conditions 155
syspolicy_configuration 156
syspolicy_policies 156
syspolicy_policy_categories 157
syspolicy_policy_category_subscriptions 157
syspolicy_policy_execution_history 157
syspolicy_policy_execution_history_details 158
syspolicy_system_health_state 158
syspolicy_target_set_levels 158
syspolicy_target_sets 158
Combining Views 159
Checking for New Views 160
Stored Procedures 160
Summary 162
CHAPTER 7 Practical Uses of Policy-Based Management 163
A DBA Checklist 163
Custom Policies 164
Database Free Space 164
Successful Transaction Log Backups 167
SQL Server Agent Is Running 172
All SQL Server Agent Jobs Have Notification on Failure 174
Data Purity Flag Enabled 177
Best Practices Policies 180
Summary 182
CHAPTER 8 Reporting 183
EPM Framework Prerequisites 183
Setting Up the EPM Framework 184
The Setup Script 184
The PowerShell Script 185
Reporting Services Reports 189
Viewing EPM Framework Reports 193
Automating the EPM Framework 196
Summary 197
CHAPTER 9 Enforcing Compliance 198
Compliance Overview 198
Compliance Regulations 199
Gramm-Leach-Bliley Act 199
The Sarbanes-Oxley Act 200
Health Insurance Portability and Accountability Act 200
Payment Card Industry Data Security Standard 200
Server Configuration 201
Service Account 202
Log Retention 204
Surface Area Configuration 207
Security 208
Administrative Accounts 209
Removing the BuiltinAdministrators Login 210
Disabling the sa Login 210
Best Practice Security Policies 211
Encryption 212
Transparent Data Encryption 212
Extensible Key Management 214
Best Practice Encryption Policies 215
Auditing 215
SQL Server Audit 216
Login Auditing 216
Default Trace 217
Best Practice Audit Policy 219
Summary 219
CHAPTER 10 Where to Go from Here 220
Upcoming Releases 220
SQL Server Web Sites 220
Blogs 221
White Papers 221
Podcasts 222
Free Training Events 222
Social Networking 223
Microsoft Support Options 224
SQL Server Books Online 224
Webcasts 224
SQL Server Troubleshooting and Support Resources 225
Microsoft Technical Communities 225
Paid Support 225
Summary 226
APPENDIX Microsoft SQL Server Best Practice Policies 227
Best Practice Policy Descriptions 227
Best Practice Policy Conditions and Facets 242
Index 246