This article is part of the SQL Server virtualization family
Build a SQL Server virtual machine that has its own standards and best practices across organizations. From the first glance, there are hundreds of physical configurations of light, all of which are likely to cause trouble detection in the subsequent daily management process. If you create an appropriate virtual machine template and use it for subsequent creation and deployment of new virtual machines, you can minimize many deployment issues.
In this article, "Why" is discussed to create an idealized SQL Server virtual machine for the current SQL Server payload. The next chapter will demonstrate the creation of the current mainstream VMware and Hyper-V virtual environments.
VM Configuration:
The virtual machine contains most of the traditional physical machine components--cpu, memory, network cards and storage. Virtual machines only change the configuration and distribution of these, but this change must be tied to the operating system to take effect.
Virtual CPU configuration:Understanding the CPU's infrastructure can help you select the appropriate CPU configuration for SQL Server virtual machines in command management. Today's CPU architecture is very complex and diverse, the server can often contain one to 16 CPU chip sockets, and each physical CPU can be divided into multiple virtual CPU core (core), a physical CPU can contain 1 or a maximum of 16 cores.For virtual machines and new hypervisors, you can configure the number of virtual CPU sockets and virtual CPU cores, but how much is "right"?In the physical machine world, too few CPUs limit the ability of SQL Server to handle the load. In the virtual machine world, too. Another extreme phenomenon is that there are too many CPUs. In a physical machine environment, too much CPU is only high idle rate, and will generate additional heat, waiting for the load to increase. But for virtual machines, too many CPUs will not be the same.For virtual machines, allocating too much CPU can cause performance degradation, as too many virtual CPUs are allocated to a virtual machine, which slows down the running of the virtual machine and causes additional CPU contention because of the resources that account for the CPU on the same physical machine.Therefore, choosing the appropriate number of virtual CPUs for each SQL Server virtual machine is a key and difficult part of this, not only because of the difficulties caused by this lack of sufficient load analysis, but also because it is a constantly changing process.
Virtual NUMA:In modern servers, there are multiple CPU sockets, each with a single CPU chip, and each chip contains multiple virtual cores. For example, a logical CPU is configured in a 4-slot, 48-core server. Each slot has a certain amount of memory allocated on top. Then the combination of slots and this part of memory belongs to a NUMA (Non Uniform memory access, non-conforming RAM accesses/nodes). Other operating system threads can access memory from other NUMA nodes on one core of a NUMA node, but because of the length of the physical path, the path is complex, resulting in slower speeds.The new version of Hypervisors improves VM performance by improving the encoding to make better use of NUMA, and the virtual machine's activity can be isolated to a single NUMA node to minimize overhead across NUMA nodes. It is also possible to extend the NUMA configuration of a virtual machine to the operating system, allowing the physical machine to reasonably balance the workload so that the application can take advantage of the NUMA configuration.
Shows 2 different virtual machine VM configurations with 8 virtual CPUs per virtual machine. The first virtual machine (left) uses 2 virtual slots (less than the number of CPU-encapsulated schedules per NUMA node), with 4 virtual cores per slot. The second VM uses the default configuration, and each virtual slot corresponds to a virtual core, which is 8 virtual slots.The configuration of the same size, spanning two physical CPU sockets in the first configuration, may be optimal for each CPU socket workload. Its memory access can be configured to only two slots, and then the VM knows how to achieve the most efficient memory management by locating the memory of each virtual NUMA node.In the second configuration, the possible load is dispersed across all available virtual CPU sockets, and memory management is not as good as the first one, and there may be a performance penalty.If a SQL Server virtual machine requires 16 virtual CPUs to cope with the workload. So, what is the optimization of the following configuration options?
- 1 Virtual Slots (16 virtual cores).
- 2 virtual slots, 8 virtual cores per second.
- 4 virtual slots, 4 virtual cores per second.
- 8 virtual slots, 2 virtual cores per second.
- 16 virtual slots, 1 virtual cores per second.
The DBA Domain has a famous saying: It depends (specific analysis). Also suitable for use here, this depends entirely on your underlying hardware, the resource contention on the physical machine, the level of application-to-NUMA recognition, and so on. Fortunately, SQL Server is a NUMA-aware application. So you can use a specific configuration to run a load-test or synthetic benchmark tool (Synthetic Benchmarking Utility), and then come up with performance metrics. For core applications, use this approach as much as possible to derive optimal performance.In a nutshell, if you use the method of testing for 1 months, and use the top 5 items in the middle of three different configuration tests. and using HAMMERDB to simulate SQL Server load, get the following results:
In this test, 4 virtual CPU sockets were used, each with 4 virtual CPU cores (core), the highest in each test, and 64 concurrent users, 8 faster than 2 slots per 31% virtual core. This dramatic improvement doesn't have to change any line of code, and it's completely free.Our goal is to configure a CPU that is appropriate for a workload to a single VM and optimize it with NUMA configuration. If the memory allocated to the VM spans the NUMA node, the performance of the VM is reduced. So make sure that the VMS on the physical machine can run on the same hardware configuration. If different configurations exist, separate tests are required to find the most appropriate scenario.
Hot Add (Hot-add):Hypervisors supports hot-add, which means that CPU and memory resources can be added directly while the VM is running. But when it comes to the NUMA impact, such as having a CPU slot, 4 Vcpus and 16GB of memory virtual machines, and then adding 13 virtual CPUs to the virtual machine to reach a total of 17 virtual CPUs, how does Numa equalize? is technically possible, but unless the VM resides on a physical machine that has a larger number of CPU sockets than the physical CPU core, it can cause performance degradation across NUMA due to memory addressing. Therefore, the Vnuma extension is not enabled, regardless of whether you enable any form of hot-add CPU or hot-add memory. And you are likely to affect existing SQL Server performance because the VM's NUMA addressing is addressed across NUMA because the hot add CPU or memory causes it.Therefore, it is not recommended to enable hot-add functionality for VMS running SQL Server, unless you need to do so in the actual situation.
Virtual network (Vsan Networking):The virtual machine needs a way to interconnect with the physical network, hypervisors the network between the virtual machine and the physical machine by creating a virtual network. This network is mapped to a virtual switch, essentially the hypervisor internal traffic routing engine (Traffic Routing Engine), the virtual switch is attached to one or more physical network adapters on the physical machine. When a virtual network adapter on a VM is granted to a specific named virtual network, network traffic can be routed through traffic to the physical network.Virtual networks can be multiple, and traffic can often be isolated or redirected to a certain number of targets through the expansion of VLANs. A VLAN allows traffic to transmit traffic or routes in a specific way, and multiple VLANs can be shared to a physical network adapter port. Each virtual network is tagged with a VLAN tag and granted traffic to a specific VLAN on the pipeline.If you plan to configure Availability Groups (SQL Server AlwaysON) in a virtual machine, then you want to isolate the transport traffic and the backup traffic for AG. The three virtual networks that are in the configuration scenario are:
The two SQL Servers (SQLAG01 and SQLAG02) in AlwaysOn contain 3 virtual network adapters. The third stand-alone machine (SQL09) has only two, and these adapters are connected to a separate virtual network. Each virtual network is configured with a separate VLAN tag or VLAN assignment. If the underlying network is set to not route traffic between multiple VLANs, the network adapter on one server does not recognize the network adapters of the other VMs.For example, SQL09 traffic is not on VLAN61, nor can access high-availability groups through VLAN61. This configuration allows the flow of AG to be isolated to maximize performance, as there is no need to worry about the traffic being affected by other virtual machines, while the backup traffic is isolated and can be safely set up separately. If you introduce virtual fan acquisition, you can further increase security.
Virtual hard disk (Vsan Disks):Storage systems can be presented in a variety of ways in virtual machines, each with advantages and disadvantages. Most VM Administrators use virtual hard disks in the same way as. In the VMDK format in VMware, a VHDX in Hyper-V is displayed as a file format. A virtual hard disk is a shared file system managed by hypervisor, which provides very much flexibility from a hypervisor perspective. Virtual hard disks can be dynamically redistributed or dynamically expanded without the need to interrupt the corresponding guest system. For SQL Server VMS, this type of hard drive is almost absolute proportions.
The second way in VMware is to callRaw Devices Mapped disk (RDM, raw device mapped disks), called pass-through disk (direct attached disks) in Hyper-V. Storage is spent as a San LUN, presented to all physical server virtualization clusters. For example, this disk is identified by a red arrow and a Box shape. However, these are not formatted and managed by Hypervisor for virtual disks, it is configured as a separate disk for the virtual machine and is connected directly from the physical hard disk to the virtual machine via hypervisor. The end result is similar to a single San LUN directly connected to a physical machine.
However, in general, the use scenario for RDM and direct-attached disks takes precedence because the size of the volume is limited, in older versions of hypervisor and VMware-specific shared storage, for shared storage applications such as Windows Server failover clusters are lock-size. The old version of Hypervisor was limited to 2TB, but Vsphere 5.5 released the same limit to 64tb,hyper-v. In the past, the performance of RDM and direct-attached disks was due to virtual disks, but the differences in modern hypervisor were small enough to be ignored.
The third form of virtual machine disk representation is in-guest, which is typically related to iSCSI or SMB 3.0. In an airliner, the operating system of the virtual machine is allowed to connect to the storage target instead of being controlled by hypervisor. The advantage of this approach is that between the physical machine and the virtual machine, the LUN is relocated from one server to another server. This design reduces the downtime of large loads from physical servers to virtual servers. However, this configuration removes the abstraction of a particular device, thus reducing the flexibility relative to the VM's virtual hard disk.Virtual hard disks are usually preferred unless there is a special requirement. Before selecting a virtual hard disk, you should not only collect the original SQL Server instance, but also the original server itself. Because the workload may exist in a non-SQL Server on this server. Continue with these collected data, and then determine the configuration of the virtual hard disk that is appropriate for the workload.For example, add 8 virtual hard disks to a virtual machine based on a specific load collection, and allocate a regular amount of space:
| Drive |
Size (GB) |
Objective |
| C |
60 |
Operating system |
| D |
20 |
SQL Server Instance installation path |
| E |
20 |
SQL Server System Inventory drop path |
| F |
100 |
User database Data files |
| L |
40 |
User database log files |
| T |
50 |
TempDB Data and log files |
| Y |
50 |
Windows Paging File |
| Z |
100 |
Database Local backup Disk |
Note Not all SQL Server needs to be configured as described above.In a lighter-load SQL Server, the configuration might look something like this:
| Drive |
Size (GB) |
Objective |
| C |
80 |
Operating system +sql Server Instance installation path |
| F |
100 |
User database Data files |
| L |
40 |
User database log files |
| T |
50 |
tempdb data and log files |
| Z |
100 |
Database Local backup Disk |
Just like SQL Server on a physical machine, the usual goal is to allocate the load to a reasonable hard disk, making its queue smaller. If only one worker thread is available for a process, then any path to the end-to-end system stack can be congested and become a system bottleneck. For example, a SQL Server that has only a single filegroup and data file on one volume (e-disk), the storage request is transferred to the virtual layer via a path to the Windows OS and then to the inline switch via the HBA card, eventually connecting to the San controller and the physical hard disk.
In contrast, adding multiple filegroups, data files, physical disks, and ensuring that paths are properly isolated minimizes possible bottlenecks, allowing the hypervisor and physical layers to better distribute the load. Such as.
The goal and type of the isolated payload also reflects the flexibility of how the end user controls the volume. For example, if a Windows or SQL Server SP needs to be installed, the administrator can create a VM-level snapshot of the virtual disk, but the volume of the database may be excluded from losing the committed transaction when the system rolls back.The system library for SQL Server: Master, model, and msdb are placed on a single volume, because if the system is to use advanced features such as mirrors or high-availability groups (availability Groups), you can put this hard disk on faster storage to provide better performance.The Windows operating system's page files are also independent, and should be done under heavy San-to-san replication mechanisms. These files are placed in a non-standard location to allow this single virtual hard disk to be placed on a volume that replicates much less frequently than other volumes of the SAN, saving replication bandwidth.The database backup target is used for local storage of the reserve files. Similar to the page file for Windows, you can save overhead by preventing low-speed disks.Tempdb is independently placed on a separate disk due to its own characteristics. Tempdb contention waits if tempdb is contending for obvious hard disks on the resource.Similarly, the configuration of data files and log files also needs to be considered. Depending on the load, the space and the disk strategy will vary.
Summarize:Understanding the core concepts and core component configurations of a virtual environment is essential for DBAs to create and manage database virtual machines. If you already have a server running on a virtual environment, you can check and adjust it. The next two sections describe the actions on VMware and Hyper-V, respectively.
Next section:SQL Server Virtualization (3)--building SQL Server on VMware
SQL Server Virtualization (2)--Ideal SQL Server virtual machine architecture
