Four Essential Tips for Virtualizing SQL Server Instance

1. Using SLAT-Enabled 64-bit Processors

When you virtualize your SQL server, you have to make sure that the virtualization host is using the right processor. It should be able to accommodate the requirements that come with the change. The processor should be 64-bit but remember that not all 64-bit processors support Second Level Address Translation, better known as SLAT. SLAT is a form of hardware virtualization in the current generation of Intel and AMD 64-bit processors.

The SLAT-enabled processors have the ability to reduce the software overhead that is a result of shadow paging. This, eventually, can get quite recursive and expensive. SLAT translates guest physical addresses to system physical addresses which leave the guest in complete control over its page tables. SLAT-enabled processors improve scalability as well as the VM performance.

2. Maintaining a one-to-one ratio between virtual CPUs and physical processor cores

Maintaining a one-to-one ratio between the virtual CPUs (vCPUs) and physical processors will significantly increase the performance. This practice will ensure that every virtual machine (VM) has a consistently available processing power.

3. Selecting the Right Virtualization Platform

Having multiple processors on your server is quite advantageous for the SQL Server. If your SQL Server needs a high degree of scalability, then choosing the right virtualization platform can   help in achieving visible change in performance. In some cases, there is a limit to the number of virtual CPUs you can use. For example, Hyper-V in Windows Server 2008 is limited to four vCPUs. Choose a virtualization platform that doesn’t put such limitations.

Windows Server 2012 overcame this restriction by extending its vCPU support to 64 vCPUS. Other examples include Mware vSphere 5.0 which supports 32 vCPUs and the latest vSphere 5.1 that supports up to 64 vCPUs. Opt for Windows Server 2012 Hyper-V or vSphere 5.0 or later for virtualized SQL Server environments entertaining high CPU-intensive operations.

4. Make use of dynamic memory and avoid over-allocating Memory

Insufficient memory configuration can seriously affect the performance of SQL Server, regardless of whether the server is physical or virtual. SQL Servers have a buffer cache and procedure cache, which contain recently used data pages and recently executed T-SQL commands respectively. The SQL Server can manage its memory buffers automatically and adjust them according to the server workload. But, its ability to allocate excess memory is limited and over-allocating memory in one VM will take memory away from other VMs. The solution to this kind of memory problem lies in dynamic memory.

In order for the SQL Server to utilize dynamic memory, the guest operating system must be able to recognize hot-add RAM. By installing Hyper-V host in Window Server 2008 R2 SP1 or later version, you can use dynamic memory with operating system Hyper-V. The guest operating system (such as Windows Server 2008 SP2 or Windows Server 2008 R2 SP1) that is running on the VM must also support the ability to hot-add RAM. If you want to take advantage of hot-add RAM in the guess operating system, you must run the SQL Server Enterprise or Datacenter edition of SQL Server 2008 R2 or the SQL Server Enterprise edition of SQL Server 2012.SQL Server can detect added memory use and can increase its memory to meet the workload demand.

A couple of years back, it was thought that it wasn’t possible to virtualize an SQL server, but not anymore. SQL Virtualization brings in numerous benefits such as reducing your IT infrastructure’s power, space requirements and cooling, reducing management and increasing the hardware platforms’ ROI. You can successfully enhance the performance and scalability and support your mission-critical database applications in an efficient manner.

About the author: A self-proclaimed tech geek, with a passion for ScaleArc’s disruptive technology innovation in database load balancing. Tony has a passion for dissecting tech topics such as transparent failover, centralized control, ACID compliance, database scalability and downtime effects. On his days off, he can be found watching sci-fi movies, rock climbing or volunteering.

Tony Branson, Database Load Balancing Senior Analyst at ScaleArc
tony.branson@scalearc.com