Chris Shaw, Sr.Database Engineer, Xtivia
If you answered yes, this doesn’t surprise me. For years, I was against placing any instance of SQL Server, especially a mission critical one, anywhere near a virtualized environment. The Internet was scattered with assorted horror stories about how virtualizing SQL Server would lead to a resume-changing event. As a Database Administrator, I recall the pains of my first SAN Migration. During this migration one of the major changes was the visibility into the underlining architecture. Essentially, we lost that visibility. This means that we couldn’t fix what we couldn’t see. This is the same type of environmental change database administrators go through when moving to a virtual environment.
Even stubborn Database Administrators are unable to refute the cost benefits of a virtualized environment; business needs are going to drive technology and the surrounding decisions for many years to come. Software companies are motivated to provide these solutions simply because of the business need to provide better infrastructure for less money. This action leads us to the real question, what information needs to be supplied to the Database Administrator so they are comfortable with virtualizing SQL Server?
Database Administrators often make a number of configuration changes to optimize the way SQL Server utilizes the hardware. For example, Database Administrators make adjustments to how much memory SQL Server can use. The goal is to leave the operating system enough memory while simultaneously giving SQL Server enough memory for efficient query processing. In a virtualized environment, Database Administrators still need to know how to tune the configuration to meet the demanding performance requirements of mission critical databases. Virtualization adds another layer of complexity that the Database Administrator now has to take into account.
Microsoft SQL Server memory usage can be tuned by adjusting the minimum memory and maximum memory. When adjusting the maximum server memory it is common to set allocation of memory for SQL Server at a very aggressive level thus leaving the Operating System starved for memory. This is an easy mistake to make, considering that many SQL Servers are dedicated to running only SQL Server. However, processes as simple as copying backup files off the server can impact the memory usage on the operating system. If the operating system is starved for memory, the downstream effects will be materialized in SQL Server. Limiting the maximum memory within SQL Server will often improve the overall performance as well as alleviate the memory starvation of the operating system. The minimum memory setting is frequently left untouched, at the default setting. This allows for Microsoft SQL Server to release memory back to the operating system when Microsoft SQL Server has finished with it. It is not common to see the minimum memory setting configured, I suspect this is because most Database Administrators understand how important the memory can be, when it comes to high performing Microsoft SQL Servers. To finish the memory configuration for virtualized servers, enable the ‘Lock Pages in Memory’ for the account that the SQL Server service is running as.
“Most Database Administrators are going to need to see proof that moving to a virtualized environment is not going to impact the mission critical environment negatively”
VMWare put a considerable amount of research into a SQL Server configuration, which produces optimal performance on VMware platforms. Some of these recommendations go against the traditional configuration of SQL Server, such as the minimum memory setting.
Memory isn’t the only hardware consideration when it comes to finding support from your Database Administrators. There is always the issue of storage and how important it is to the performance of the database. When Database Administrators request storage from SAN Administrators often the only question we are asked is, “What is the size of the volume?”Seasoned Database Administrators should reply with the amount of storage, and follow up that request with a specific latency the database will require (Microsoft recommends sub five millisecond for log files and sub 10 millisecond for data files). In a virtualized environment the requirement doesn’t change. The SQLIO tool by Microsoft works just as well in a virtualized environment just as it does physical environments.
Technical articles and White Papers can only go so far when it comes to winning over your Database Administrators to using virtualized environments. Most Database Administrators are going to need to see proof that moving to a virtualized environment is not going to impact the mission critical environment negatively. In the physical world, when new architecture was being considered this often meant the commitment of the organization to purchase test machines. In a virtualized environment a new test machine can be spun up within a matter of minutes. Using this virtual test machine, the Database Administrator can perform benchmark testing to determine the overall impact. When the results of these tests are placed side by side, with the flexibility that a virtualized environment offers as well as the overall savings, then the decision to support a virtualized environment is pretty clear. As a Database Administrator, I am very cautious when trying a new technology because the stability of business critical applications are depending on me. However, when the technology is easily tested and has been well documented, my comfort level increases. Today, at XTIVIA 60-70 percent of the SQL Server installations weper form are in a virtualized environment, with the majority of those being installed on VMWare.