Tips for SQL Server Virtualization

It’s hard to believe, but some people still believe that SQL Server can not run on a virtual machine. This is not so. SQL Server can operate successfully in a virtual machine. However, in view of its high resource requirements, I want to give a number of recommendations, which it is desirable to follow. Maybe the biggest difference between poorly performing a virtual instance of SQL Server and virtual instance, ensuring exceptional performance. The examples in this article, tips will help ensure a successful virtualization of SQL Server.

Council № 1. Take advantage of the second level address translation

To support virtualization platforms such as VMware vSphere Server and Microsoft Hyper-V, as a virtualization host server must use a 64-bit x64 processor. It may be the AMD or Intel. In addition, for products with a high consumption of resources, such as SQL Server, it is essential that the processor supports and the second level address translation Second Level Address Translation (SLAT). SLAT technology is known by different names, depending on the manufacturer of the processor. At Intel, it is called Extended Page Tables, in AMD — Nested Page Tables and Rapid Virtualization Indexing.

The virtual machine uses memory virtualization host and SLAT — a mechanism that allows the processor to display the virtual memory used by the virtual machine to a physical memory virtualization host. If this display is not executed by the processor, it should be hypervisor. The ability of the processor to perform memory mapping improves performance and scalability. Microsoft Research show that for each running virtual machine SLAT reduces excessive burden on the hypervisor by about 2% with a simultaneous release of the server’s memory by approximately 1 MB. SLAT work to increase the performance of the virtual machine.

Most of today’s northern platform is already equipped with x64 processors with support for SLAT. But if you plan to virtualize SQL Server on older platforms, the situation may be different. Many older systems with x64 processors support SLAT missing. Therefore, if you intend to virtualize SQL Server on these older systems, make sure that they provide support for SLAT.

Council № 2. Do not overload your physical processors

SQL Server can automatically take advantage of multiple processors available in the system. If you are upgrading from an environment deployed on a physical server to a virtualized environment, you can use the number of processors in the physical environment as a guideline for the number of virtual processors assigned to virtual machines with SQL Server. If you are setting up a new installation of SQL Server, you will first need to follow the instructions of the application developer or to track the use of processors to use the information system monitor running in the appropriate virtual machine.

5.1 virtualization platform on this issue is important, especially if you need virtualization SQL Server, requires a high level of scalability. Hyper-V in Windows Server 2008 and Windows Server 2008 R2 is limited to four virtual CPUs to a virtual machine that is a definite ceiling for virtual instances of SQL Server on these platforms. Windows Server 2012 dramatically raises this limit to 64 virtual CPUs. VMware vSphere 5.0 supports 32 virtual processors and a new version of vSphere — up to 64 virtual CPUs. If the workload on the processor in a virtualized SQL Server is very high, you must run Windows Server 2012 Hypcr-V, vSphere 5.0 or newer.

Council № 3. Use the dynamic memory

RAM — one of the most important factors in the performance of SQL Server. This is true for physical and virtualized instances of SQL Server. SQL Server uses memory to cache an internal buffer and procedure cache. The buffer cache is mainly used to store recently used data and procedure cache contains recently executed command of T-SQL. These buffers allow SQL Server to achieve a high level of performance required by the sample data and instruction cache, avoiding the performance IO with a hard disk. SQL Server can automatically manage the buffer cache and cache management procedures and increase their size, based on the needs of the load and the size of available RAM. Thus, the presence of available memory is essential for good performance SQL Server. However, an excessive allocation helps only to a certain point. Furthermore, in a virtual environment, the amount of physical memory limits the number of simultaneously active virtual machines. When the virtual server capacity planning is important to ensure that adequate, but not excessive memory, as this may lead to a lack of memory for other virtual machines. Dynamic memory is an excellent solution to meet the memory requirements of SQL Server and the need for separation of physical memory between multiple virtual machines.

To use the SQL Server services advantages of dynamic memory the guest operating system must be able to identify «hot» connection memory. To use Dynamic Memory Hyper-V on Hyper-V host must be running Windows Server 2008 R2 SP1 or later. In addition, the guest operating system running on the virtual machine must support the «hot» connection memory. Use dynamic memory Hyper-V may be the following guest operating systems:

2012 5.2 Windows Server;

2008 5.3 Windows Server R2 SP1;

2008 5.4 Windows Server SP2;

The Windows Server 2003 R2 SP2;

5.5 Windows 8;

5.6 Windows 7 SP1;

5.7 Windows Vista with SP1.

To take advantage of dynamic memory necessary to use in SQL Server 2008 (or newer) Enterprise Edition or Datacenter Edition edition versions of SQL Server 2008 R2, or SQL Server 2008. The other version of SQL Server does not have the ability to support «hot» connection of memory and can not take advantage dynamic memory.

When you select a guest operating system, additional memory, it looks like a «hot» addition of physical memory for an instance of SQL Server. When SQL Server performs its workload sqlscrvr.exe process requests and obtains memory from the operating system. For dynamic growth of SQL Server memory are important three factors:

5.8 workload;

5.9 the maximum amount of memory the server is higher than the current size of the allocated memory;

5.10 operating system signals about the critical shortage of memory.

When SQL Server workload leads to an increase in the required resources of sqlservcr.exe, SQL Server detects the addition of memory and allocates resources according to requirements of the workload. SQL Server checks the amount of memory the operating system every second and dynamically adjusts its memory according to the amount of available memory and the value of the maximum amount of memory on the server.

The article Corp. Microsoft «Running SQL Server with Hyper-V Dynamic Memory» (msdn.microsoft. Com / cn-us / 1 ibrary / hh 372970. aspx) is a set of stress tests that compared eight virtual machines with SQL Server. In one set of test load is performed on the eight virtual machines with 7.5 GB of memory and static on eight virtual machines with dynamic memory. In tests with dynamic memory virtual machines have 2 GB of initial memory and maximum 12 GB memory. The load consisted in performing SQL-12500 pps. In the embodiment with a dynamic memory in the system was stable value of I-7500 you water per second, when using static memory was a stable level of 12 thousand. IOPS per second. In other words, dynamic memory provides the same performance using only 60% of the IO performed with static memory.

The figure shows the results of the screen test of dynamic memory in Performance Monitor. Black line here shows the buffer pool memory of SQL Server. The green line indicates the number of disk reads per second. It is clearly seen that in the beginning of the test, the number of reads is very high, but as the allocation of an additional virtual machine SQL Server memory increases the size of the buffer pool. As a result, the number of disk reads decreased by approximately 50%.

SQL Server typically does not seek to recover the memory that it has allocated. However, it reduces the size of the caches in response to signals of the operating system of the critically low memory. Monitor SQL Server resource checks for signals on the low memory every five seconds and tries to free memory, as long as these signals do not stop. If the available virtual machine guest memory is reduced due to the fact that the hypervisor is trying to take away from the memory of a virtual machine and select it another guest operating system can move the pages of the working set of SQL Server paged pool. This can have a negative impact on the performance of SQL Server. To prevent this situation, Microsoft recommends that you use the settings page locks SQL Server memory to memory SQL Server is never placed in the paged pool. For more information about using dynamic memory to SQL Server can be found in the article «Using Dynamic Memory Hyper-V with SQL Server», published in the Windows IT Pro / RE № 3 for 2013.

Although the dynamic memory is an important means of improving the efficiency of resource use virtualization environment, it is also important to remember that it is not a panacea in the case of performance problems. It is no substitute for quality design and configuration databases. You still need a system monitor to track the workload of server products as well as system requirements and performance change over time.

Council № 4. Use fixed or connected directly to the virtual hard disks

There are three main types of virtual hard disks Virtual Hard Disk (VHD), used in the virtual machine, regardless of the selected platform virtualization (Hyper-V or vSphere).

-Dynamic. The initial size of the dynamic VHD is set to the amount of information stored in the guest operating system in the future size of the VHD is increased in accordance with the growing needs until it reaches the set limit.

-Fixed. Presumptive VHD is set to the maximum size.

Differencing disks. First create a basic disk that is used as the parent disk. Then you, as appropriate, can create child differencing disks. Subsidiaries parent disk drives used as a base, but all the changes each child drive stores independently.

Fixed VHD disk is almost always the best option for virtualized SQL Server systems in a production environment. Fixed drives occupy a larger space, but provide the best performance compared with other types of VHD. Dynamic VHD suitable for lab and test environments, and can also be used in non-critical production environments. Dynamic VHD uses less disk space, but do not provide the same performance as a fixed virtual disks. In addition, the systems that use dynamic disks, will from time to time there is a pause in the expansion of the moments on the dynamic disk. Difference drives are best suited to laboratory environments, where the first place is to save disc space. They require much less space, but they and the lowest performance.

Another option to store the virtual machine is to use plug directly (pass-through) discs. Connected directly drives — an alternative to the file VHD. Disks of this kind, in fact, are the sections of the storage host system directly allocated to a virtual machine. This storage can be either internal hard drive Server Hyper-V, or LUN array SAN, connected to the server virtualization. Connected directly drives provide the highest level of performance for the virtual machine disk subsystems. However, they do not have the flexibility of fixed VHD. Connected directly drives can not be moved without stopping the virtual machine, they also do not support snapshots of virtual machines.

In general, the best option would be to use fixed VHD, unless you have a strong need of a high level of performance, and provides plug directly drives. Dynamic disks can be used in non-critical production environments as well as for testing, training and laboratory experiments.

Council № 5. Do not use the setting for the default

As well as the physical server, disk storage configuration of the virtual server can have a huge impact on the performance of SQL Server. If you use the settings for the default offered by vSphere and Hyper-V, you are sure to finally get a copy of the inefficient SQL Server, because by default only for VHD disk subsystem. In other words, the operating system files, files of SQL Server databases and transaction log files will be placed on the same VHD. Standard configuration is suitable only for small virtual SQL Server environment with a small flow of transactions. Most production systems with transaction-intensive inevitably face the challenge of competition for disk resources.

The high-performance virtual SQL Server environment is very important to place the files of the operating system, database and transaction logs on different VHD or pass-through disks. If you are using a shared storage network, it is important to know well the internal implementation of the physical disk and provide storage for SQL Server transaction log separately from the database files.

Council № 6. Be well versed in matters of licensing

There is no doubt that the license — it is one of the most important and most confusing aspect in the development of products Microsoft. Virtualization complicates things even more, and SQL Server is no exception. Some changes in SQL Server 2012 editions, as well as new types of license «per processor core» can be very confusing licensing virtual instances of SQL Server.

As you can see, Enterprise edition is licensed only in «to the core», while the new version of the Business Edition is licensed only «per server» plus CAL (CAL). Standard edition is licensed as «at the core» and in the «server plus CAL». The licensing regime SQL Server 2012 «at the core» requires the mandatory purchase of a minimum of four licenses. You can buy additional licenses «to the core» package for two licenses. When you deploy SQL Server 2012 in a virtual machine, each virtual processor is equal to the core of the physical processor. For example, if you license the standard edition of SQL Server 2012 in the «to the core» and install it on a virtual machine with four virtual processors, you need to purchase four licenses «to the core.» An exception is the case when you buy SQL Server

-Enterprise Edition, and this edition are licensing all core physical machine. With this you can run an unlimited number of instances of SQL Server in virtual machines on a given node virtualization. And no matter what your virtualization platform — Hyper-V or vSphere.

Another important aspect is the transferability of licenses. Technically, you can easily implement live migration of virtual machines between hosts virtualization, but there are licensing restrictions governing the frequency of movement of the virtual machine. OEM-license can be moved only once in 90 days, which is not very suitable for dynamic data centers. To be able to move more frequent virtual machine is required to have a license granted subscription Software Assurance (SA). Have SA is a prerequisite if you are planning to deploy a dynamic data center or private «cloud».

virtual reality

Although virtualization SQL Server for a long time considered impossible, it is now clear that it has many advantages over deploying a physical server. The consolidation of several servers as virtual machines allow:

-increase the return on investment by optimizing the use of natural resources;

-reduce management costs by reducing the number of physical servers, requiring management;

-reduce IT infrastructure requirements to energy consumption, cooling, and space.

In addition, the virtualization provides a framework for program-controlled data centers. If you pay due attention to the planning and implementation of recommendations on optimization of virtualized instances of SQL Server will be able to provide the performance and scalability needed to effectively support business-critical applications that use the database.

SQL - 16 | 0,453 сек. | 6.97 МБ