Virtualizing SQL Server with VMware: Doing IT Right!

 

The printing presses at VMware Press have been churning out copies of Virtualizing SQL Server with VMware: Doing IT Right, which I’ve co-authored with Michael Corey and Jeff Szastak. We had a great technical reviewer for this book in Mark Achtemichuk (Mark A – VCDX-050). There will be enough copies for everyone, and we’ll be signing them at VMworld(s), and vForum Sydney if you’d like your copy autographed (and hopefully SQL PASS events also). This is 512 pages that combines decades of experience into a book, in a single place, which is the definitive guide for virtualizing SQL Server on VMware. This project took us over 12 months to complete, and many late nights and full weekends. If you ask my family they almost didn’t see me for the entire year. This is the third book project I’ve been involved with, after being technical reviewer for VCDX Bootcamp and Virtualizing and Tuning Large-Scale Java Platforms, both also by VMware Press. Duncan Epping has done us a great honour and written a fantastic foreward for the book.

Although I have dedicated this book to my wife, Susanne, and my four sons, Sebastian, Bradley, Benjamin, and Alexander , for their ongoing support (and putting up with my absence during this project). I’ve also dedicated this book to the VMware Community.

I was also lucky to have some great sounding boards during this project in addition to my fantastic co-authors. Kasim Hansia, VMware Strategic Architect and SAP expert, Cameron Gardiner, Microsoft Senior Program Manager Azure and SQL, and Josh Odgers (VCDX-090), Nutanix Senior Solutions and Performance Architect.

Below I have included the full Table of Contents so you can take a look at what we cover and see the value that we’ve packed into this book. I hope you enjoy the book, get a signed copy, and succeed when virtualizing SQL Server.

 

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

Configuring 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 Influences 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 Configuring 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 Configuration 345

VMDK File Configuration Inside Guest Operating System 352

Memory Reservations 355

Enabling Hot Add Memory and Hot Add CPU 356

Affinity and Anti-Affinity Rules 358

Validate the Network Connections 359

Configuring Windows Failover Clustering 359

Setting Up the Clusters 362

Validate Cluster Network Configuration 368

Changing Windows Failover Cluster Quorum Mode 369

Installing SQL Server 2012 374

Configuration of SQL Server 2012 AlwaysOn Availability Groups 387

Configuring 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 Configuring Dell DVD Store 406

Running the Dell DVD Store Load Test 430

Summary 436

Appendix A Additional Resources 437

 

Note: Guidance specific to number of data files has been extensively reviewed and is based on extensive implementation experience as the lowest common denominator of many versions of SQL Server that the book covers, from SQL 2000 onwards, which are in common use across the globe in enterprises today. This could be considered overly conservative for the most recent versions of SQL (such as SQL 2014) and you should determine the best approach based on your specific circumstances and requirements. When we refer in the book to keeping a backup of the transaction log on SAN or alternative storage to local flash this can easily be achieved with a transaction log backup – see msdn.microsoft.com/en-us/library/ms191429.aspx. There is an error on page 309 where the failover time of a cluster is listed as 3 seconds. A 0 (zero) was omitted and the failover time should read 30 seconds. We apologise for this error and it will be corrected.

 

 

Final Word

We put a lot of time and effort into this project in the hope that you get a lot of value out of it. Your comments and feedback are as always welcomed. Our book is now generally available in both paperback and eBook form. If you like what you see in the table of contents then please consider purchasing your copy today.

—

This post first appeared on the Long White Virtual Clouds blog at longwhiteclouds.com. By Michael Webster. Copyright © 2012 – 2014 – IT Solutions 2000 Ltd and Michael Webster. All rights reserved. Not to be reproduced for commercial purposes without written permission.