Webcast : 3 Tips for Successfully Virtualizing Your SQL Server

About this webcast:

Greg: Welcome to the MSSQLTips webcast: ”Succesfully Virtualizing SQL Server,” sponsored by Idera. I'm Greg Robidoux, co-founder of MSSQLTips and today's webcast host. Joining me today is Robert Davis who will provide some insight in how to implement SQL Server in a virtual environment. You'll hear more about Robert shortly.

Before we get started, if you have questions, you can submit them at any time during the webcast by asking your question in the question area in your presentation controls. We'll try to answer as many questions as possible during today's webcast. If you have any techNICal issues with sound or screen quality during the webcast, also use the question area to let us know.

If you're not already a member of the MSSQLTips.com web community, we invite you to join. You can go to MSSQLTips.com, sign up for our newsletter, read any of our tips, download white papers, watch archived webcasts, as well as post your SQL Server questions to be answered by our community members. This webcast will be recorded and archived for future playback and you'll receive a followup email with the links. Move to slide two, Robert?

So after the webcast, please take some time to visit Idera's website at idera.com to learn about all their SQL Server tool offerings and download a copy of SQL Diagnostic Manager. SQL Diagnostic Manager will help troubleshoot and solve performance issues fast. You can identify performance issues, set up automate alerting, high end assistance center and dive deep with all the information you need to diagnose and resolve SQL Server issues. So after today's webcast, please visit Idera.com to learn about SQL Diagnostic Manager and their other tools. So at this point, I'm gonna do a quick poll and then I'll turn it over to Robert.

So the first poll question here is: Besides SQL Server, what other database platforms do you use in your environment? Oracle, My SQL, Sybase, DB2, so we'll just take a few seconds here to get some answers and then we'll show the results. So we'll just let this go for another twenty seconds, people are still voting here. Looks like there's still people joining the webcast as well, so we'll get them to vote.

Alright, why don't we close this down and we'll take a look. So here the results are: so 53 percent also using Oracle, 39 percent My SQL, 10 percent Sybase and 18 percent DB2. Alright, I'll close that down and I'm gonna actually turn things over to Robert and he can begin his presentation.

Robert: Thanks a lot, Greg. Welcome everybody. As Greg said, my name is Robert Davis. I am the principal DBA at Outerwall, also a certified master and I hope to see a lot of you at the #SQLPASS Summit this year. I'll be presenting two sessions there. There's a lot more information on this slide, about me, if you wanna download the slide deck and take a look at the note section, uh, for this slide but I won't bore you with all the details.

I want to jump right into talking about successfully virtualizing SQL Server, so first thing I want to talk about is why you might chose to virtualize SQL Server. The key things for, uh, the key benefits of virtualizing are lower energy costs, um, because you have less physical machines running. Using a lot less overall energy. Um, if you have idle machines that are only active during certain periods of the year or only needed at very peak periods of time. You can shut down the idle machines until they're needed and bring them online quickly. That's a lot more difficult to do with, uh, physical machines. If you shut one of them down, you either need ILO burning or someone at the data center to bring you back up for you. So much, much easier to do with virtual machines. Um, they require less space in the data center, so if you have a lot of physical machines that are hosting a lot of really small applications you can end up taking a lot of space in the data enter, and your hosting cost could be through the roof just for hosting a bunch of small applications. If you could consolidate a lot of those machines with small work loads only to one or a few VM hosts running, uh, virtual machines then you could really lower your data center cost and save a lot of space.

Another key benefit is speed of delivery. Uh, you don't have to wait for new hardware to be ordered and brought in and landed, and wrapped and cabled and get the software installed and the operating system installed, uh, before you can put them to use. Um, and also VMs offer very handy snapshot and rollback capabilities so that you can take a snapshot of a VM and if something goes wrong or if you need to revert back to an earlier state, you can very easily just roll that snapshot back without having to go through a bunch of very tricky processes. This is very handy for, like, QA or testing your environments or you need to test things over and over starting from a known good state, so you can take a snapshot before you begin your test, run the test and then if you need to keep running at multiple times, keep reverting that snapshot back to the, um, known good and you can go back to that known good point in time in just a matter of seconds. Really reduces the amount of time you have to stand running, uh, multiple tests against your environment.

Another reason is that it eases hardware migration. In some sense you can think of what virtual machines, if done correctly, the migrated to do hardware, if you replace the hardware every three to five years, uh, with new hardware, it's very easy to do without any, or hardly any, time, um, downtime for the migration with mobility like vMotion or live migration, you can easily just move a VM to an old to a new machine with hardly any effort or any downtime on your part.

Um, hardware upgrades are no longer your problem as the DBA on the people managing the host yet manage the hardware upgrades behind the scenes. Um, and with the ability to move VMs around, they can do ___ hardware upgrades without you ever having to seeing nothing more than just a blip in your,um, activity. It also provide some additional availability in the disaster and recovery options. Especially when you consider things like, what I already mentioned , live migration or vMotion the ability to move a whole VM from one machine to another so if you have a VM host that goes down they can automatically move the VM to a different machine that is up without you having to setup anything special at the SQL Server level, um, to have this. This is additional layers of availability and disaster recovery that you have available to you to keep your systems up and running before you even get to the SQL Server level.

Uh, mobility, VHD backups. You can backup the VHD which is, which will backup the entire VM. And if you don’t have mobility setup you can always use the backup of the VHD as a disaster recovery option, quickly bring that up on a new server as a new new VM and then just point everything, all the VMS entries to the new VM.

It also offers host or guest clustering. We’re used to clustering SQL Server but now we also have the option of clustering the VM host as well so the host that host your VM could be clustered as well to provide additional availability at the host level as well as you can still use clustering of SQL Server withing the, um, VMs itself.

So just some tips for how to successfully virtualize SQL Server. My first tip is to start slow. If you're not virtualizing at all yet and you want to get started, a great way to get started with it is to first look at your non-critical workloads. So you an think of you know your development, test, sandbox or other non-production environment as being non-critical. Uh, but critical to a point as somebody needs to have those systems up in order to do their job. By noncritical, what I mean is that if they are down for a certain point of time your company isn’t actively loosing revenue. So by noncritical I mean if they go down its not gonna impact the revenue of your company. So development test and sandbox all those non-production environments are using the first things I looked at virtualizing, uh, that way we can work out all of the kinks, find out what, figure out what we do wrong so that we can define our processes before we get to the critical workloads. So start with those non-critical workloads fist before you move on to the other workloads.

And then the next one I would start with is small footprint servers. Now these may be critical, uh, servers but they are for applications that have a very small footprint so they can be like internal applications like perhaps an HR application or a payroll application that your internal teams use or it could be something like TFS or whatever source control your teams are using. Uh, yes these are, the-these may be considered production applications but they are very small footprints, they have a low workload or in many cases they are seasonal servers. And I even consider disaster recovery sites for production servers as a small footprint server because these will be servers that are designed to set their ILO until a disaster happens. So these are good ones to include in the, uh, second wave of servers that you move to a virtual platform.

And then last move on to production workloads, um and with production workload it's very critical that you test what you do before you move to production. You need to set it up, run some, a lot of stress testing, performance testing, uh, run some workload tests against it, and make sure that when you move a production workload to a virtualization, you need to really make sure, before you put it out to production use, that it's actually gonna be able to support the production use that you move it to. Uh, so more than anything its very critical to pred-to test production workloads before you put them out there.  Tip two is host configuration is important. First of all the host needs to be able to handle the loads. You cant just stick a bunch of servers onto another server and expect it to be able to handle any sort of load you throw on it. Um, so the VM host has to be very powerful, able to handle the load of any servers.

And you need to verify the performance, the power plan on the host as well. So hopefully, most of you are aware that the beginning with 2008, uh, the default is the balance power plan in windows 2008 and which means that your CPUs can be throttled down the same power, the problem with using effect balance power plan its that it takes a long sustained, very high CPU load before this processor power gets throttled back up, and so this is, uh, not a good power plan for SQL Server. I admit, maybe good for like web servers or other types of servers that have a very highly available workload, for something like SQL Server should be running in high performance power plan so that the processors are running at 100 percent all of the time. And you need to validate that with a utility CPU-Z. You can download that at www.seatstuid.com and because a lot of modern servers have the ability to throttle CPU behind the scenes, and I have seen  servers recently where we have high performance power plans set and yet and it showed that it was running at a hundred percent power, but then when we actually examined it with CPU-Z, it showed that the actual speed was much lower than 100 percent. We discovered that the HP server was using recurrent job has the ability to use CPU via ILO. ILO stands for Integrated Lights Out and it's the management utility next to the servers and gives you the ability to do things like remotely start stop and reboot servers um even if the servers insenseble. So ILO for HP has the ability to throttle CPU and so we discovered our ILO was throtling CPU despite having high performance power plans set to run the CPUs at 100 percent so to the operating system it looked like they were running the CPUs at 100 percent but behind the scenes ILO was actually throttling before the operating system had even solved the CPU so we had to change it at the ILO level to actually get 100 percent performance out of our CPUs and a lot of times a lot of servers will have the have the ability for you to throttle CPU via the BIOs as well. So if you validate your servers with CPU-Z and  you see that it still is not running at 100 percent power just by being a high performance power plan then you need to investigate the BIOs just to see if there's a setting in the bios that needs to be set to turn off CPU throttling and also ILO to see if its throttling CPU. So once you're sure that the servers are set to a high performance power plan and CPUs are actually running at 100 percent, then you can go into your VM and do the same there and set 100 performance power plan and any of those layers, if your CPUs getting throttled, then setting high performance plan at the VM levels won't help you,. So you need to do it all the way through all the levels of the server.

Need to make sure that your host has adequate storage. One of the, uh, mistakes I see happening a lot is that a lot of times people will put a lot of servers on VMs, on the VM host and even though you may lay out the storage according to best practices in SQL Server, behind the scenes the VM admin may be just throwing all those VHDs onto a single drive, uh, and some may be OK but I have a SQL Server workload typically if you're virtualizing production servers this could be a problem. So you need to make sure that as adequate storage to be able to appropriately handle all the different type of files and types of workloads.

Um, with SQL Server highly recommend that you do not over-commit SQL Server resources. Now it may be perfectly fine to over-commit memory or CPU or other types of workloads, like web servers where servers aren’t constantly busy, um, handling large workloads all the time. So if I got, like, a pool of web servers, uh, running, probably they are not gonna be busy all the time and it may be OK to over-commit those resources, but for SQL Servers definitely recommend that you do not over-commit resources for those. Make sure that CPU and memory resources are reserved, uh, disable balooning on the VM host so the it's not trying to take memory back away from SQL Server by falsely telling you that there’s memory pressure.

Also monitor your host server as well, so as a DBA we will probably be monitoring our SQL Server and our VM as of normal part of our practices but a lot of people never get ___ to monitoring the host server. And depending on the company you may not be able to, but if you're not monitoring it yourself you need to make sure that whoever manage it, that VM host is monitoring it, and if possible try to get inside into the monitoring that they have. Try to, at the very least, get read inside into what they are monitoring. Also, some of the monitoring sotware out there can also provide you a lot of information about your virtual platform as well. Like I know that Idera Diagnostic Manager has the ability to monitor VMware platforms as well. And I believe they are working on adding monitoring for the hyper-v platform as well.

Uh, mentioned this a little bit above but disable memory ballooning. Memory balooning is a process where it goes in and regularly tells the VM that there’s external memory pressure and so this will kind of prompt SQL Server to release memory, uh. SQL Server believes that there’s external memory pressure and may, if you're not sending lock pages in memory, it may release some of the memory it has to the host, even though it may need to use that memory. Uh, so disable memory ballooning to make sure that your SQL workload is very predicable and steady and not suffering from sudden drops of memory duet to signaling that it's … that there's external memory pressure.

And also, I mention this a litle bit above, but segregate the SQL workload to different drives. Just like you would separate out your different files on your SQL Server, you wanna put all those on different VHDs and make sure those VHDs are being segregated as well, Don’t just, uh, throw them all on one drive and expect to get good performance out of it. Uh, separate out the VHDs with the database logs and database files and and segregate out the VHDs that have typdb and backups and so on, to different dedicated drives on the host server as well. And for if you have VMs that are very everywhere votes, you may even end up using different dedicated ones for each VM.

Tip number 3. Your VM configuration is important as well. So it's important that you, and I mentioned this a little but in the previous tip but in the VMs as well, you still need to segregate your SQL Server workloads to different VHDs. Same as you would for physical server. In this respect treat it as a physical server where you lay out the workloads, um, but you need to coordinate that to make sure that it's not just different drives in the server. You don’t want it all on one VHD presented as a single drive with its partion into separate drives, you want them on actual different VDHs so that the workloads are going to separate files, and even if the host is gonna co-mingle all those VHDs on a single drive, I still do this. The reason being as I may in a lot of cases I may not be able to convince the VM admin that right from the very beginning that, uh, my system is is, uh, going to need to have all the VHDs separated out to different drives, or they may not even give me the option to, um, request that. But once we get running, um, and we can see … We're running in production, we can see that there is contention on the files that having them doing together is causing contention on the drives that are hosting those VHDs we can then use that information to, uh, get it moved to different drives at a later point. However, if were not segregating our workload to different VHDs so that the very beginning makíng that changed layer would be very painful. You don’t want to have to go back in later when you find out that having the VHDs on the same drive is a problem. You don’t want to go back in later and completely redo the drives that reconfigure the VM, um, for multiple VHDs. You wanna be able to just say, “Hey, we'll shut down for very brief blip move, uh. This VHD to that drive and this VDH to that drive won't be back online.” If you are able to prove that you do need to be moved out to dedicated drives then you want to be as easy to do as possible.

<Noise> Use Windows NIC teaming with resiliency. Um, so the virtual platforms have been growing very quickly and especially the Windows platform has especially been growing quickly and were taking a lot of considerations for virtualizing into Windows. Uh, Windows NIC teaming is greatly improved in Windows 2012 and works really well, and they have what they call resiliency in NIC teaming to ensure that, u,h the system keeps working. So if you are able to use have multiple NICs in your VM, use NIC teaming with resiliency to get the best performance out of your network connection for your VM. Also, with regards to the network connection, you may wanna consider addressing the net workload or the TDS stream, uh, to improve performance throughput, uh, at that level. So there are several, um, appliances out there you can do to compress the entire network stream, or there are also ... Idera has a new product out again to compress just the TDS stream between the server the SQL Server and the receiving client. So you get a lot of different options with the appliance versus the software solution and I have some blog posts and a white paper where I discuss this that you can take a look at, um, but consider compressing the net workload or the TDS stream to lessen the workload on the network in your VM. So it has a lot more networking layers to go through when its in a VM so the network performance is even more important in a VM.

Use the most current operation system. Now, with how easy it is to set up new VMs and everything, there's a lot of the hindrance to upgrading operating systems has gone away. So its very easy to split up a new VM with a new version of the operating system and migrate to it, and like I mentioned above, uh, when I was talking about NIC teaming, the operating systems have been coming very fast of the operating system level, uh, so if you’re doing a lot of virtualizations, especially your production workloads, it's very important to use the current OS. You get the best functionality and performance out of your virtual machine, um.

My next recommendation is to enable lock pages in memory. Now this configuration is highly debated for SQL Server in general, but I believe it's even more important to enable lock pages in memory, um, on a virtual machine because you do have so many more external processes and you don’t have control over whether or not your VM actually got reserved resources or not. Uh, I’ve seen a lot of situations with the DBA requested, you know, that they don’t over-commit and they don't give up dedicated reserved resources for their SQL Server, and at the time they may get it but then, uh, as the needs change on the virtual machine and they have more and more requests for virtual machines the VM admin may go in and change some of those settings. May turn balooning- memory balooning back on, may start over-committing things, just to make room for more VMs without having to add more hosts. So these things can change over time without your realization that they’ve been changed, and so in order to try to keep the SQL Server workload system consistent, uh, one of the things we can do is enable lock pages in memory so that if SQL Server does start receiving those fake messages saying that there's external memory pressure due to- memory balooning, uh, SQL Server can choose not to release memory and instead keep the pages that it has locked into memory and not have them forced out by external memory pressure signals.

Uh, another recommendation is do not span NUMA nodes if possible. This really will boil down to whether or not you need more processes for your VM that the host has per NUMA node. So if you got, uh, if you're, for example, if you're gonna have a VM with four virtual CPUs on it and the host has, uh, eight uh sockets per NUMA node, uh, then that's no ___  you can easily ad the defualt setting is for all of those virtual CPUs to come from a single NUMA node. But let's say you're gonna have a big beefy VM, let's say you have one with 32 virtual CPUs, but the host only has 16 sockets per NUMA node, obviously you're gonna need to span NUMA nodes in that case because you're not gonna give it up, uh, CPUs from a single NUMA node but when possible keep a single NUMA node as- use a minimal number of numa nodes necessary to be able to support the virtual CPUs for your virtual machine.

Um, use fixed size, static VHDs. Uh, do not use dynamic disk or thin provisioning with SQL Server. I find that particularly SAN admins love thin provisioning. Um, dynamic disks give you the ability to, uh, only allocate a small portion of the actual size of the VHD to the virtual machine. To the vir- if I use a dynamic disk, I can give you a dynamic disk of 500 gigabytes to a virtual machine. It looks like- on your side it looks like you have 500 gb but actually, on the host side, you only have 1 gb or 10 gb, and it will allocate out those disks as you need it. Thin provision is pretty much the same concept, You can use thin provisioning, um, at either the VDH level or it can be built in to the SAN as well. You need to make sure this is configured correctly, not only in the host, when you set the host, but also on the SAN so do not use thin provisioning because that that causes interrupts in your workload on those files and they have to calls for the drives to be extended behind the scenes, so do not use any of these. Always use fixed-size static VHDs.

And of course except for noted, follow standard SQL Server best practices. I get a lot of questions about one of my- “I'm moving SQL to a virtual server, what maximum DOPs should I set for that?” or “What should I set cost threshold?” or a lot of questions like that about best practice for SQL Server. Except for stated otherwise in here, general rule of thumb is, uh, configure your SQL Server the same way you would a physical machine. From the SQL Server point of view, very little is differet, uh, with regards to how you configure it. So just follow those same best practices you are already following.

Um, and again test it. I mentioned this when I talked about moving production workloads to a server, but there are a lot of things that can go wrong when virtualizing SQL Server. I don't say that to scare you, I say that to really emphasize how important it is to make sure everything is configured correctly and really test it before you put it in production use. And a lot- I would emphasize that even without virtualization, but it's even more true with a virtual platform so with the high level basic that can go wrong not just with SQL Server, the operating system and the SAN anymore and were not taking about all those levels at the VM host as well and everything that supports VM hosts, and the majority of the issues I see where people have big problems with virtualizing SQL Server have almost always boiled down to, uh, something with the way that it's configuration or set up. So poor planning poor testing before deployment can really, uh, mess up and cause you a lot of problems. It's very critical in the virtual platform to test it. Test it again, test it over and over. Uh, stress test it, test it with realistic production workload to ensure that you get the performance that you expect to get.

And now I wanna spend a little time, um, now that we talked about those tips I wanna spend a little time talking about licensing, uh, cause there are some significant differences with licensing SQL Server in a virtual platform versus licensing just a physical serve. And there are two key, uh, just to simplify this discussion, I’m not even gonna cover talking about server or client access licenses, or CALs, as you may know them, were gonna concentrate on four core licensing for virtual machines. So really what were talking about is do you wanna license the host or the VM per SQL Server. So host licensing, first of all, host licensing requires some kind of insurance which is an additional cost on top of the licenses. Um, and host licensing is Enterprise Edition only, so that's a very key deciding feature there. You have to buy an Enterprise licenses in order to license the host to host unlimited machines. So this is a very expensive options. You can run Standard Edition using downgrade rights, but you have to buy Enterprise Edition licenses, so you're not really saving any money by downgrading to Standard Edition. There's no real benefit to downgrade to Stanard Edition and the only time I’ve seen anyone actually do this is when for raises they could not figure out ... their server performed better on Standard Edition than they did a Enterprise Edition and in these cases I wasn't involved in the troubleshooting, and so I don’t have any details on why exactly their workloads performed worse on Enterprise Edition, but I've seen that in a few forms for people, after spending time with Microsoft support trying to figure out why it performed worse on Enterprise Edition, they ended up just downgrading to Standard. It is a possibility you can do with these licensing model, um, but outside one of these weird edge cases there's really no benefit to downgrading to Standard Edition if you're licensing for Enterprise. Um, with VM licensing, as the software insurance is optional, but it is needed for some features, so even if you go for VM licensing you still may wanna get software insurance to be able to take advantage of some of the features that are required. So one of the good signs of VM licensing just the VM is you can do this for any edition of SQL Server. You can do it for BI or Standard Edition, um, whatever any edition of SQL Server on a VM if you're licensing a VM licensing model. And you can mix workloads here so, um, if you're licensing the host to host unlimited SQL VMs, you're not gonna wanna take up the resources with that server- with web servers or application servers, you're gonna wanna dedicate that server to run nothing but SQL Server VMs, otherwise it's a waste of SQL Server licenses, and Enterprise Edition olicenses are tens of thousands of dollars. You're not gonna want to throw tens of thousands or hundreds of thousands of dollars away by simple running webservers or application servers on that host. But if you are using the VM licensing model you absolutely because you're only licensing those SQL- or those VMs are actually using SQL you can put VMs over on that host, not be- not waste your money. And this model, uh, typically is a much cheaper solution, um. So I’m I'm gonna ask your hosting so many SQL Servers that that, um, on that host that that, uh, the Enterprise Edition that, um, desk cost becomes prohibited. This is a much cheaper version if you are hosting a lot of SQL server VMs and you do need Enterprise Edition on all those SQL VMs, then there is a point where host licensing becomes a cheaper option, but those are really extreme cases. Most people that are virtualizing SQL Server are gonna find that licensing the VM itself is gonna be a much cheaper option. So use host licensing for VMs, license the host for unlimited SQL VMs if you need Enterprise Edition of SQL server on all of the VMs. If the host is dedicated to VMs and it runs SQL Server only and the number of VMs that it would be hosting exceeds the licensing requirements for VM licensing. Use VMs licensing if you don’t use Enterprise Edition on all servers and the host, um, may support a mix workload on different kinds of VM servers and of course you don’t have an unlimited budget. As I said before, licensing a host for unlimited SQL VMs is a very expensive option, so most budgets are unlimited so many people aren't gonna be able to consider that. And this moral makes sense 90 percent of the time. That's just a estimate that I came up with, uh. I wouldn’t be surprised if the number's if actually much higher than 90 percent. That brings out the question, do you need software insurance? So SA, as I mentioned is a extra cause on top of, uh, licensing, uh, so in there’s some nice things nice features you get with that saying, but from a DBA standpoint whether or not you need it depends on what, uh, technical features you can take advantage of, so like host licensing, if you wanna be able to license the host for unlimited SQL VMs, you have to have software insurance now.

Uh. mobility rights. VM hosts such as vMotion like Vmware, live migration, and hyper-V, without software insurance the license is tied the host so if you wanna be able to move, uh, VMs from one host to another, um, and you don’t have software insurance you have to license that VM on each host separately, so if you wanna be able to move between host you have yo license it twice, once for each host. So if you have a lot of SQL VMs that you wanna move back and forth, software insurance will be a much  cheaper option than behind twice as many licenses.

Um, With SA, the license moves with the server to the other host so you get mobility rights of the license with moving the VM around. It's also required for fail over rights now. For many years, we basically thought of having a passive fail over for SQL servers being free. For many years we knew that we could have a fail over server like a passive fail over cluster or mirroring partner database mirroring or a paasive replica in AT or log shipping or any sort of passive fail over we could have. And the licensing for the active node would cover one passive server as well, so one active server license can also cover one passive server. So Without SA, you don’t have to license that passive server that started with, uh, that was a recent changed in licensing so as of right now you have to buy licensing for your passive fail overs if you don’t have software insurance. With SA one license active server covers one passive server as well.

Um, any questions I can answer at this point.

Greg: Yeah, thanks Robert. Actually, before we get to the questions, let's just do our second poll and we have a good amount of questions so far. If there are other questions people have, just feel free to put them out there and we'll try to answer as many as we can. So let's do this second poll real quickly. So the second poll is: Are you using a cloud based database service, and if so which one? Um, so Windows Azure, Amazon RDS, Rackspace, Oracle Cloud Service. Sorry, there’s no none-option, um, so if none of those apply I guess don’t answer the poll, but if you wanna take some time to fill those out and then we'll get to some questions. A few more questions are coming in and while we're waiting for this, um, as far as the slides, the archive, yes, will get a copy of the slides on the internet and you get a followup email where you can download the slides as well as being recorded, we'll post the archive again in the followup email that you get, you will have a link to watch the archive as well. So let's give this another 10 seconds, we'll close and get to some of the questions.

Alright, I’m gonna close this and look at the results real quick. So 64 percent pf the people that voted said they're using Azure, 22 Amazon RDS, and then a few people with Rackspace and Oracle, so OK, let me close that. And OK, so why don’t we get to some questions. Alright, let's see, the first question here. The first question, Robert, is related to lock pieces in memory and the question is, if there is not enough resources, um, will this cause memory pressure and then eventually fail over to disk paging or even effect the guest at the host level?

Robert: Uh, yes, I actually have a white paper coming out on virtualization as well and I go a little bit more in depth in the white paper on this topic, and so one of the, um, standard recommendations I always make for SQL Server, whether its virtual or physical, is before you set lock pages in memory, always make sure you set the max set memory, um, appropriately for your server do you need to figure out what the bestmaz set memory for your server is set that make sure the SQL Server isn’t allowed to consume all memory on the server. Um, if you fail to set max server memory and you set, uh, and you set lock pages in memory, SQL Server can take a part the memory on the server and if something else needs the operating system needs it and definitely you'll experience external memory pressure that isn’t able to be released by SQL Server releasing memory. Those very critical that's whether it's virtual or not, never set lock pages in memory until you appropriately release that max server memory setting. 'Cause that does allow SQL server to not releases memory if the operating system is under pressure.

Greg: OK, great. The next question here is about HA solutions and the question is. “Would you recommend using a server versus a primary and a VM per fail over? And if so what type of HA solution would you recommend with that kind of configuration?”

Robert: So it really depends on what your particular needs are for the application. What the chances are that you’ll be running your server on the failover, uh, and if you wanna be- how much of a load you wanna be able to run on the second server in general and as a DBA I'd like to recommend that the DR server be able to manage the heap loads of the server that is possible that you can have a virtual machine that’s perfectly capable of handling the same load as a beefy physical machine, um, for failov- uh so it's if configured and done correctly, yes, its perfectly fine to have your DR or HA failover on a virtual machine, even though the the live machine is physical. I think that's perfectly fine to do, but I think it also depends a lot on what layer HA or DR this virtual machine is, so I like to approach HA and DR as, uh, I'm building multiple levels of protection, so like, I may use to cluster virtual machines and then have a log shipping failover to a virtual machine or, uh, may decide to use availability groups and have a primary, uh, known on a physical machine and a primary failover known on a physical machine and just as fail safe in case allthose go wrong may have a third node that’s on a virtual machine or even now see what on the 2014, I may even put that third node in the cloud so, yeah, that’s definitely one of the options but you need to consider what kind of workload you’re gonna be able to handle once you once you fail over to it. As long as you can handle the workload, um, I have absolutely no problem with that DR HA failover being in a virtual machine.

Greg: OK, the next question ties into part of that answer you gave and the question is, if all the latency and disaster recovery built on the Vmware, do you still feel there’s a need to cluster production SQL Server?

Robert: I-I do still think there's a definite need for clustering these days. Generally though, I- if I’m virtualizing in production server, I would typically use only the host clustering. Uh, I know guest clustering is a new thing and it sounds very popular but if I’m gonna be clustering I would prefer to cluster just the host server and then maybe rely on something else like mobility, live motion, or vMotion, or live migration to be able to move that server around to different hosts as opposed to using guest clustering, so if I’m in virtual machines I don’t use I don’t cluster SQL Server itself. I may, um, one exception in that is if I am putting SQL Server on virtual machinse and I want to be able to use ability group that does require clustering so in that case I would probably just consider using clustering at the guest level so that I could use availibilty groups on top of that. But otherwise, if I’m just considering clustering as my availability option, I would probably look at clustering the virtual machine rather than the individual SQL Servers.

Greg: OK, next question is related to physical servers versus the virtual server and the question is, if you have well-configured virtual SQL Server, will a physical outperform  virtual servers or are there ways to get comparable results for myo memory CPU etc?

Robert: Well, there are definitely additional overhead, uh, for virtualization, um, but if the virtual server is configured correctly and optimally, it absolutely get almost the exact performance with the virtual server that you get with a physical server with the same resources. Uh typically, uh, though, when were taking about virtualizing SQL Server were not comparing a virtual server to ...

Dog: WOOF WOOF WOOF!

Robert: Exact same physical server, we're, uh. Typically, what were looking to do when were moving to virtual server is moving to a virtual server that uses less resources. So majority of what we put on VMs aren’t gonna need the exact same performance as they get from as they currently get from the physical machine. And I’m talking about that second class machines that I’m talking about it's critical workloads but they’re small footprints servers. We're putting them on the smallest Enterprise class servers available that are much beefier than what is actually needed by those applications, so if the. I can give application of VM much smaller than the smallest Enterprise class server and get all the performance I need without getting the exact same performance. But if you compare apples to apples exact same number of CPUs and memory and the disk is all the same, the difference in performance between a VM and a physical machine would be very hard to detect. It would be very little bit of different, but such a small difference that you would have a really hard time finding it. Like I said in the webcast, usually, when people are saying big differences is because something has been configured incorrectly or they’re doing something on the host such as throwing all the VDHDs on the same drive. Something that you wouldn't be seeing on the physical server

Greg: OK great, actually would you mind moving to the last slide Robert? I'm just gonna wrap things up. So there are some questions we can get to. What we'll do is I'll compile those and send those to Robert and if he has time he can try to get back to people with some answers or maybe we can get some tips written, um, that address some of these questions. There are definitely a lot of good questions out there. Uh, I'm just gonna wrap things up here and thank you Robert for all the great information I also want to thank Idera for sponsoring today's webcast. The webcast was recorded and you'll receive a followup email with the links to view the archive as well as download the slides. Once again, take some time to visit Idera's website at idera.com to learn about SQL Diagnostic Manager and all of their other free tools. Also visit MSSQLTips.com to access all of our SQL Server- free SQL Server resources and I want to thank everybody for attending today, and ask everybody when you exit the webinar, just to take a few minutes to fill out the survey. The feedback helps both today's webcast as well as future events. Once again, thanks for attending and I hope y’all have a great day.

Robert: Thanks Greg. Thanks everyone that attended today.

Robert L Davis

Robert L. Davis is a SQL Server 2008 Certified Master, an experienced DBA, evangelist, speaker, writer, trainer, and consultant. He is the Principal DBA with Outerwall, Inc. and consults part-time via SQL DBA Master. He has worked with SQL Server for more than 13 years, including recent stints as a Sr. Product Consultant and Chief SQL Server Evangelist for Idera Software, Program Manager for the SQL Server Certified Master Program at Microsoft Learning, and as a production DBA at Microsoft.

Try SQL Diagnostic Manager for SQL Server FREE for 14 days
SQL Diagnostic Manager Repository dashboard

24X7 SQL performance monitoring, alerting and diagnostics

  • Monitor performance for physical, virtual, and cloud environments.
  • Monitor queries and query plans to see the causes of blocks and deadlocks.
  • Monitor application transactions with SQL Workload Analysis add-on.
  • View expert recommendations from SQL Doctor to optimize performance.
  • Alert predictively with settings to avoid false alerts.
  • View summary of top issues and alerts with the web console add-on.
Start for Free