SQL Server: Virtualization

Learn how to properly configure, control, and monitor virtual machines, and troubleshoot problems when running SQL Server in a virtualized environment using VMware or Hyper-V, applicable for DBAs, VM admins, and any other IT admins responsible for SQL Server and/or virtualization

Course info

Rating

(185)

Level

Intermediate

Updated

Jun 19, 2013

Duration

4h 7m

Description

Virtualization of SQL Server instances have become very popular in IT infrastructures, but it's very common to see misconfigured virtual machines resulting in severe performance problems of the SQL Server workloads. This course will show you how to properly configure, control, and monitor VMware and Hyper-V virtual machines being used for SQL Server instances, and how to troubleshoot and fix common performance problems caused by virtualization issues. The course begins by explaining how virtualization works under the covers and why virtualization has become so popular. It then discusses the various memory, CPU, and storage considerations that must be taken into account when virtualizing SQL Server, and demonstrates how to properly configure virtual machines for use with SQL Server. Next the course discusses the high availability and disaster recovery options that VMware and Hyper-V provide, plus how to back up virtual machines. Finally the course shows how to monitor and improve virtual machine guest and host performance in VMware and Hyper-V, and discusses some of the limitations that could prevent a SQL Server workload from being successfully virtualized. This course is perfect for all IT admins and DBAs who are responsible for any aspect of a virtualized SQL Server environment, from SQL Server 2005 onward.

About the author

Jonathan Kehayias is a Principal Consultant with SQLskills. He is the youngest person ever to achieve the Microsoft Certified Master - SQL Server 2008 certification and has been a SQL Server MVP for many years.

Section Introduction Transcripts

IntroductionHi this is Jonathan Kehayias of SQL Skill. com and I'm recording this SQL Server Virtualizations course for Pluralsight, this is module 1 an Introduction to the course. So in recent years it's become a lot more popular for virtualization to be used by most of the IT shops that I've worked with, and this has been a growing trend over the recent years especially for line of business applications like SQL Server. And we even see a lot of virtualization of things like Exchange and Oracle and SI Base as well. Now when I typically deal with IT shops it's when there's a problem, and usually when I'm working with virtual machines for SQL Server it's because there's been performance issues. And a lot of times attempts at virtualizing SQL Server can fail for a lot of reasons that can be identified. Some examples would be incorrect virtual machine configuration for the workload that's actually being run on SQL Server or Host hardware sizing issues, based on the consolidated workloads of all the BM's that are running on a single host. Failure to properly consider the effects of mixing really large, and very small virtual machines together on a single host, and in a lot of cases just incorrect monitoring of virtual machine performance. So we want to look at how all of these come together as a part of this course for being able to properly virtualize SQL Server. Now some recent changes that have happened with hypervisor technology, and the limitations of the sizes of virtual machines that we can actually run on VMware and Hyper-V makes it possible to virtualize SQL Server workloads with acceptable performance a lot easier today then it was in previous years. So as a part of this course we are going to take a look at how these hypervisor changes have come about and what they actually mean to you for virtualizing SQL Server.

How Virtualization WorksHi, this is Jonathan Kehayias from SQLskills. com, and I'm recording this SQL Server Virtualization course for Pluralsight. This is module 2: How Virtualization Works. Now virtualization is a hot topic today, but it's not actually a new technology. It's actually been being used since the 1960's on mainframes to allow multitasking of applications to occur. Virtualizing the X86 architecture does pose some challenges that have to be overcome to prevent problems from occurring for the operating systems that are running inside of the virtual machine environment. Understanding how virtualization actually functions at a basic level can be beneficial to making decisions about what to virtualize, and when, inside of your business data center. In this module we're going to cover the CPU virtualization techniques or states that have to be virtualized for successful virtualization to occur. We'll also talk about the x86 architecture levels of privilege and how Ring De-privileging allows virtualization to be performed successfully in today's applications base. We'll also talk about the Virtual Machine Monitor or VMM, and how it functions to provide virtualization capabilities to guest systems running on a single host. Then we'll talk about the different types of virtualization including binary translation, para-virtualization and hardware-assisted virtualization and how they are actually implemented inside of the virtual layer. And then we'll talk about memory virtualization and what's necessary for being able to virtualize memory access for servers running in a virtualized infrastructure.

Why Virtualization is PopularHi this is Jonathan Kehayias from SQL Skills. com and I'm recording this SQL Server virtualization course for Pluralsight, this is Module 3 Why Virtualization is Popular. In recent years virtualization has exploded in popularity amongst all business sizes, even small businesses are heavily invested or interested in virtualizing the servers in their data center. Previously it used to just be large enterprises that were the forerunners for virtualization of data center assets. Understanding why virtualization is become so popular can help with making the case for whether or not virtualizing SQL Server is the right thing to do for your business. In this module were going to cover a number of reasons why virtualization has become so popular. Including virtualization being cheaper, lowering the total cost of ownership for the servers inside of the data center, simplifying disaster recovery for critical servers in the event of a data center disaster, providing high availability to servers without having additional hardware resources required, and how it can reposition IT as a utility rather than a cost center, which is the way IT departments traditionally have been budgeted for and viewed by businesses, is it's just a cost of doing business and how can we minimize that cost? By repositioning IT as a utility it actually changes the way the server infrastructure and budgeting occurs for a lot of IT departments.

SQL Server ConsiderationsHi. This is Jonathon Kehayias from SQLskills. com and I'm recording this SQL Server Virtualization course for Pluralsight. This is Module 4: SQL Server Considerations. Now it's been my experience working with virtualization that understanding the considerations that are generally applied by IT administrators for determining what servers to virtualize is actually important for understanding why they make the big push to virtualize SQL Server and having a good understanding of the considerations that they apply to what should be virtualized will help you with making arguments for or against server virtualization of SQL Server. Knowing the important considerations for SQL Server specifically will help with driving any planning for a physical to virtual migration of SQL Server or in implementing new SQL Servers using virtualization while also preventing performance problems that typically occur. In this module we'll talk about determining VM candidates and how IT administrators or non-DBAs consider virtualization candidates inside of the datacenter and then we'll look at some SQL Server specific considerations that you can push back on or provide further information to the IT managers or administrators about what SQL Server needs and then we'll wrap up talking about planning for VM storage needs.

Virtual Machine Resource ConfigurationHi, this is Jonathan Kehayias from SQLskill. com, and I'm recording the SQL Server: Virtualization course for Pluralsight. This is Module 5: Virtual Machine Resource Configuration. So, we've already addressed that the proper configuration of the VM that SQL Server will run on is critical for obtaining the best possible performance, and in this module we're going to take a look at the configuration options that exist for both Hyper-V and VMware. Now, Hyper-V and VMware both provide options to control resource allocations for specific VMs. In this module we're going to cover determining the appropriate vCPU count for a VM, co-scheduling issues that can occur for VMs running inside of both Hyper-V and VMware, virtual NUMA configurations for large or wide VMs that will run SQL Server with more virtual CPUs and memory allocations than a hardware NUMA node may contain, how to guarantee VM resources to a SQL Server VM so that we have predictable performance, and then memory configuration considerations for both Hyper-V and VMware.

High Availability and Disaster RecoveryHi, this is Jonathan Kehayias from SQLskill. com, and I'm recording this SQL Server: Virtualization course for Pluralsight. This is Module 6: High Availability and Disaster Recovery. Now previously I've mentioned that the built-in high-availability is one of the most common reasons that I hear for businesses pushing to virtualize SQL Server. And the end goal there is to actually remove the unused passive high-availability servers that exist inside of the data center and are taking up power, cooling, and rack space. Now with the high availability that's offered by VMware, there are benefits that could be used for eliminating parts of the high-availability configuration by SQL Server. However, the SQL Server high-availability options typically provide more than just protection from hardware failures, so we need to keep those additional options in mind when we're thinking about whether or not high-availability provided by VMware or Hyper-V is going to meet our business requirements, because we may still be required to have SQL Server high-availability options configured when we're virtualizing SQL Server. In this module, we'll cover the basics of virtualization high-availability and what it actually provides, then we'll talk about additional high-availability features provided by VMware and Hyper-V, including VMware Fault Tolerance and Hyper-V Replicas, and we'll also talk about the limitations that those have when we're working with SQL Server virtual machines, and then we'll also take a look at the SQL Server high-availability options and the benefits that they provide to server virtualization and SQL Server for our business SLA requirements.

Backing Up SQL Server Virtual MachinesHi, this is Jonathan Kehaylas from SQLskills. om, and I'm recording this SQL Server Virtualization course for Pluralsight. This is module seven, Backing up SQL Server virtual machines. Now virtualized SQL Servers require that the appropriate backups are configured to meet your business data loss SLAs. And there are many different ways to back up virtual machines, but the drivers should be the recovery point objective and recovery time objective SLAs that have been defined by the business. VM level backups may seem tempting for their simplicity, but they may not meet data loss or recovery point objective SLAs for SQL Server. SQL Server backups should always be evaluated against third-party VM based tools to ensure that you're getting the appropriate backup configuration for your SQL Server VMs. I'm not saying that VM level backups don't have a fit, but it's definitely something that you want to make sure is going to be the right fit for the end result, should you have a crash of your database or virtual machine. In this module, we're going to cover VM backup's solutions and how they work, some of the limitations that exist for VM backups, and then SQL Server backups and whether or not you should choose to use native backups with SQL Server over one of the VM backup solutions that might exist.

Monitoring PerformanceHi, this is Jonathan Kehaylas from SQLskills. om, and I'm recording this SQL Server Virtualization course for Pluralsight. This is module eight, Monitoring Performance. As previously mentioned in this course, performance monitoring of virtual SQL Server instances is just as critical for being able to identify when performance problems occur and for troubleshooting the root cause of the performance problem when they're happening. Now, Virtual Machine performance counters many not have the same meaning inside of a VM as they would inside of a physical machine. And a lot of misconceptions occur when looking at performance counters inside of Windows guestOS, thinking that they actually relate to the same information that you would have had inside of a physical server. So we'll take a look at some of the examples of where that's not exactly the case. Understanding the most important counters to collect, where to find them and what they mean, is very important for being able to determine where the root cause of a performance problem actually exists. And this is one of the critical steps that gets missed sometimes when working with virtual machines that are running SQL Server that may be having performance issues. In this module, we're going to cover the performance counter differences that happen inside of a virtual machine, the VMware host counters that are important for monitoring server performance, Virtual Center counters and security necessary to be able to read those without actually having permission to change anything about virtual machine or host configuration inside of Virtual Center, the VMware Guest counter that exist inside of the Windows GuestOS, when the tools are installed, and then Hyper-V host counters and permissions necessary for monitoring Hyper-V host performance if you're running SQL Server virtual machines on Hyper-V.

Limitations and Reasons Not to VirtualizeHi, this is Jonathan Kehaylas from SQLskills. om, and I'm recording this SQL Server Virtualization course for Pluralsight. This is module nine, Limitations and Reasons Not to Virtualize. So while virtualizations very popular, it's not always the best solution for SQL Server workloads. And there are a lot of different limitations that could exist that would effect SQL Server workloads being able to perform or be highly available for critical line-of-business applications that makes virtualization not the best solution. Understanding the technical and non-technical limitations that exist while virtualizing SQL Server, will help drive the decision on whether it's appropriate for the given workload or application. In this module, we'll cover some of the technical limitation that can exist as well as the non-technical limitations that might exist. As well as other considerations for determining whether it's appropriate to virtualize SQL Server for a given application.