June 16, 2011 at 7:43 am
Chris,
I have 3 SMSQL articles at MSSQLTips.com. I talk about backup performance gains, configuration, and implementation best practices. SMSQL has been a challenge and has a high learning curve. I am more than delighted to talk about my experiences (good and bad) with configuring SQL Servers for SMSQL (and it really is configuring SQL Server for SMSQL and not the other way around). We spent a lot of hours with trial and error to get it right.
June 16, 2011 at 7:49 am
Excellent points. In fact, VMWare would agree with you (and me too) that not everything should be virtualized. SQL Server implementations tend to be smaller, as a general rule, than something like Oracle. Though we have over 200 production systems few of them hit the 8 vCPU limit for VM. We do have an application that monitors patients in hospitals and pernatal care that we did not virtualize and there was one other nurse-on-call cluster server we tried to virtualize but it just wouldn't work. Those are the only exception.
I did forget to mention that our VM team ran Capacity Planner for 3 weeks prior to any P2V. This provided them a good estimate for resources. Our VM project was about as a agressive as you can be and I put it out there as an example as a oppossed to a recommendation.
June 16, 2011 at 7:49 am
Shared cpu cycles and overcommitted RAM allocations, complete loss of control over system performance, licencing issues, and possibility of failed backups in which the blame falls on the dba.., server sprawl, hardware failures now cause multiple server outages, especially if the farm cant take additional load (highly likely if virtualization was a cost saving measure). Failing to see any positives for the dba.
June 16, 2011 at 7:50 am
Thayal,
I have articles at MSSQLTips related to SMSQL. Please let me know if you have any questions.
June 16, 2011 at 7:53 am
i recently did a test conversion of 2 SQL servers to VMware. a total of 3GB of databases. the developer i worked with who owns the data gave me some push back because according to her these were the most important databases in the company and we should have bought one of the new monster servers on the market for them.
in the end it went smooth and no complaints. in fact i think our HD ticket system is now faster that the SQL is on vmware
for backups we still use netbackup and it works just fine. i don't get the love with native sql backups here. much slower compared to modern tape or d2d tech
if there was a push to VM our main servers i wouldn't complain since it's a killer DR solution. no messing with configuration data or whatever. just mount the VM image at your DR location and you are ready to go. tomorrow i have to stay up late for a SQL hardware migration. if it was vmware it would take all of 15 minutes or so for a vmotion and to verify things work
June 16, 2011 at 7:54 am
slaberer,
Be sure you are running the latest version of ESX. You should be running ESX 4+. VMWare made significant changes to the CPU Scheduler. http://www.vmware.com/resources/techresources/10059
Also, when looking at IO issues in VMWare make sure you look in vSphere and not perfmon. Perfmon on VM, espcially for time-based metriqs, can be misleading and downright inaccurate.
I can honestly say (and I don't work for VMWare 🙂 ) that we have not found any performance issues related to the migration to VM.
June 16, 2011 at 8:01 am
Mark,
Thanks for the positive words. VM changes everything - especially for the DBA. It changes how we think about managing databases. It changes how we support and configure them. For example, Microsoft clustering is a thing of the past now. Our P2V only migrated the virtual cluster name and all the cluster nodes disappeared. I wrote an article on MSSQLTips on how to dismantle a cluster after a P2V. http://www.mssqltips.com/tip.asp?tip=2355
VM has changed how we monitor databases (perfmon lies) and how we backup and restore them (SMSQL). In a virtual environment DBA's really have become "infrastructure" DBAs. We used to only need to understand the local physcial environment but now we have to understand the entire storage system from SAN to host to VM. I find it both a daunting time but also an exciting time.
June 16, 2011 at 8:02 am
There are many benefits to running a SQL environment on VM. I was part of an IT shop where we had to move the entire infrastructure to HyperV. It was difficult and took 3 times longer than planned, but I learned a lot and I no longer had an excuse for not having appropriate dev/stage environments for development.
Tip * make sure you have a crack-pot hardware team. Dual nics competing for IPs on the physical hardware caused major headaches and was very difficult to diagnose.
Aigle de Guerre!
June 16, 2011 at 8:04 am
Henry,
Absolutely! VM benefits storage and server teams but, most of all, it benefits the CIO, the COO, and the CEO. Since we DBAs are along for the ride I guess we might as well try to figure out how the car runs, right? 😉
June 16, 2011 at 8:12 am
Scott
I would say that your article is spot on speaking from experience. My old environment was built from the ground up to be virtual and I found myself working very closely with App Developers and Infrastructure Engineers. Fortunately, our communication was excellent and when issues came up all teams pitched in to isolate and fix instead of point fingers. The one thing I would recommend to all DBA's where virtualization is inevitable is to get extremely familiar with your server workloads and make the infrastructure guys your best friends.
June 16, 2011 at 8:14 am
Foxxo,
I agree it can seem like a loss for the DBA but don't underestimate the provisioning aspect. Think about it - you no longer have to worry about initial sizing or IO ramp-ups due to seasonal, or annual changes in data access. Customers are happy because they don't have to wait 3 months to get an order in for a physcial server. Drives, vCPU, memory are added on the fly. Windows clusters are a thing of the past. I see test and dev snapshot cloning in the future which will make those pesky restore\refreshes obsolete.
Yes, there are challenges but I don't think they're showstoppers.
June 16, 2011 at 8:53 am
I completely agree that VM should be seriously considered for development and QA environments.
In production environments one of the constant challenges I've faced is server sprawl. The astronomical costs of licensing SQL Server will eat your lunch (and dinner too). In my experience virtualization makes the problem worse. It becomes too easy to stand up SQL Server instances, and the licenses you do have are not utilized.
Virtualization helps increase hardware utilization, but I'd be willing to bet that in most companies licensing costs dwarf hardware costs, particularly where SQL Server is concerned.
Virtualization hurts you in the pocketbook if you are trying to make the most of your SQL Server licenses (and you should be).
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 16, 2011 at 9:08 am
The key to a successful VM implementation is having people skilled in VM Administration. You can't just hand a VM manual to your NT Administrator (even the worlds greatest NT Administrator) and expect to have a smooth transition. The learning curve is simply too steep. At my company and at other companies in the region the initial failure rate was about 20%.
A lot of of companies try to overcome this by trying to hire experienced VW Administrators. This can also be problematic. How can you tell if the guy your hiring actually knows VM if you don't know VM? Our NT Admin Manager made a bad choice and was eventually pushed out of his job because our VM servers had too many failures. That was unfortunate, because aside from the VM issues, he was a good manager. The guy he hired got to keep his job, but was sidelined someplace where he couldn't do too much damage.
Our SQL Server group had to ban SQL Servers on VM because we initially had so many problems. This lasted for about a year. Eventually the company hired a consultant from VM Ware to help us with our implementation. He knew what he was doing, and more importantly transferred that knowledge to our internal staff.
We now have almost 3000 SQL Servers running on VM Ware across the country and our SQL Server team can wait to get rid of the few remaining physical servers. 😀 The key is being able to manage the learning curve.
June 16, 2011 at 9:44 am
The article fails to mention the importance of LUN usage. By default your VM might be set up on a single LUN, but you need several to accommodate the OS, tempdb, primary and log backup drives. This spreads the load over multiple channels and disk controllers, giving optimal disk i/o. This was a tricky thing to track down during a recent upgrade to SQL 2008 and migration to a new datacenter (which had better/faster disks, but we weren't seeing it until we spread out our disks across multiple luns). We've been running SQL server in virtualized environment for 3+ years now and this was the only real issue.
June 16, 2011 at 9:48 am
Thanks for the article. We have a very robust physical VM environment with about 300 VM servers. Oracle has been very negative towards the whole VM environment and let's add their new licensing structure for large enterprises .... well the powers that be think SQL for any new applications is de-facto. I do agree tho it's more work for me. Any DBA's need a job?
As the DBA I was part of the VM team and was trained on the whole VM enviroment implementation (used to be a SA in a former job) so I am able to manage and monitor via the VM management tools all of my SQL servers, not just the databases.
What I LOVE about VM is:
Failover. We dropped a very large Oracle system VM node with lot's of users as a test and it was completely seemless in failover to another VM Node.
Cloning: Need a test box to try something stupid... piece of cake and no $ involved other than your time.
Problem Solving: We've had some systems that have issues so we take a snapshot, bring it up as a another system and are able to work on production stuff problems without touching the "real" production system.
Licensing: We have SQL Enterpise licensed our physical VM node sockets and this has allowed us to bring up as many SQL servers as the licensed nodes can handle. We have many very small systems that require SQL Server and this has saved us thousands and thousands on licensing costs. If your looking into VM careful on the licensing Enterprise R2 have very different licensing than Enterprise RTM.
Performance: Ah the bane of all systems. What we've been ablet o show is that by upgrading to new OS's and versions of software (and SQL), as well as making things 64-bit. The show and tell works 1000% better than tell by itself.
Snapshots: Bring up a system --- especially one that took the vendor 5 working days to completely install their stuff, take a snapshot and then we start doing are customizations and ---blat!!! things don't work. Roll back to snapshot and try again....and again... and again. But SO much better than starting from scratch.
Anyway I think virtualization is the best of the best and I've been doing this for 20+ years
Thanks
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply