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
Michael Corey (@Michael_Corey) is the President of Ntirety, a division of Hosting. Michael is an experienced entrepreneur and a recognized expert on relational databases, remote database administration, and data warehousing. Microsoft named Michael a SQL Server MVP, VMware named him a vExpert, and Oracle named him an Oracle Ace. Michael has presented at technical and business conferences from Brazil to Australia. Michael is a past president of the Independent Oracle Users Group; he helped found the Professional Association of SQL Server, is a current board member of the IOUG Cloud SIG, and is actively involved in numerous professional associations and industry user groups. Michael currently sits on the executive committee for the Massachusetts Robert H. Goddard Council for Science, Technology, Engineering, and Mathematics. Jeff Szastak (@Szastak) is currently a Staff Systems Engineer for VMware. Jeff has been with VMware for over six years, holding various roles with VMware during his tenure. These roles have included being a TAM, Systems Engineer Specialist for Business-Critical Applications, Enterprise Healthcare Systems Engineer, and a CTO Ambassador. Jeff is a recognized expert for virtualizing databases and other high I/O applications on the vSphere platform. Jeff is a regular speaker at VMworld, VMware Partner Exchange, VMware User Groups, and has spoken at several SQL PASS events. Jeff holds a Master of Information Assurance degree as well as the distinguished CISSP certification. Jeff has over 13 "lucky" years in IT and is passionate about helping others find a better way to do IT. Michael Webster (@vcdxnz001) is based in Auckland, New Zealand. He is a VMware Certified Design Expert (VCDX #66), author of longwhiteclouds. com (a top-15 virtualization blog), and a Top 10 Vmworld Session Speaker for 2013. In addition, he is a Senior Solutions and Performance Engineer for Nutanix, vExpert, MCSE, and NPP. Michael specializes in solution architecture and performance engineering for Unix-to-VMware migrations as well as virtualizing business-critical applications such as SQL, Oracle, SAP, Exchange, Enterprise Java Systems, and monster VMs in software-defined data centers. Michael has more than 20 years experience in the IT industry and 10 years experience deploying VMware solutions in large-scale environments around the globe. He is regularly a presenter at VMware VMworld, VMware vForums, VMware User Groups, and other industry events. In addition to this book, Michael was technical reviewer of VCDX Boot Camp and Virtualizing and Tuning Large-Scale Java Platforms, both published by VMware Press.