none
SQL Server in Hyper-V RRS feed

  • Question

  • Hello,

    I'm thinking of moving my SQL databases in VMs to help with the constant problem of disk space.

    Could you please point me to some useful documents?

    Any tips on improving hard disk performance for Sql Server?

    Thank you.

    Monday, January 14, 2019 12:42 PM

Answers

All replies

  • Monday, January 14, 2019 12:48 PM
  • Hi Bogdan G. Suciu,

    Actually, moving SQL Server database to SQL Server on Hyper-v is just a migration of database, you can do a simple backup and restore to migrate the database. But I think how to configure the VM is more important, I would suggest you referring to this blog: https://www.varonis.com/blog/sql-server-best-practices-in-virtualized-environments/.

    >>Any tips on improving hard disk performance for Sql Server?

    Could you please share more detailed information? You want to improve the query I/O performance or just the hard disk speed?

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 15, 2019 2:47 AM
    Moderator

  • Any tips on improving hard disk performance for Sql Server?

    Thank you.

    SQL Server has no control over hard disk. You need to buy good SSD(Fast Drives) from reputed storage vendor to get maximum performance.

    Put data, log file, backup and tempdb on different drives to avoid I/O contention. Are you having a disk issue ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, January 15, 2019 6:50 AM
    Moderator
  • Hello,

    First of all thank you for all the responses.

    To better understand my concerns i will detail a bit on my setup.

    Currently we have as follows:

    1. Raid 10 formed of 4 Dell SAS 12 Gbps HDD

    - on this disk we have stored the database

    2. Raid 1 formed of 2 Dell SAS 12Gbps HDD

    - on this disk we have stored the log ( system databases logs and the user database logs)

    Here are the read/write tests done with CrystalDiskMark:

    1.

    2.

    Currently on heavy load the disk queue length gets high (1000+) and the response time as well. No more modifications can be done to the disks since i have no more available bays.

    Backups are done on a separate disk.

    Since we do not have any more spots for bays i'm thinking of buying a new server with more bays and to virtualize the DB Server to make it possible to move in case i run into similar disk problems.

    So my concern is what setup should i have for my raids? SAS or SATA SSD? Raid 10 or Raid 0? Logs on Raid 0 and DB on Raid 10? Is it even worth virtualizing or just use a physical machine? Does the allocation unit matter and which is the best for log and for data? 

    Tuesday, January 15, 2019 8:22 AM
  • As a matter of first importance thank you for every one of the reactions. 
    To more readily comprehend my worries I will detail a bit on my setup. 
    Presently we have as pursues: 

    1. Assault 10 framed of 4 Dell SAS 12 Gbps HDD 
    - on this plate we have put away the database 

    2. Assault 1 framed of 2 Dell SAS 12Gbps HDD 
    - on this plate we have put away the log ( framework databases logs and the client database logs) 

    Here are the perused/compose tests finished with CrystalDiskMark: 
    Presently on overwhelming burden the plate line length gets high (1000+) and the reaction time also. No more alterations should be possible to the plates since I have not any more accessible straights. 

    Reinforcements are done on a different plate. 

    Since we don't have any more spots for sounds i'm considering purchasing another server with more straights and to virtualize the DB Server to make it conceivable to move on the off chance that I keep running into comparable plate issues. 

    So my worry is what setup would it be a good idea for me to have for my attacks? SAS or SATA SSD? Strike 10 or Assault 0? Logs on Strike 0 and DB on Attack 10? Is it even worth virtualizing or simply utilize a physical machine? Does the allotment unit matter and which is the best for log and for information?

    Tuesday, January 15, 2019 8:41 AM
  • Bogdan and DBA Programming, is this still an issue for you?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 8, 2019 9:20 PM
    Owner