December 21, 2010 at 6:58 am
Hi all,
I apologize in advance for this is gonna be a long post but I'd rather give too much info than not enough.
Our tech departments wants to reduce our hardware and re-use some for other tasks. One of the machines they'd like to take is our SQL Server which is quite powerful, more than needed actually.
So I have to decide between sticking to that physical machine or virtualizing our SQL Server and frankly, I don't know if I like the idea or not. I'm the first one to advocate virtualization when it comes to terminal servers or application servers but I don't know if it's such a good idea with servers doing lots of disk I/O like SQL Server.
This is about SQL 2005 Standard Edition SP3 currently running on Windows 2003, both 64 bit.
The current machine is a SuperMicro server with two quad-core CPU's, 32 Gigs of RAM and a RAID controller. We have a 450 GB SAS Drive where we keep the databases and a 1 TB WD Caviar Black Sata II drive for the Transaction Logs. Both drives are mirrored.
We use Red Gate's SQL Backup with two full backups a day, one differential backup at noon and transaction log backups every 2 hours. These backups are stored on the 1 TB Drive (same as our T-Logs) and also copied to a another drive with Red Gate's network copy feature.
We run all four SQL Services (SSDS, SSIS, SSRS, SSAS) on that machine and use them all.
SSDS: We have 30 databases ranging from a few hundred KB's to over 28 GB's. The 28 GB one supports our MRP systems and all the operations associated with running our company.
SSIS/SSAS: We really don't use it much, I have just a couple of packages which run twice a day to generate/update an OLAP cube that is accessed by about 15 people a few times a day each.
SSRS: We don't have many reports on the server but they are linked to our live databases and they are critical to our normal operations. They give our people the information they need to do their job when they need it. I'd say it's running reports around a 100 times a day.
Now back to our databases. Our MRP system comes from a 3rd party, it's programmed in C# .NET and relies heavily on stored procedures, user functions and database triggers. The problem is that it is all very badly coded and uses cursors like you wouldn't believe. It has cursors in SP's calling SP's that have cursors calling SP's that also have cursors. It has SP's with cursors calling themselves inside the cursor loops with different parameters. It has nested cursors and even cursors and nested cursors in triggers :sick:. Needless to say, the whole thing looks like a bad joke and is slow as hell.
And to add to that, the .NET app works with huge datasets which are populated by these "cursored" SP's.
And to add even more, most of it (if not all of it) runs inside transactions. Yes.......in case you're wondering, we do face problems of transactions running for minutes and locking up the entire tables and kicking people out. Some of our users do wonder why they are chosen as victims of something so often :w00t:. I guess that gives a good idea of what can be going on in the transaction logs.
I think that pretty much covers our environment so back to virtualization now, here's what's been proposed to me by our tech department. They want to convert this powerful machine into a Hyper-V server. I'll spare you the details on how they wish to proceed but down the lines it comes to converting the current physical machine into a virtual machine on a Hyper-V server hosted on Windows 2008 R2 and then upgrade the Windows 2003 OS of the virtual machine to Windows 2008 R2.
Here are the things that I'm concerned about.
Memory speed: A 30 GB database running cursors and generating datasets all day long probably uses memory a lot. Will a virtual SQL Server experience slow memory or is it gonna be as fast as when it runs on the physical machine ?
Transaction logs: That bothers me a lot actually. Is it conceivable to run a 30 GB database with a 20 GB transaction log on virtual disk when the amount of I/O that all these transactions and cursors are generating ?
Is it a really bad idea to virtualize this ? Are there any other things I should consider ?
And we do virtualize it, is converting the physical machine to virtual and upgrading the OS a good idea ? Are we gonna run into all kinds of problems ? Should we create a virtual machine from scratch and re-install all the SQL services and databases ?
I'm one of those people who ended in charge because there was nobody else and although I think I'm doing a good job with our server, this kind of stuff really is beyond me level of knowledge and I really don't have the expertise to make that kind of decision......but I have to make it anyway so I will be very grateful for any help I can get on this.
December 22, 2010 at 2:19 pm
Should I take the lack of answers as an indication that virtualizing SQL Server isn't popular and may not be a good idea ?
December 22, 2010 at 2:39 pm
It's not that virtualizing is a bad idea or that people on the forum don't support it. 🙂 The post is indeed long and the questions are probably something that should be discussed in greater detail, maybe as a project with a consultant.
Ultimately the determination to move to virtual is something that should be tested. I'm sure that you can find plenty of success stories as well as failures. The key is understanding what your environment requirements are and can you meet them with a virtual server.
A couple of questions I would have are;
1. Are you looking to do a like for like as far as the resources on the Virtual server?
2. How is your IO performance presently? What are your read and write performance counters showing?
3. How is your memory utilization presently? Is you Page Life Expectancy solid?
4. Do you have people experienced using virtualization so that if you run into performance issues you are going to have support for troubleshooting, tuning, etc?
5. Is is possible to set up a side-by-side install to production and run a test copy of the application against it to see how it performs?
Really, based on what you are describing it doesn't sound like your environment should be a challenge to virtualization. My main concern would be at the disk / IO layer. However, the move should not be taken lightly and the cost savings are not always as dramatic as people make them out to be.
I'm not sure if that helps or not. I will say in closing that I have kicked against it on a couple of occasions as the performance was causing problems but I have also now seen those issues worked through and a very solid SQL Server environment put up that runs very well.
If you have specific questions I can try to answer with experience that I have but it is not deep for sure.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 22, 2010 at 3:25 pm
I, personally, find Brent Ozar's blog[/url] very informative ,especially including the comments and the follow-up article. Those two articles combined give a neutral (if possible at all) point of view why you should or shouldn't go for virtualization.
Regarding your specific situation:
If you can get some figures in terms of I/O utilization as well as tempDB and CPU usage you might be able to support the argument that you need such a heavy machine as a dedicated SQL Server. That's step 1.
Step 2 would be to describe the reason why you need a Server of such a size (the c.u.r.s.o.r. *cough* nightmare with some figures could help).
Step 3 would be to get the users involved with a statement how much of a slow-down they're willing to take. 😉
A recommendation could be to get the vendor to speed up the app by replacing the messy code and based on that open the server for virtualization.
With transactions running for minutes, locking up the entire tables and kicking people out we'd consider immediate action as well. But the word "virtualization" is nowhere close to what we'd do... ("performance improvement" or "app replacement" would be much closer)...
If you virtualize under the current scenario you're facing the risk of even more users being kicked out and even lower application response time.
December 22, 2010 at 3:46 pm
David, Lutz,
Thank you both for sharing your thoughts on this. I agree that checking out I/O performance is the first thing I should do, if that is as "bad" as I think it will be I guess I'm gonna have my answer immediately.
LutzM (12/22/2010)
If you virtualize under the current scenario you're facing the risk of even more users being kicked out and even lower application response time.
Lutz, that sounds like you're saying that generally speaking, virtualizing a SQL Server will impact performance and that it's all a question of whether it will slow it down enough to be noticeable or not. Did I interpret this correctly ?
Unfortunately, having the app recoded (the correct word should be fixed or repaired :)) or replacing it is not an option. We just need to make the best we can with it at least for now.
There's a process that we need to run mid-morning every day, it runs for about 35 minutes. This is 35 minutes every day where nobody can do anything with the system because they'll just get timed out on transactions.
On a 30GB database I had a 15GB transaction log and it wouldn't go a day without expanding when I was backing it up every 12 hours. I think that falls well into the high I/O category 😀
December 22, 2010 at 4:20 pm
Gagne (12/22/2010)
...Lutz, that sounds like you're saying that generally speaking, virtualizing a SQL Server will impact performance and that it's all a question of whether it will slow it down enough to be noticeable or not. Did I interpret this correctly ?
...
On a 30GB database I had a 15GB transaction log and it wouldn't go a day without expanding when I was backing it up every 12 hours. I think that falls well into the high I/O category 😀
Virtualization basically means to share hardware resources, or to allow other "processes" (= virtual machines) utilize free resources on a given machine.
There are basically two scenarios:
1) your (formerly dedicated SQL) server will be used as a host for virtual machines. Then you'd have to share resources. Example: As long as your current system never exceeds 90% CPU utilization and you don't have anI/O issue at all, you'd hardly recognize a VM utilizing 5% of the CPU with low I/O impact. It won't impact the SQL Server performance in general.
2) Your server will be moved to a (more powerful) hardware, but will run in a virtual machine. Assuming you'd have to share the hardware with other systems consuming much less CPU and I/O and your system had hardware driven performance issues before, you could even benefit from going virtual.
It all depends. Therefore, there's no "generally speaking". Unfortunately.
Regarding your transaction log: you might want to perform a transaction log more frequently (the setting on our production system is 15 minutes...). But that's a different story. More or less (because of the file size issue).
December 22, 2010 at 5:22 pm
LutzM (12/22/2010)
Gagne (12/22/2010)
...Regarding your transaction log: you might want to perform a transaction log more frequently (the setting on our production system is 15 minutes...). But that's a different story. More or less (because of the file size issue).
I changed that a while ago. If I recall it was after I had 50 users ringing my phone or walking into my office to complain that nothing was working............which turned to be caused by the expansion of the transaction log. It's backed up every 2 hours now.
But back to the point and the first part of your latest post. The CPU on is actually pretty much idle all the time, processor is really not a concern but we are conscious that we'll have to be careful on how much load we put on that machine if we turn it into a hyper-v host.
Disk I/O however can be an issue. I was reading a white paper yesterday on virtualizing SQL Server and there was something about "physical I/O path". I haven't had the time yet to take a close look at that but if I understood well, it's a way of allowing access to the physical disks to a virtual machine which would obviously improve I/O performance. This is definitely something I need to consider.
December 23, 2010 at 4:35 am
I was reading a white paper yesterday on virtualizing SQL Server and there was something about "physical I/O path".
Care to share the link? Others might benefit from it.
December 23, 2010 at 6:28 am
Yes of course, I should have posted the link. It's about SQL 2008 but I'm sure it's good for 2005 as well.
December 23, 2010 at 9:01 am
We run our SQL servers on VMWare and have been very happy with the performance. BUT, we are a small organization with a lightly loaded primary SQL server with a few hundred users, most of whom are either reading small amounts of data or updating selected rows.
We subcontracted most of the VMWare setup to a consultant who is very experienced in setting up SANs w/ VMWare servers, which has been essential for our success.
If your primary app. is so poorly written, is it a resource hog? Something I didn't see you mention: if you virtualize and SQL server is sharing CPU/RAM/IO with other servers, then the other servers may suffer if you aren't careful.
I like the previous suggestion to create a parallel test environment. I know you said upgrading your application is not a possibility, but it honestly sounds like you'd likely get more performance bang for your buck by doing that than you will by virtualizing. Maybe if you ran the numbers, you and management would have something meaningful to compare?
Good luck,
Rich
December 23, 2010 at 9:24 am
rmechaber (12/23/2010)
...it honestly sounds like you'd likely get more performance bang for your buck by doing that than you will by virtualizing. ...
Just to make sure there is no misinterpretation: there will be no such thing like performance improvement due to virtualization itself.
As long as you use the same hardware there won't be any benefit performance wise. You might benefit from reducing the number of servers required. But at the best you'll get the same performance. It's a different story when bigger hardware is used to run multiple virtual machines on it. In such a case you benefit from the hardware, not from virtualization. Take away the virtual machine and run your app on the bigger hardware and you'll see the same improvement. 😉
Other than that I second Rich's comment. Especially the part using experienced VMWare consultants for the setup. That's definitely a key factor for success.
December 23, 2010 at 9:49 am
Just to make sure there is no misinterpretation: there will be no such thing like performance improvement due to virtualization itself.
I should have been clearer. I didn't quite catch the idea that you were planning to take existing hardware, use it for a virtual server, and then put SQL (and other servers?) on top of that.
Lutz is quite correct, your performance will likely suffer. How can you expect it to improve or even remain the same if you are running additional software on the same hardware? The HyperV or VMWare software incurs overhead. Hardware abstraction is not free.
One thing our consultant was clear with us about was to buy and build sufficient resources (ESX servers, RAM and SAN) to be larger than our typical load requirements would suggest. That way we can take down one of the ESX servers and all of our host servers will first migrate seamlessly to the other ESX servers. Users don't even know it happened. It's remarkable.
I would be very wary of putting an 800-pound gorilla app. such as you described onto a virtual setup, especially a poorly behaved gorilla. I'm a little confused. Your SQL server you describe as "our SQL Server which is quite powerful, more than needed actually." But you describe the primary SQL application as "Needless to say, the whole thing looks like a bad joke and is slow as hell." If throwing more hardware at your current application hasn't enabled it to function properly, it won't get any better as a virtual server. This is a classic example of an application in need of re-coding.
We've been very successful with ESX, and it has exceeded our expectations. We have linux, Windows, and Netware servers all running side by side. We deploy test servers from a template in 15 minutes, run them for as long as we need, then simply delete them. But we don't put big, resource-hungry things like email on it.
Rich
December 23, 2010 at 9:56 am
LutzM (12/22/2010)
Gagne (12/22/2010)
...Lutz, that sounds like you're saying that generally speaking, virtualizing a SQL Server will impact performance and that it's all a question of whether it will slow it down enough to be noticeable or not. Did I interpret this correctly ?
...
On a 30GB database I had a 15GB transaction log and it wouldn't go a day without expanding when I was backing it up every 12 hours. I think that falls well into the high I/O category 😀
Virtualization basically means to share hardware resources, or to allow other "processes" (= virtual machines) utilize free resources on a given machine.
There are basically two scenarios:
1) your (formerly dedicated SQL) server will be used as a host for virtual machines. Then you'd have to share resources. Example: As long as your current system never exceeds 90% CPU utilization and you don't have anI/O issue at all, you'd hardly recognize a VM utilizing 5% of the CPU with low I/O impact. It won't impact the SQL Server performance in general.
2) Your server will be moved to a (more powerful) hardware, but will run in a virtual machine. Assuming you'd have to share the hardware with other systems consuming much less CPU and I/O and your system had hardware driven performance issues before, you could even benefit from going virtual.
It all depends. Therefore, there's no "generally speaking". Unfortunately.
Regarding your transaction log: you might want to perform a transaction log more frequently (the setting on our production system is 15 minutes...). But that's a different story. More or less (because of the file size issue).
Here's another aspect to consider: just because you viritualize you server doesn't necessarily mean you HAVE to share the underlying hardware. We've virtualized a LOT of our servers, simply because it becomes much easier to swap out the "underlying plumbing" without huge conversion routines/server moves/etc....
I am not sure of all of the magic our intrastructure guys pull to make this happen, but they've done a really good job of just that. A week ago, the hardware our big primary DB ran on started showing signs that some of the hardware was going, so they "moved" it from one physical box to another while the server was up. Sp the virtual instance stayed up even after the physical hardware was taken out.
So the question might turn into - how do they plan on making the virtualization work? can they make sure you odn't lose the performance edge you need, etc....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 23, 2010 at 11:19 am
I think I haven't been clear or maybe my original post was too long and this got lost in it.
We are not looking into this because we want to virtualize our server. We need a second hyper-v server and with the economy being what it has been for 2 years we'd rather not spend 8000$ on it........at least not right now.
Since our actual physical SQL Server is quite powerful, it can be turned into a Hyper-V server but if we do that I need to move that SQL server somewhere and I'd rather no run it alongside anything else which is why we're looking into virtualizing it.
I'm not really worried about CPU and memory, I am really much more concerned about physical disk I/O and whether I should virtualize the disks or give the VM direct access to the disks (physical i/o path).
One thing I feel needs to be closely looked at is the possibility of a power failure. If, for any reason, our UPS doesn't do its job and doesn't properly shutdown the server there's a risk of damaging the transaction log and end up being unable to restart the database which would for me to restore the most current backup.
December 23, 2010 at 12:33 pm
Ok, so at least you have a budgetary number to compete against... 😉
Get a quote from your vendor how much it would cost to replace the "heaviest" c.u.r.s.o.r.s.
Once that's done, your I/O wont suffer that much anymore and you might be able to use the hardware as a VM host.
On the other side you should really consider a backup strategy for your server. So, a second system used as a VM host but being able to be used as a backup system for your app might be the way to go.
A UPS will protect you from a power failure. But how about hardware defects?
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply