WIXLIB

SQL Server Virtualizationpros and cons

SQL Server Virtualization pros and consVirtualization often has a positive impact on theContentsSQL Servervirtualization risks:among all the pros,some consSQL Servervirtualization: Notalways a good ideaway business run their IT operations, saving them money andmanpower, but when it comes down to databases such asSQL Server organizations should be sure to assess the prosand cons.This E-Guide will provide readers a checklist for SQL ServerVirtualization success and expert tips for noting the risks thatcome with virtualization.SQL Server virtualization risks: among all the pros, someconsBy: Alan R. Earls, ContributorIt is hard to argue with virtualization. Few technologies have had such asudden and profound impact on the way businesses run their IT operations,saving them money and manpower, all with scant glitches or snafus. Butwhen it comes to databases such as SQL Server, analysts warn there maybe a few “gotchas” -- namely, SQL Server virtualization risks -- lurking outthere.One note of caution came from Peter O’Kelly, principal analyst at O’KellyAssociates. O’Kelly said there are “waves,” or trends, in the IT industry, andthe current wave holds that virtualization is supposed to be good foreverything. “Now, industry is discovering that there are some places whereyou may want to dial that back a bit,” he said. “It is probably something thatneeds to be assessed on a case-by-case basis.”Virtualization might not always be a good thing for databases in generalbecause it may interfere with the heuristics of the database managementsystem for data access optimization, which is designed to work directly withdata storage devices.Page 2 of 9Sponsored by

SQL Server Virtualization pros and cons“Adding virtual storage may result in more disk access operations, and sincedisk access is measured in milliseconds while memory access [e.g., forContentsSQL Servervirtualization risks:among all the pros,some consSQL Servervirtualization: Notalways a good ideacached data] is measured in nanoseconds, the consequences can besignificant,” O’Kelly said. “The heuristics will break, the optimizer won’t doeverything it is expected to do, and that will create a problem.”Chris Wolf, an analyst at Gartner Inc., agreed that memory can be anAchilles’ heel for databases in virtualized environments. “Historically, peoplehave run into issues involving memory management,” he said.For instance, a few years ago hypervisors were using software to emulatephysical memory. And, as noted by O’Kelly, when you try to emulate memoryin software you run into bottlenecks and end up with slower response times.However, starting in the second half of 2009, AMD began to introduce AMDV Rapid Virtualization Indexing on hardware, and Intel early last yearreleased Extended Page Tables. According to Wolf, these developmentsallow virtual machines to manage their own physical page tables in memory.That removes the software bottleneck. “So a few years ago peoplevirtualizing SQL Server might have said it doesn’t run well, but with the rightarchitecture today, it isn’t a problem,” Wolf said.Another potential rough spot for SQL Server virtualization involves memoryappetite. “SQL will take as much memory as you will give it, and that willcause problems with resources sharing," Wolf said. "That’s why on a physicalserver, people must tune it to use as much memory as it needs, not as muchas it wants.”Fortunately, Wolf said, the tuning is straightforward. So, he advises thatinfrastructure people and SQL Server administration teams make a point oftalking about the issue and resolving it.The same can be said for making sure I/O is optimized. As an example, Wolfcited vSphere 2.1, in which VMware introduced Paravirtual SCSI. “It is a newstorage driver to provide accelerated I/O to access storage, and theyintroduced a new feature -- storage I/O control -- which lets you prioritizePage 3 of 9Sponsored by

SQL Server Virtualization pros and consstorage access for certain applications, so one app won’t take over all of theI/O,” he explained.ContentsSQL Servervirtualization risks:among all the pros,some consSQL Servervirtualization: Notalways a good ideaSimilar tuning issues concerned Greg Shields, an IT analyst at consultingfirm Concentrated Technology. Now, although almost anything can bevirtualized, Shields said implementation can still present challenges.It used to be that you could look at a server and say, that’s a networkproblem -- but with virtualized servers, it might really be a lack of processingresources.-- Greg Shields, IT analyst at Concentrated Technology“In my experience, most IT pros do not have a good handle on capacitymanagement,” he said. When the world was all physical servers, Shields saidexperience taught people to develop gut feelings for things like the supply ofmemory. “It used to be that you could look at a server and say, that’s anetwork problem -- but with virtualized servers, it might really be a lack ofprocessing resources.”According to Shields, what is needed now, especially with SQL Server, whichhas the potential for very high utilization, are tools to help administratorsconvert their data and metrics to actual intelligence. “By themselves, nohuman being can really do a good job of converting those metrics into usefulinformation,” he explained.“When you have those insights, maybe it means you don’t consolidate asmany machines or maybe in some cases it might mean you virtualize oneserver on one physical machine, as counterintuitive as that might seem,”Shields added.However, he stressed, the power of virtualization is such that the “overhead”of virtualizing is now so minimal that performance is “almost native” anyway.And that’s bound to be good news for those running SQL Server.Page 4 of 9Sponsored by

SQL Server Virtualization pros and cons“Today, with the right architecture, there is no reason you can’t run a SQLServer workload in a virtual machine environment,” Wolf said. “We have hadContentsSQL Servervirtualization risks:among all the pros,some consSQL Servervirtualization: Notalways a good ideamany of our customers doing this with large-scale databases. Our position isthat virtualization should be the default platform for all your apps in an x86environment. The onus should be on the owner to show why it isn’t goodrather than on IT to show why it is needed.”SQL Server virtualization: Checklist for successWith the right hardware, some planning and a few key steps, SQL Server willgenerally perform well in a virtualized environment. Gartner analyst ChrisWolf makes the following basic suggestions: Use the hardware-assisted memory virtualization feature becausewithout it, high-memory page latency will result in very poor performancefor any database workload at enterprise scale. Make sure there is relative equality among virtual machines. In otherwords, you need to have appropriate storage I/O, network I/O control anda resource pool so that in-memory activities are appropriately prioritizedfor more critical virtual machines. Implement tuning so that applications consume appropriate amounts ofmemory.And to avoid problems, Robin Layland, principal analyst at LaylandConsulting, offers these pointers: Make sure that virtualized applications like SQL Server are provided withthe correct virtual LAN number so that they can communicate with thehost server. Give your server load balancer information about the applications it willbe handling. “If you bring up another instance and don’t tell the serverload balancer, there is a good chance no one will be able to use it. If it isrunning five servers and you add another, it may still think you have five,”Layland said.Page 5 of 9Sponsored by

SQL Server Virtualization pros and cons When you bring up another instance of an application, coordinate withpersonnel responsible for server operation and network operation so thatContentsthey can make appropriate changes.SQL Servervirtualization risks:among all the pros,some consAlan R. Earls is a Boston-area freelance writer focused on business andSQL Servervirtualization: Notalways a good ideaSQL Server virtualization: Not always a good ideatechnology.By: Serdar Yegulalp, ContributorAs virtualization has grown more sophisticated, there is more incentive thanever to move SQL Server instances to a virtualized server. The number ofphysical machines goes down, along with power consumption and licensingcosts, and you can better manage the systems you do have since they’re allvirtualized. It all sounds like upside, but there are still a few major scenariosin which SQL Server virtualization won’t benefit you.When VM I/O is a poor substituteThe single biggest resource a database needs to perform well, apart fromCPU, is disk I/O. With a physical server largely dedicated to one SQL Serverinstance -- or even multiple instances -- it’s much easier to ameliorate I/Obottlenecks. You can change up disks for faster models, move from spinningplatters to solid state if it’s cost-effective in terms of IOPS (input/outputoperations per second) or add more random-access memory (RAM) toincrease buffering. The trade-off is cost, since such upgrades rarely comecheap.During SQL Server virtualization, though, the hardware must be as good asor better than the hardware you’re migrating from or database performancewill suffer. This is doubly true for I/O, since the last thing you want is to sharethe I/O bandwidth needed for a heavily used database. There are ways tomitigate that -- for example, you can put your database’s storage on physicalspindles and I/O channels that are deliberately segregated from other virtualmachines (VMs) -- but this must be done before you migrate to a VM.Page 6 of 9Sponsored by

SQL Server Virtualization pros and consHere’s a common example: Many servers dedicated to databases use RAID1 0 (also known as RAID 10) in their storage arrays, which is expensive butContentsSQL Servervirtualization risks:among all the pros,some consSQL Servervirtualization: Notalways a good ideayields the best results. A VM setup, on the other hand, might use RAID 5 asthe best trade-off between performance and redundancy. A workloadoptimized for the former hardware would suffer on the latter, unless you candedicate physical disks directly on the VM to replicating the original RAID1 0 setup.None of this implies that the disks have to be locally attached storage. Astorage area network on a VM of equal or better speed than local disks onthe physical box will certainly work. What matters is IOPS -- not the exacttechnology needed to achieve it.If you build the VM host yourself, you have some control. But if you usesomeone else’s VM hosting, you may be at the mercy of a one-size-fits-allsetup.When VM memory isn’t enoughDatabases benefit from having plenty of physical memory: it’s a place toperform operations and a cache for I/O. SQL Server works best on its ownstandalone machine for this reason, since it can freely provision out memoryas needed and not directly compete with other applications on the samemachine. A rule of thumb is to allow enough RAM for the entire database -or at least the most commonly used parts of it -- to be cached in memory,whenever possible.A VM version of the same database server needs to be provisioned with thesame amount of memory, or more, as its physical counterpart. Many VMsystems have memory-sharing techniques that allow identical pages ofmemory to be shared across VMs. But such sharing tends to be for thingslike the operating system running in each VM. Most of what’s sharablebetween VMs is not going to be the contents of the database itself.This is another area in which having memory-consumption statistics for yourSQL Server instance is helpful. You’ll be able to see how much memory yourPage 7 of 9Sponsored by