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
. 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
. 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.