none
SQL Server Setup on Hyper-V - Disk Config RRS feed

  • Question

  • Hi All,

    I am about to start setting up an SQL server on Hyper-V for an ArcGIS Server. I have done a fair bit of reading on the subject of drive configuration and generally, people seem to agree that OS, Data, Logs and TempDB should all be on different drives as they have different performance requirements.

    The hardware we have for the new server has 4 x 2 TB SAS SSDs in RAID 10, for VM storage and a 240GB RAID 1 for Hyper-V. The 4 disk array is currently configured as one big drive in Hyper-V.

    Now, I can setup the SQL server to have separate virtual disks for OS, Data, Logs and TempDB, however, this seems to not really achieve much as all the virtual disks will be stored on the same SSD array.

    I have seen some info about configuring SQL in Azure which seems to indicate that it is OK to put the Data and Logs on a Premium SSDs and TempDB on another (non-persistent) drive. I have also seen some people putting forward if you are on an SSD array the disk configuration becomes a lot less important.

    If the case is that on this HW, I don't need to create a separate virtual disk for each of Data, Logs and TempDB, then I would prefer to simply have 2 drives - OS (including SQL server app) and Data (for Data, Logs and TempDB). 

    I'd love to hear any suggestions on what might be the best setup with the HW I have in your opinion. 

    Thanks 

    Regards

    Mike

    Tuesday, January 14, 2020 5:49 AM

All replies

  • Hello Mike,

    As we know, an important reason for making optimized drive configuration is to ensure the performance and high fault tolerance, right ? So it’s better to store database files in different location on the disk to reduce I/O competition.

    To configure data and log files for best performance, follow these best practices:

    1. Do not put Data files on the same drive that contains the OS files.
    2. Put transaction Log files on a drive separate from Data files.
    3. Put the TempDB database on a separate drive, if possible

    According to your Hyper-V hardware condition: 240GB RAID 1 for Hyper-V. The 4 disk array is currently configured as one big drive, I recommend you put OS, DATA, LOGs, TempDB in different disk separately. If possible, extend disk to 8 for default tolerance and redundant.

    And you can refer to these articles which I think give the good suggestion of disk configuration of SQL : https://docs.microsoft.com/en-us/archive/blogs/sql_server_isv/sql-server-drive-configurations

    https://desktop.arcgis.com/en/arcmap/10.6/extensions/data-reviewer-guide/admin-dr-sql-server/geodatabase-configuration-keywords-and-disk-configuration-for-the-reviewer-workspace-in-sql-server.htm

     

    Hope it will help. Any farther question, please let’s me know.


    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.


    Wednesday, January 15, 2020 2:59 AM
  • Hello,
    Have you solved the question?
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    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.

    Monday, January 20, 2020 9:06 AM