This article is part of the SQL Server virtualization family
Objective:In modern systems, virtualization is becoming more common, and if there is a lack of understanding of how virtualization works, DBAs will have blind spots when it comes to solving performance problems such as reducing resource contention and improving backup and restore speed. Therefore, based on the situation of my working environment and the trend of the great times, at the same time, according to personal experience, the majority of IT staff have doubts or reservations about virtualization attitude. So here's a series of articles that describe the contents of SQL Server virtualization, from Stairway to server virtualizaion, such as subsequent updates or other content that will be added.The current series contains the following subsections, the first of which is this article:
- SQL Server Virtualization (1)--Introduction to Virtualization
- SQL Server Virtualization (2)--Ideal SQL Server virtual machine architecture
- SQL Server Virtualization (3)--building SQL Server on VMware
- SQL Server Virtualization (4)--building SQL Server on Hyper-V
Let's start with the first section, Introduction to Virtualization http://www.sqlservercentral.com/articles/Stairway+Series/112555/
Brief introduction:Virtualization is one of the most disruptive and beneficial technologies in enterprise data-centric processes in a long period of events. With virtualization technology, businesses have consolidated their server infrastructure and, over the past more than 10 years, have saved a lot of money for data centers, and virtualization is everywhere.If you haven't virtualized your SQL Server at this point, it's just a matter of time for your business. No, from experience, in many enterprise management specifications, the virtual layer is visible only to infrastructure administrators, and DBAs are almost completely isolated and ignorant of the virtualization platform. As a result, DBAs tend to hesitate to place critical servers on the new platform.Even if the server is virtualized, the DBA is generally not included in the virtualization process, so the DBA has little knowledge of the newly added layer. Like a "black box", full of unknowns and risks.Without an understanding of how virtualization works, DBAs have a blind spot when they try to solve many problems, such as performance issues such as reducing resource contention and improving backup restore performance. They often don't realize that some of the problems, such as unstable backup strategies or long-running tasks, can be solved by adjusting some of the features and functionality built into the virtualization layer.This section first describes what virtualization is, why you need to understand its concepts, why it needs to be involved as a DBA, and how to run your critical SQL Server tasks in a virtual infrastructure. In subsequent articles, key virtualization-specific tips and tricks are overwritten to maximize SQL Server efficiency and flexibility for the virtualized platform.
From physical to virtualized:In order to compare virtualization, first we need to understand the corresponding side of the physical, the following is the traditional server infrastructure:
In a traditional server infrastructure, each SQL Server needs to do the following:
- Procure and deploy a physical server.
- Configure local or similar SAN storage.
- Install and configure the operating system.
- Install and configure SQL Server.
- Deploy the database, and then configure application access.
At this point, the physical resources of the server are fixed and limited. Also, you need to allocate more CPU, I/O, and memory resources than the expected load generated by the expected resource consumption object. As a result, resources are often underutilized (in percent) for most of the time the server is running.As a result, virtualization comes into being, and functionally, virtualization is a new layer built on the hardware IQ that allows multiple independent operating systems to run on the same physical server. Although they are actually sharing the physical resources available on the physical server, they are completely independent and run on the same physical server independently of each other.
Multiple independent operating systems (that is, virtual machines) can run independently and concurrently on the same physical server, and each operating system no longer relies on a particular physical machine. The virtual layer uses queues so that each virtual machine and its internal applications can request the same computing resources, such as CPU or memory. Through the appropriate queue request, the resource request can be reasonably responded to by the physical machine.Finally, virtualization can make one or more physical servers a total pool of compute resources, as well as using queues to allow virtual machines and applications above to access the resource pool. Once configured, administrators can divide and allocate resources by creating virtual servers, each of which assigns unique resources to each virtual server.
Virtualization Technology:System administrators tend to use the same infrastructure-specific terminology to refer to different parts of the stack. Such as:
- Hypervisor: Installs and configures the virtualization layer on each physical machine. Used to process virtual machine resource request queues and implement resource delivery on physical machines.
- host: Refers to a physical machine within a set of machines in a cluster or a group of hosts following the same rules.
- Independent Management System: Controls the actions and rules in the host group, monitors component outages in all infrastructure, environment automation synergies, and provides administrators with a centralized management environment.
- Guest: A virtual machine in a virtualized "cluster" can be located on any one of the physical machine (host) clusters. However, a moment can only be located on one host, so the size of the virtual machine is limited by the available resources of the single host.
- interconnects: The host is connected by different network and storage connections. and shared storage components.
The mainstream virtualization management program on the market is:
- VMware VSphere
- Microsoft Hyper-V
- OpenStack
- Oracle VMS
- Red Hat Enterprise Virtualization
- Citrix XenServer
Conceptually, they do the same thing: Allow multiple virtual machines, have their own operating system, run independently and cooperatively on the same physical server, and share the compute resources of one or more physical servers. But different vendors have different implementations of the software, so they have their own characteristics, but they are very similar. Because this series focuses on the virtualization of SQL Server, we focus on architecture, performance, and specific management details.
How does virtualization Help DBAs?We often hear about looking at issues from a corporate perspective: saving money, energy, time, and so on. But what exactly is virtualization good for DBAs? If used wisely, the virtualization platform can work almost anywhere in the DBA's career.
Reduce costs:The cost of the license for SQL Server is considerable compared to many DBMS (of course not with Oracle), but it can be reduced if properly paired with the virtualization licensing model.
Independent Integration:
Often, when DBAs consider consolidation, headaches are the hassle and challenges of merging multiple instances into one instance, or merging multiple application databases into one instance. With virtualization, you can consolidate by improving resource consumption so that multiple virtual machines can exist together on a single host. This does not require merging instances or applications. In another direction, the overhead of multiple operating systems can be overlooked in the resource consumption perspective, so it is preferable to detach instances as multiple separate host hosts if the license allows.
High Availability:
Virtual tiers can be added directly and indirectly to SQL Server's high availability and risk minimization policies (risk minimization strategy). Without virtualization, creating a highly available SQL Server environment requires a lot of complex technology. In some cases, virtualization high availability protection for virtual machines can replace a complex, highly available solution with a very simple and transparent ha solution.
For example, in VMware High-availability scenarios, a check box for hardware unplanned outages is provided, providing functionality that can be restored within 4 minutes once a physical machine fails.
Even if the configuration is unreasonable in a strict virtual machine-centric HA scenario, high availability at the virtual machine level can also complement the stability of traditional SQL Server servers, reducing the severity of events caused by physical component failures.
Disaster recovery:The virtualized SQL Server is performing better in disaster recovery. Typically, the SQL Server at the disaster recovery site should be almost identical to the source server, or there may be insufficient resources in the failover process to cope with the stress situation, which can result in an exception or a transfer failure. In a physical environment, disaster recovery testing is not to be omitted. In virtualized SQL Server environments, virtual machines are not dependent on the nuances and complexities of physical servers. You can recover and elevate the server to an active state without relying on specific devices. Virtualization technology can be used to simplify, supplement, and even replace traditional SQL Server disaster recovery techniques.
Agility and Flexibility:Due to the elimination of hardware dependencies, the system changes become relatively easy. As long as a single host in the cluster can accommodate your virtual machine, you can dynamically adjust CPU, memory, network, and hard disk configuration. If the virtual machine exceeds the host limit, you can purchase a higher-provisioned host and migrate the virtual machine to the new host, which will not affect the running business. This process also applies to hardware upgrades. Add a new host to the cluster, speak the virtual machine to migrate to the new device, and then stop using the old hardware.
Standardization:In your environment, how many servers are made up of? What is the model? BIOS and driver version? Hard drive configuration? Where are the files stored? With the right design of the virtual infrastructure, and using preconfigured standard SQL Server virtual machine templates for deployment. This way your SQL Server environment becomes a standardized environment and can be documented. It also saves time.
Differences in organizational barriers and priorities:Virtualization is a relatively lengthy and arduous process, initially virtualized for rapid and large-scale integration. The overall performance overhead of the early hypervisor was much higher than now, making the performance impact very noticeable. But because early virtualization was used in a simple pre-production environment load, performance issues were not very important.With the development of time. The hypervisor continues to improve, becoming more efficient and capable of supporting large virtual machines. More production environments are virtualized. It can be said that these platforms have been prepared to cope with the entire production environment on Earth.But don't forget the original purpose of virtualization-mass integration. Virtualization administrators are forced to squeeze virtual machines into their environment as much as possible. The first factor in the consolidation process-performance. Back now, in a lot of circumstances, the problem still exists.Now that the DBA has a different task, the normal DBA takes the data as the start, then data consistency, and then performance. The first two and virtualization management goals are the same. But both sides of the core conflict-performance and integration still exist. Looking back at the beginning, I said, is virtualization really only a resource and resource queue? The more resources you request in your environment, the more obvious the resource contention will be. Resource contention for physical servers means the performance loss of virtual machines. The more resource contention, the more performance loss.The conflict between performance and consolidation is a major problem in most SQL Server virtualization processes. SQL Server virtualization has a poor reputation for SQL Server virtualization due to unreasonable creation and management of virtual machine administrators. Virtual machine administrators are not proficient in the nuances of the application in virtualization, nor do they know what they should know. However, their best practice is usually aimed at enabling the environment to adapt to most workloads. But this often brings trouble to the DBA. SQL Server is a latency-sensitive application that 1~2% the performance impact of a file server, which may be magnified to 25% or even larger in large SQL Server. You as a DBA, to express this phenomenon, familiarity with the virtualized environment is critical to the normal communication problems with virtual machine management.The simplest way to express the impact is to conceptually understand the stack and use object information to demonstrate the performance impact of the underlying infrastructure problem. These objective metrics can break any virtualized application, and if you are ready to virtualize your entire production system, the availability and performance of your critical SQL Server will be less cumbersome.
SQL Server Virtualization Monitoring:Monitoring, collecting, and trending analysis of key performance metrics can help DBAs track and analyze trends before and after virtualization. If you can't objectively prove that your system is performing abnormally after virtualization, how do you respond to the problem? So resumes the process of continuous performance statistics collection, analysis, and infrastructure availability to maximize efficiency, which is a key point in a virtual environment.
Continuous Performance Collection:Continuous performance metrics collection of Windows Server and SQL Server is critical. Fortunately, this configuration and maintenance is relatively straightforward. If you have a System center or other collector, make sure all of your key counters have been added. If not, you can use Windows performance counters. Make it 24*7 collection without affecting Windows and SQL Server running. This section can refer to the author's blog inside the article: ongoing Windows Perfmon Collection Setup
Benchmarks and baselines:In addition to the system performance counters, performance statistics for performance-critical objects should be collected from the SQL Server environment. Includes objects that can measure performance: long-running queries, backup events, ETL events, and so on.The information collected should be repeatable and the results must be saved for trend analysis.
Understanding the Virtualization Layer:When you start to virtualize your environment. The management system behind enterprise virtualization collects key metrics such as CPU performance, storage consumption, and resource queue wait events from hosts, guest jets, and host infrastructure. At least you need to have read-only access to these systems that you manage. This information can be accessed quickly to analyze these metrics and locate performance issues.
Summarize:This section describes some of the basics of SQL Server virtualization, the key features, and the difficulties in virtualization. These sections are covered in depth in subsequent chapters.
Next section:SQL Server Virtualization (2)--Ideal SQL Server virtual machine architecture
SQL Server Virtualization (1)--Introduction to Virtualization
