SQL Server 2008 Administration in Action

contents
foreword xiv
preface xvii
acknowledgments xix
about this book xx
about the cover illustration xxiii
about the author xxiv
PART I PLANNING AND INSTALLATION ............................... 1
1 The SQL Server landscape 3
1.1 SQL Server 2008: evolution or revolution? 4
1.2 Editions and features 5
Enterprise 5 ■ Standard 7 ■ Workgroup 7 ■ Other editions
of SQL Server 8
1.3 SQL Server tools 8
1.4 DBA responsibilities 10
2 Storage system sizing 12
2.1 Characterizing I/O workload 13
OLTP vs. OLAP/DSS 13 ■ I/O metrics 14

vi CONTENTS
2.2 Determining the required number of disks and
controllers 15
Calculating the number of disks required 15 ■ Bus
bandwidth 17 ■ A note on capacity 18
2.3 Selecting the appropriate RAID level 18
RAID 0 19 ■ RAID 1 20 ■ RAID 5 20 ■ RAID 10 21
2.4 Selecting an appropriate storage system 22
Direct-attached storage 22 ■ Fibre Channel SANs 22
iSCSI 23 ■ Recommendations 24
2.5 SQL Server and SANs 25
The SAN administrator 25 ■ LUN configuration 26
Performance tuning 27 ■ Disaster-recovery options 27
2.6 Solid-state disks 28
What is SSD? 28 ■ Current limitations of SSD for enterprise
deployments 29 ■ Potential advantages for SQL Server
deployments 29
2.7 Best practice considerations: storage system sizing 30
3 Physical server design 31
3.1 Disk configuration 31
Creating and aligning partitions 32 ■ Distributing load over
multiple controllers 36 ■ Configuring storage cache 37
Validating disk storage performance and integrity 38
3.2 CPU architecture 42
Hyperthreading and multicore 42 ■ CPU cache and clock
speed 43 ■ CPU platforms 44
3.3 Memory configuration 45
Design for future RAM upgrades 46 ■ NUMA 47
3.4 Networking components 50
Gigabit switches 50 ■ NIC teaming 50 ■ Manually
configuring NIC settings 50
3.5 Server consolidation and virtualization 51
Goals of consolidation and virtualization 51
Consolidation 52 ■ Virtualization 53
3.6 Best practice considerations: physical server design 56

CONTENTS vii
4 Installing and upgrading SQL Server 2008 58
4.1 Preparing for installation 59
Preinstallation checklist 59 ■ Service accounts 59 ■ Additional
checks and considerations 60
4.2 Installing SQL Server 62
Default and named instances 62 ■ GUI installation 62
Command prompt installations 67
4.3 Upgrading to SQL Server 2008 67
Upgrade Advisor 68 ■ In-place upgrade 70 ■ Side-by-side
upgrade 71
4.4 Developing a service pack upgrade strategy 73
Installation considerations 74 ■ Application
outage 74 ■ Recommended approach 75
4.5 Best practice considerations: installing and upgrading
SQL Server 75
5 Failover clustering 78
5.1 Clustering overview 79
Clustering architecture 79 ■ SQL Server clustering advantages
and limitations 80 ■ Clustering in Windows Server
2008 81 ■ Quorum models 82
5.2 Clustering topologies and failover rules 83
Single-instance clusters 84 ■ Multi-instance
clusters 84 ■ N+1/M clusters 85 ■ Failover rules 85
5.3 Installing a clustered SQL Server instance 86
Integrated vs. advanced installation 86 ■ Integrated installation
steps 87
5.4 Best practice considerations: failover clustering 91
PART II CONFIGURATION ............................................... 93
6 Security 95
6.1 Authentication mode 96
Windows Authentication mode 97 ■ SQL Server and Windows
Authentication mode (Mixed Mode) 98

viii CONTENTS
6.2 Networking 98
Protocol selection and configuration 99 ■ Static and dynamic
TCP ports 100 ■ Windows Firewall 101 ■ Network
encryption 102
6.3 Implementing least privilege 103
Windows and DBA privilege separation 103 ■ SQL Server service
account permissions 104 ■ SQL Server Agent job
permissions 105 ■ Role-based security 107
6.4 Auditing 111
SQL Server Audit 111 ■ DDL triggers 115 ■ Logon
triggers 116 ■ Change Data Capture 117
6.5 Data encryption 119
Transparent Data Encryption 120 ■ Cell-level encryption 123
6.6 SQL injection protection 123
6.7 Best practice considerations: security 124
7 Configuring SQL Server 128
7.1 Memory configuration 129
32-bit memory management 129 ■ 64-bit memory
management 131 ■ Setting minimum and maximum
memory values 132
7.2 CPU configuration 134
Boost SQL Server Priority option 135 ■ Maximum Worker
Threads option 135 ■ Lightweight pooling 136 ■ CPU
affinity 137 ■ Maximum Degree of Parallelism 137 ■ Cost
Threshold for Parallelism 139
7.3 Server configuration 139
Recovery Interval 140 ■ Fill factor 141 ■ Locks 142 ■ Query
Wait 142 ■ User Connections 143 ■ Query Governor Cost
Limit 143
7.4 Operating system configuration 144
Running services 144 ■ Processor scheduling 144 ■ Network
protocols 145 ■ Page file location 145
7.5 Best practice considerations: configuring SQL Server 145
8 Policy-based management 147
8.1 Server management challenges 148
Enterprise environments 148 ■ Enterprise DBA
challenges 149 ■ The risks of mismanagement 150

CONTENTS ix
8.2 Policy-based management terms 151
Targets 151 ■ Facets 151 ■ Conditions 152
Policies 153
8.3 Policies in action 153
Importing policies from file 153 ■ Evaluating
policies 155 ■ Creating a database properties
policy 157 ■ Exporting policies 158
8.4 Enterprise policy management 159
Central management servers 159 ■ Policy-based management with
central management servers 161
8.5 Advanced policy-based management 162
ExecuteWql() and ExecuteSql() 162 ■ PowerShell 164
8.6 Best practice considerations: policy-based
management 166
9 Data management 168
9.1 Database file configuration 169
Volume separation 169 ■ Multiple data files 171 ■ Sizing
database files 172 ■ Instant initialization 174
9.2 Filegroups 175
Controlling object placement 175 ■ Backup and restore
flexibility 175
9.3 BLOB storage with FileStream 177
BLOBS in the database 178 ■ BLOBS in the file
system 179 ■ FileStream data 180
9.4 Data compression 183
Data compression overview 183 ■ Row compression 185 ■ Page
compression 185 ■ Data compression considerations 186
9.5 Best practice considerations: data management 190
PART III OPERATIONS ................................................ 193
10 Backup and recovery 195
10.1 Backup types 196
Full backup 196 ■ Differential backup 199 ■ Transaction log
backup 200 ■ COPY_ONLY backups 203

x CONTENTS
10.2 Recovery models and data loss exposure 204
Simple recovery model 204 ■ Full recovery
model 205 ■ Bulk_Logged recovery model 206
10.3 Backup options 207
Backup location and retention policy 207 ■ Backup
checksums 210 ■ Backup mirroring 210 ■ Transaction log
marks 211
10.4 Online piecemeal restores 212
10.5 Database snapshots 217
Creating and restoring snapshots 218 ■ Snapshot usage
scenarios 219
10.6 Backup compression 221
10.7 Best practice considerations: backup and recovery 223
11 High availability with database mirroring 226
11.1 High-availability options 227
Failover clustering 227 ■ Transaction log
shipping 227 ■ Database mirroring 228 ■ Comparing highavailability
options 229
11.2 Transaction log shipping 230
Usage scenarios 231 ■ Setting up and monitoring log
shipping 232 ■ Failover and role reversal 237
11.3 Database mirroring overview 238
Terminology 238 ■ Mirroring restrictions 239
11.4 Mirroring modes 240
High performance (asynchronous) 241 ■ High safety
(synchronous) 242
11.5 Failover options 243
Automatic failover with SNAC 243 ■ Manual
failover 245 ■ Forced service 245 ■ Failure scenarios 246
11.6 Mirroring in action 246
Mirroring setup 247 ■ Monitoring database
mirroring 250 ■ Suspending and resuming
mirroring 253 ■ Initiating failover 254 ■ Considerations for
mirroring multiple databases 255
11.7 Best practice considerations: high availability 256

CONTENTS xi
12 DBCC validation 260
12.1 DBCC validation overview 261
DBCC CHECKDB 262 ■ Granular consistency
checking 265 ■ Additional DBCC CHECK* commands 267
12.2 Preventing and detecting corruption 268
SQLIOSIM 268 ■ Page checksums 269
12.3 Controlling CHECKDB impact 270
Running against backups 270 ■ WITH
PHYSICAL_ONLY 271 ■ Partitioned and granular
checks 272 ■ User-defined snapshots 273
12.4 Removing corruption 273
Interpreting DBCC output 274 ■ Determining the extent of data
loss with DBCC PAGE 275 ■ Recovery options 275 ■ Root
cause analysis 278
12.5 Best practice considerations: DBCC validation 278
13 Index design and maintenance 280
13.1 An introduction to indexes 281
Heaps 281 ■ Clustered indexes 281 ■ Nonclustered
indexes 283 ■ Index structure 284 ■ Key
lookup 286 ■ Statistics 287
13.2 Index design 287
Selecting a clustered index 288 ■ Improving nonclustered index
efficiency 291 ■ Indexed views 299
13.3 Index analysis 303
Identifying indexes to drop/disable 304 ■ Identifying indexes to
add 307 ■ Identifying index fragmentation 314
13.4 Index maintenance 316
Dropping and disabling indexes 316 ■ Removing
fragmentation 317
13.5 Managing statistics 320
Index statistics 320 ■ Column statistics 322 ■ Manually
creating/updating statistics 323 ■ Inspecting statistics 324
13.6 Best practice considerations: index design and
maintenance 325

xii CONTENTS
14 Monitoring and automation 330
14.1 Activity Monitor 331
Processes 332 ■ Resource Waits 332
Data File I/O 332 ■ Recent Expensive Queries 333
14.2 SQL Server Profiler 334
Workload analysis 334 ■ Server-side trace 337 ■ Trace
replay 338 ■ RML utilities 340 ■ Deadlock
diagnosis 343 ■ Blocked process report 345 ■ Correlating traces
with performance logs 346
14.3 Performance Monitor 347
Viewing counters in real time 347 ■ Baseline analysis 348
14.4 Task automation and alerts 350
Maintenance plans 350 ■ SQL Server Agent 353 ■ Event
alerts 354 ■ Error logs 357
14.5 Best practice considerations: monitoring and
automation 358
15 Data Collector and MDW 360
15.1 Component overview 361
Data Collector 361 ■ Data collection sets 361 ■ Management
data warehouse 361
15.2 Setup and configuration 362
MDW selection or creation 362 ■ Data collection setup 364
15.3 Data collection 365
Upload method and frequency 365 ■ Backup
considerations 367 ■ Retention period 368 ■ Logging 368
15.4 Custom collection sets 368
15.5 Reporting 370
Disk Usage Summary 370 ■ Query Statistics History 371
Server Activity History 372 ■ Custom reports 374
15.6 Best practice considerations: Data Collector and MDW 374
16 Resource Governor 375
16.1 Resource Governor overview 376
Resource Governor benefits 376 ■ Resource Governor
limitations 376 ■ Resource Governor components 377
16.2 Classifier function 378

CONTENTS xiii
16.3 Workload groups 380
16.4 Resource pools 382
Effective minimum: memory considerations 383 ■ Effective
minimum: CPU considerations 383
16.5 Resource Governor in action 384
16.6 Monitoring resource usage 387
Performance Monitor 387 ■ Events 387 ■ DMVs 387
Establishing resource boundaries 388
16.7 Best practice considerations: Resource Governor 388
17 Waits and queues: a performance-tuning methodology 390
17.1 SQLOS schedulers 391
17.2 Wait analysis 392
sys.dm_os_wait_stats 393 ■ Track/get
waitstats 394 ■ sqlos.wait_info extended event 395
17.3 Common performance problems 397
Procedure cache bloating 398 ■ CPU pressure 406 ■ Indexrelated
memory pressure 408 ■ Disk bottlenecks 409
Blocking 412
17.4 Waits, queues, and DMV cross-reference 413
17.5 Best practice considerations: performance tuning 413
appendix A Top 25 DBA worst practices 417
appendix B Suggested DBA work plan 419
appendix C Common Performance Monitor counters 421
appendix D Top 10 Management Studio enhancements 423
appendix E Date/time data types in SQL Server 2008 425
index 427