SQL Server on a virtual server

  • That is true, but it's never been my decision.

    Which Virtual platform to use, i.e. Microsoft or VMWare, supported or not, has always been the pervue of the Windows Administration team.

    Even though they know it isn't fully supported by Microsoft management has always decided to take that risk. I neither condone or advise against it, since it's not my call.

    I imagine that many other DBA's find themselves in a similar situation.

  • Hmmmmm......I am going to have to respectfully disagree with Doc-s. We started down the VMware

    path 15 months ago and have not looked back. Going VM allowed us to consolidate the rack space for approx 35-40 hardware servers into about 22U in one rack.

    We run 4 ESX servers with full redundancy ( if my terminology is bad, is because I'm the DBA, not the VM guy...), fail over, auto-switching to a new ESX box, etc. All the advantages of VM in an environment not from MS. We now have at least 40 servers on the VM environment and as hardware servers get to EOL warranty-wise, the databases are being migrated to VM.

    Supported or not, VMware has served us well, especially in a production environment. The savings in hardware costs and environmental expense is not my responsibility, but just getting rid of 40 4U/6U servers has to be a savings of some magnitude. Talk about ease of maintaining the databases. In this day of personnel and budget cutbacks, VM can not be beat. We have even managed to free up rack space for the GeoData group to move back into the main computer room, which will allow that department to vacate an expensive rental site - more cost savings. Can't be beat.

  • I have to disagree with Joe. While I am not the biggest supporter of VMs on SQL (especially the potential nightmare live migrations can have on a very active server), Microsoft does in fact support VMWare on certain setups. VMWare is a partner in their Server Virtualization Validation Program (SVVP) which Microsoft supports officially under approved setups.

    Microsoft server software and supported virtualization environments[/url]

    Welcome to the Windows Server Virtualization Validation Program

    Joie Andrew
    "Since 1982"

  • Thanks for the link, Joie. I think that's a relatively recent change on MS's part to support VMWare, and I'm glad to see it.

  • I have a VMWare SQL 2000 Standard SP4 on Windows 2003 Server SP2 test box and I am looking for ways to speed it up. It has 4 cpu's and 4GB RAM. Granted it's only test however I have recently moved a 480GB database on it and I am performing some intensive queries and need to check the index fragmentation and possibly rebuild them, plus backup and restore VLDB's.

    What steps can I take to speed this instance up - or is it just the nature of the beast ie - VMWare?

    would 'Boost SQL priorty on Windows' help any?

    Many thanks

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • Did you run column statistics? Also, don't forget to check the index, table, and file fragmentation. Diskkeeper or another third party tool is good to defragment database files on disk. Often, you can defragment with the database open and online. Don't forget to fine-grain stripe tempdb. Sometimes, you can mitigate poor performance by larger amounts of RAM, using stored procs, and having a faster disk subsystem. Look at the NIC cards. Be sure to have dual pathing in place and use fiber not iSCSI. Check the RAM cache on the controller card.

    Doc

  • How many VMs are running along with the SQL VM on the same physical box, and what kind of servers are they?

    Joie Andrew
    "Since 1982"

  • There are quite a few other (non work intensive I've been told) Virtual servers (also they aren't using VMWare but Virtual Iron) on that server also. Found out last night that the drive where my VLDB resides is massively fragmented, ran the defrag, nothing changed.

    I know the database indexes are fragmented but it seems like the chicken and the egg, need to rebuild indexes to speed up performance but even trying to do this is taking too long.

    Back to the drawing board. :ermm:

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • Well I would definitely say that your indexes should be rebuilt if any of them have large fragmentation. Does the server have any maintenance periods, non-business hours or known periods of low activity? I would think that those time slots would be prime candidates for getting some of your index maintenance tasks done. Since you have stated that you are supporitng a VLDB, I would look to work on the most fragmented indexes first. You may not be able to rebuild/reorg them all within a single period, but given enough regular maintenance periods you should be able to get through them all.

    How often are you performing full backups? Can some of that time be alloted for rebuilding indexes?

    Joie Andrew
    "Since 1982"

  • Hi,

    thanks for all your replies, some really good ideas here. I think the main issue with the dog slowness of the box is because of the Virtual server AND a heavily fragmented database AND this db has many tables with over 100,000,000 rows in them. Trying to rebuild indexes was taking forever!

    Edit: forgot to add, the drives where the datbase files live was massively fragmented also, however because the files were large, a defrag of the drive didn't help much.

    In the end we have decided to move a better hyper-v server with SQL 2005 Developer (this is only a test box) x64 and use partitioning.

    However if I had to carry on using this box I would have started on the index fragmentation probaby one table at a time, as I can estimate that some clustered index rebuilds would take an entire weekend. :w00t:

    thanks again

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • If you are moving machines make sure to also plan out carefully where the database files will be. Separation between the OS, data and log files as well as tempdb can be very important.

    Joie Andrew
    "Since 1982"

  • Rackspace has moved their headquarters from a building once occupied by Datapoint Corporation to the unoccupied Windsor Park Mall in Windcrest, Texas. Rackspace's Chairman, Graham Weston, owned the Montgomery Ward building in the mall until 2006, when it was sold to a developer.

    Managed Hosting[/URL]

  • Personally, I prefer not to run critical apps on virtuals. Past experience, shared resources, etc. I also prefer SQL Server instances to running a mass of virtual SQL servers.

    One thing to keep in with VMWare and virtual SQL servers, especially the PROD ones, is licensing. My previous place got a rather hefty and unexpected true-up bill from Microsoft. We got heavily billed for Enterprise Edition. And heres why... basically we had Enterprise Edition installed on one of our reporting servers (load balanced front-end, single DB) and basically got billed for an enterprise license for every CPU on the virtual host. WTF?!?!? Got a very basic explanation and it was along the lines of the virtual host does the sharing of resources...

    Looking for more info now.

    I may be wrong...

  • Seems to me if you only have one instance of EE on your virtual host, you would only need to license the virtual procs assigned to that SQL instance.

    KU

  • We are considering sql server virtualization and the major question I have is resource contention. If I have four virtual servers running then the all have to share resources and I would think that contention could be an issue. i'm thinking we should just go with multiple instances rather than having virtual servers running sql server.

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply