The start-to-finish guide to virtualizing business-critical SQL Server databases on VMware vSphere 5
By virtualizing business-critical databases, enterprises can drive far more value from existing IT infrastructure. But squeezing maximum performance out of a virtualized database instance is an art as much as a science. This indispensable start-to-finish guide brings together all the techniques, tips, and insights you need to succeed.
Drawing on unsurpassed personal experience, three leading experts share complete best practices for deploying business-critical database servers in virtualized vSphere 5 environments. They cover the entire project lifecycle, bridging technical and communications gaps between SQL Server and VMware professionals that often make database virtualization more difficult than it needs to be.
You’ll find specific guidance for architects and administrators responsible for systems, storage, databases, applications, or VMware virtualization. The authors also present detailed, start-to-finish coverage of performance baselining and testing: all you need to make your virtualized databases as fast as they are cost effective. Although this book focuses on SQL, the authors’ proven guidance for enhancing performance can be leveraged by any IT professional virtualizing a demanding Tier 1 application.
Coverage includes
• Business cases for database virtualization: consolidation, Database as a Service (DaaS), efficiency, and “SLAs on steroids”
• Using the redundancy inherent in virtualization to improve availability
• Constructing a careful, conservative implementation plan
• Balancing disk, CPU, memory, and network for superior performance
• Mastering the five key principles of database storage design
• Leveraging memory: SQL MAX, page locking, NUMA, reservations, swapping, large memory pages, and more
• Ensuring responsiveness by providing a fast, reliable, low-latency network
• Supporting advanced AlwaysOn Failover Cluster Instances and Availability Groups
• Baselining physical systems and properly determining resource requirements
• Configuring performance tests from beginning to end
• Migrating existing SQL Server databases onto a vSphere platform
• Avoiding traps and pitfalls in virtualizing production databases
• Managing and monitoring virtualized database instances and resources
Table of Contents:
Foreword xvii
Preface xix
About the Authors xxiii
About the Technical Reviewer xxv
Acknowledgments xxvii
Reader Services xxix
1 Virtualization: The New World Order? 1
Virtualization: The New World Order 1
Virtualization Turns Servers into Pools of Resources 3
Living in the New World Order as a SQL Server DBA 3
A Typical Power Company 6
Summary 7
2 The Business Case for Virtualizing a Database 9
Challenge to Reduce Expenses 9
The Database Administrator (DBA) and Saving Money 10
Service Level Agreements (SLA) and the DBA 11
Avoiding the Good Intention BIOS Setting 12
DBAs’ Top Reasons to Virtualize a Production Database 13
High Availability and Database Virtualization 14
Performance and Database Virtualization 16
Provisioning/DBaaS and Database Virtualization 17
Hardware Refresh and Database Virtualization 20
Is Your Database Too Big to Virtualize? 22
Summary 23
3 Architecting for Performance: The Right Hypervisor 25
What Is a Hypervisor? 25
Hypervisor Is Like an Operating System 26
What Is a Virtual Machine? 28
Paravirtualization 29
The Different Hypervisor Types 29
Type-1 Hypervisor 30
Type-2 Hypervisor 31
Paravirtual SCSI Driver (PVSCSI) and VMXNET3 31
Installation Guidelines for a Virtualized Database 32
It’s About Me, No One Else But Me 33
Virtualized Database: It’s About Us, All of Us 34
DBA Behavior in the Virtual World 34
Shared Environment Means Access to More If You Need It 35
Check It Before You Wreck It 36
Why Full Virtualization Matters 36
Living a DBA’s Worst Nightmare 37
Physical World Is a One-to-One Relationship 38
One-to-One Relationship and Unused Capacity 38
One to Many: The Virtualized World 40
The Right Hypervisor 40
Summary 41
4 Virtualizing SQL Server: Doing IT Right 43
Doing IT Right 43
The Implementation Plan 44
Service-Level Agreements (SLAs), RPOs, and RTOs 45
Baselining the Existing vSphere Infrastructure 46
Baselining the Current Database Workload 48
Bird’s-Eye View: Virtualization Implementation 50
How a Database Virtualization Implementation Is Different 51
Summary 55
5 Architecting for Performance: Design 57
Communication 58
Mutual Understanding 59
The Responsibility Domain 60
Center of Excellence 61
Deployment Design 63
SQL Workload Characterization 64
Putting It Together (or Not) 65
Reorganization 68
Tiered Database Offering 70
Physical Hardware 73
CPU 74
Memory 76
Virtualization Overhead 76
Swapping, Paging? What’s the Difference? 78
Large Pages 79
NUMA 79
Hyper-Threading Technology 85
Memory Overcommitment 87
Reservations 87
SQL Server: Min/Max 90
SQL Server: Lock Pages in Memory 92
Storage 93
Obtain Storage-Specifi c Metrics 94
LSI Logic SAS or PVSCSI 94
Determine Adapter Count and Disk Layout 95
VMDK versus RDM 96
VMDK Provisioning Type 96
Thin Provisioning: vSphere, Array, or Both? 98
Data Stores and VMDKs 99
VMDK File Size 100
Networking 100
Virtual Network Adapter 100
Managing Traffi c Types 101
Back Up the Network 103
Summary 104
6 Architecting for Performance: Storage 105
The Five Key Principles of Database Storage Design 106
Principle 1: Your database is just an extension of your storage 106
Principle 2: Performance is more than underlying storage devices 107
Principle 3: Size for performance before capacity 107
Principle 4: Virtualize, but without compromise 108
Principle 5: Keep it standardized and simple (KISS) 109
SQL Server Database and Guest OS Storage Design 109
SQL Server Database File Layout 110
Number of Database Files 110
Size of Database Files 114
Instant File Initialization 120
SQL Server File System Layout 122
SQL Server Buffer Pool Impact on Storage Performance 129
Updating Database Statistics 130
Data Compression and Column Storage 132
Database Availability Design Impacts on Storage Performance 135
Volume Managers and Storage Spaces 136
SQL Server Virtual Machine Storage Design 136
Virtual Machine Hardware Version 137
Choosing the Right Virtual Storage Controller 138
Choosing the Right Virtual Disk Device 143
SQL Virtual Machine Storage Layout 152
Expanding SQL Virtual Machine Storage 158
Jumbo VMDK Implications for SQL Server 159
vSphere Storage Design for Maximum SQL Performance 164
Number of Data Stores and Data Store Queues 165
Number of Virtual Disks per Data Store 170
Storage IO Control–Eliminating the Noisy Neighbor 173
vSphere Storage Policies and Storage DRS 177
vSphere Storage Multipathing 184
vSphere 5.5 Failover Clustering Enhancements 185
RAID Penalties and Economics 187
SQL Performance with Server-Side Flash Acceleration 198
VMware vSphere Flash Read Cache (vFRC) 199
Fusion-io ioTurbine 201
PernixData FVP 204
SQL Server on Hyperconverged Infrastructure 207
Summary 213
7 Architecting for Performance: Memory 217
Memory 218
Memory Trends and the Stack 218
Database Buffer Pool and Database Pages 219
Database Indexes 222
Host Memory and VM Memory 225
Mixed Workload Environment with Memory Reservations 226
Transparent Page Sharing 228
Internet Myth: Disable Memory TPS 229
Memory Ballooning 230
Why the Balloon Driver Must Run on Each Individual VM 232
Memory Reservation 232
Memory Reservation: VMware HA Strict Admission Control 233
Memory Reservations and the vswap File 233
SQL Server Max Server Memory 234
SQL Server Max Server Memory: Common Misperception 235
Formula for Confi guring Max Server Memory 236
Large Pages 237
What Is a Large Page? 237
Large Pages Being Broken Down 238
Lock Pages in Memory 239
How to Lock Pages in Memory 241
Non-Uniform Memory Access (NUMA) 241
vNUMA 243
Sizing the Individual VMs 244
More VMs, More Database Instances 244
Thinking Differently in the Shared-Resource World 246
SQL Server 2014 In-Memory Built In 246
Summary 247
8 Architecting for Performance: Network 249
SQL Server and Guest OS Network Design 250
Choosing the Best Virtual Network Adapter 250
Virtual Network Adapter Tuning 252
Windows Failover Cluster Network Settings 254
Jumbo Frames 256
Confi guring Jumbo Frames 259
Testing Jumbo Frames 262
VMware vSphere Network Design 264
Virtual Switches 265
Number of Physical Network Adapters 267
Network Teaming and Failover 270
Network I/O Control 274
Multi-NIC vMotion 276
Storage Network and Storage Protocol 279
Network Virtualization and Network Security 281
Summary 286
9 Architecting for Availability: Choosing the Right Solution 287
Determining Availability Requirements 287
Providing a Menu 288
SLAs, RPOs, and RTOs 290
Business Continuity vs. Disaster Recovery 291
Business Continuity 291
Disaster Recovery 291
Disaster Recovery as a Service 292
vSphere High Availability 294
Hypervisor Availability Features 294
vMotion 296
Distributed Resource Scheduler (DRS) 297
Storage vMotion 297
Storage DRS 297
Enhanced vMotion X-vMotion 298
vSphere HA 298
vSphere App HA 299
vSphere Data Protection 300
vSphere Replication 300
vCenter Site Recovery Manager 301
VMware vCloud Hybrid Service 302
Microsoft Windows and SQL Server High Availability 302
ACID 302
SQL Server AlwaysOn Failover Cluster Instance 304
SQL Server AlwaysOn Availability Groups 306
Putting Together Your High Availability Solution 308
Summary 310
10 How to Baseline Your Physical SQL Server System 311
What Is a Performance Baseline? 312
Difference Between Performance Baseline and Benchmarks 315
Using Your Baseline and Your Benchmark to Validate Performance 318
Why Should You Take a Performance Baseline? 319
When Should You Baseline Performance? 320
What System Components to Baseline 320
Existing Physical Database Infrastructure 321
Database Application Performance 323
Existing or Proposed vSphere Infrastructure 325
Comparing Baselines of Different Processor Types and Generations 328
Comparing Different System Processor Types 328
Comparing Similar System Processor Types Across Generations 330
Non-Production Workload Infl uences on Performance 331
Producing a Baseline Performance Report 332
Performance Traps to Watch Out For 333
Shared Core Infrastructure Between Production and Non-Production 333
Invalid Assumptions Leading to Invalid Conclusions 334
Lack of Background Noise 334
Failure to Considering Single Compute Unit Performance 335
Blended Peaks of Multiple Systems 335
vMotion Slot Sizes of Monster Database Virtual Machines 336
Summary 337
Contents
11 Confi guring a Performance Test–From Beginning to End 339
Introduction 339
What We Used–Software 341
What You Will Need–Computer Names and IP Addresses 341
Additional Items for Consideration 342
Getting the Lab Up and Running 342
VMDK File Confi guration 345
VMDK File Confi guration Inside Guest Operating System 352
Memory Reservations 355
Enabling Hot Add Memory and Hot Add CPU 356
Affi nity and Anti-Affi nity Rules 358
Validate the Network Connections 359
Confi guring Windows Failover Clustering 359
Setting Up the Clusters 362
Validate Cluster Network Confi guration 368
Changing Windows Failover Cluster Quorum Mode 369
Installing SQL Server 2012 374
Confi guration of SQL Server 2012 AlwaysOn Availability Groups 387
Confi guring the Min/Max Setting for SQL Server 392
Enabling Jumbo Frames 393
Creating Multiple tempdb Files 394
Creating a Test Database 396
Creating the AlwaysOn Availability Group 399
Installing and Confi guring Dell DVD Store 406
Running the Dell DVD Store Load Test 430
Summary 436
Appendix A Additional Resources 437
TOC, 9780321927750, 7/3/14
