Virtualization of SQL Server these days are becoming more a reality and common practice. Usually if you you mention SQL Server and Virtualization in one sentence people will look at you as if you are a mad man.

With this post I would like to talk about a couple of notes you need to consider when you virtualize SQL Server. When using a hypervisor to consolidate your SQL Servers into a virtualization environment you need to think about the impact on CPU, Memory, Storage and Network performance and management.

Let’s break it a bit down further with some checkpoint.

CPU

  • Always ensure that you disable power management for CPUs in the BIOS of the physical servers. You want the full power of your CPU available at all times.
  • In your Guest VMs make sure that the Power Management Plan is set to High performance.
  • The different levels of CPU cache has a big performance impact. Try to use the latest CPUs that have big level 3 cache. Level 3 cache in the latest Intel CPU are shared among the individual cores. This allow the hypervisor to reuse the same CPU to schedule work among the cores for your SQL Server virtual machine where certain SQL Server data is cache.
  • Assign minimal virtual cores to your SQL Server virtual machine and scale up the CPU count as required when workload increases. This allow the hypervisor the reuse the same CPU socket where certain data can remain cache rather than distribute across all available CPU sockets where you will be hit by cache misses.
  • Monitor for throttling of the CPU.
  • Avoid affinity masking.
  • Use CPU-Z to make sure your CPU is performing optimally.

Memory

  • From your hypervisor reserve memory to your SQL Server virtual machine. This allow other virtual machines not to consume your memory and allow the Balloon driver not to force your SQL Server virtual machine under memory pressure to release memory.
  • Set your SQL Server minimum and maximum memory according to your reserve memory. Don’t assign all available memory you must leave some head room for the Windows Operating System.

Storage

  • Still apply the same best practices for SQL Server database files.
  • Try and stick to RAID10.
  • You can use Pass-Trough disks in your hypervisor. Which give you slightly better performance than virtual disk drives.
  • You can use Shared or Dedicated LUNs via your hypervisor. Depended on your business requirements.
  • Correctly configure multi-pathing on your SAN.
  • Avoid 1GB iSCSI for storage, try going higher.

Network

  • Assign dedicated Network Card interfaces via you hypervisor to the SQL Server virtual machine.
  • Make sure good practices are followed for network redundancy.
  • Avoid multiple virtual machine using the same network interface.

These checkpoints are some of the common practices to follow when setting up SQL Server in a virtualization environment. Virtualization setup comes down to what you can afford and what your business requirements are.

The key areas to successful deployments are:

  1. Always use the latest version of the vendors hypervisor.
  2. Follow the vendor’s best practices.
  3. Always and always monitor the physical environment and guest virtual machines for optimizing the performance.

Microsoft Hyper-V

The Microsoft Hyper-V hypervisor does have its own best practices for deploying SQL Server. One feature that you should try and avoid is dynamic memory. You should always use static memory assignment for SQL Server virtual machines.

Here are some documents to review if you are going to use Microsoft Hyper-V.

  1. Running SQL Server with Hyper-V Dynamic Memory Best Practices and Considerations
  2. Running SQL Server 2008 in Hyper-V Best Practices and Performance Recommendations
  3. High Performance SQL Server Workloads on Hyper-V

VMware vSphere

The VVMware vSphere hypervisor has its own best practices for deploying SQL Server. A very nice feature that vSphere has is memory compression. This feature is where multiple pages in memory that are the same is removed from memory. Only keeping one copy in memory. This is a great benefit for Operating System pages in memory, but no real benefit specifically towards SQL Server. 

Here are some documents to review if you are going to use VMware vSphere.

  1. Availability Guide for Deploying SQL Server on VMware vSphere
  2. Performance and Scalability of SQL Server on VMware vSphere
  3. SQL Server on VMware Best Practices Guide

Conclusion

On the end there are allot of guidance from the individual vendors for optimal deployment of SQL Server in an virtualization environment. There are many benefits like cost savings and management in using virtualization. I would really recommend that you try setup a test environment to see the benefits.

Performance is always a concern, but at the rate at which hardware performance are increasing the little bit of performance impact you experience right now with virtualization will be a thing of the past with new hardware. Especially with enterprise SSD disk drives that are coming out at a good price.

One person that I follow that provides brilliant content on SQL Server virtualization is Brent Ozar. He has many articles and videos on virtualization.

If you have any comment or questions about SQL Server virtualization please feel free to contact me.

Cheerio!

Today is the start of a very exciting and challenging journey to become a Microsoft Certified Master on SQL Server 2008. This post is about my planning on achieving the certification.

The Motivation

Over the years I have worked in various environments that involved development with SharePoint, BizTalk, Integration, Web and custom development. All of them had one thing in common and that is they all use SQL Server. I always enjoy and have allot of passion working within SQL Server. Always learning the little bits about the ins and outs of SQL Server is fun. Working with SQL Server all over the years came to a point that I ask myself  how can I improve myself to be better at what I do and know. That has put my focus on achieving the Microsoft Certified Master on SQL Server 2008.

The Schedule

How am I going to achieve this and by when? My personal schedule is to write the MCM on SQL Server Knowledge Exam in the first week of May 2012. If I pass that exam then it will onto the MCM on SQL Server Lab exam. From today it leaves me with roughly 3 months for studying.

As mentioned by other MCMs that studying alone is not enough. You do need years of experience working with SQL Server.

The Resources

Where are the resources? There are various books, whitepapers and blogs that needs to be studied that will help with the exams. On the official page and from SQLskills.com you get links  to the recommended resources. There are also training programs available that can be attended for preparation to the Microsoft Certified Master on SQL Server 2008 exams. 

The OneNote

There are so many resources available on the internet that it can be a big tasks to get these resource organized. I took the time in my planning to organise the resources into a Microsoft OneNote document. You can download it from here.

The OneNote document is divided into sections namely:

  • Videos
  • Stairways Series
  • Books
  • Blogs
  • SQLSkills Training

The videos section includes the links to the recommended readiness videos for Microsoft Certified Master on SQL Server 2008. The Stairways Series section is links the various topics about SQL Server that is provided by SQLServerCentral.com. The Books section has links to the recommended books for reading. The White Papers section are the whitepapers that must be read for the exams. The Blogs section is links to some additional reading for the exam. Lastly the SQLskills Training section has been broken down to sub sections with the various resources links that is mentioned on SQLskills MCM page.

If you are also going to do the Microsoft Certified Master on SQL Server 2008 exams I hope this document can help you on your journey. Also if you find resources that I might have missed let me know and I will update the document.

Again here is the full link to the document:  http://dl.dropbox.com/u/4406115/MCM%20SQL/SQL%20MCM%20Training.one

mcm_onenote

Conclusion

In conclusion as I mentioned in the beginning of this post, it is going to be a very exciting and challenging journey to be come a  Microsoft Certified Master on SQL Server 2008. I will continue to put up posts as my learning progresses on the topics of SQL Server. For those who also attempting these exams as well, best of luck and see you at the other end.

Cheerio!

Disqus