locked
resilient file system on SQL 2014 RRS feed

  • Question

  •  I know that you were not able able to run SQL 2012 with ReFS.   It seems that this has been fix with SQL 2014.   Is it still recommend to use NTFS or can the SQL Engine & databases now reside on a ReFS Volume?  Is there a major difference in IOPS numbers when used by SQL ?
    Wednesday, April 16, 2014 7:15 PM

Answers

  • Hello,


    I don’t know an official statement about ReFS support on SQL Server 2014.


    The following article states that is not supported on SQL Server 2012 and earlier versions only.

    http://support.microsoft.com/kb/2681562/en-us  

    An update of this article was just requested today, but it may take some time until CSS can publish the updated article.


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com




    Wednesday, April 16, 2014 7:38 PM
  • It is fully supported to deploy SQL Server data and log files on ReFS filesystems for SQL 2014.

    The one thing to note is that we specifically disable the use of the Integrity Streams feature in ReFS for data and log files.  The current implementation of Integrity Streams has significant performance issues with SQL's particular IO patterns.

    In our testing, there was a slight performance improvement when using ReFS without Integrity Streams, but this is mostly in the areas of filesystem metadata (creating and deleting files, which is a somewhat rare event in normal operations).


    Microsoft SQL Server Storage Engine PM

    Monday, April 28, 2014 4:45 PM
  • The only limitation that I'm aware of in the DB engine is that database and log files will not use the Integrity Streams features due to the performance issues referenced above.  You can create database and/or log files on a volume that has Integrity Streams turned on by default.  SQL will just override the default and create the files without Integrity Streams enabled.

    I'll contact Alan to see what he had in mind with his comment about limited support.

    To the best of my knowledge, the comment about AS is correct, but I will verify.


    Microsoft SQL Server Storage Engine PM

    Thursday, May 1, 2014 6:10 PM

All replies

  • Hello,


    I don’t know an official statement about ReFS support on SQL Server 2014.


    The following article states that is not supported on SQL Server 2012 and earlier versions only.

    http://support.microsoft.com/kb/2681562/en-us  

    An update of this article was just requested today, but it may take some time until CSS can publish the updated article.


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com




    Wednesday, April 16, 2014 7:38 PM
  • It is fully supported to deploy SQL Server data and log files on ReFS filesystems for SQL 2014.

    The one thing to note is that we specifically disable the use of the Integrity Streams feature in ReFS for data and log files.  The current implementation of Integrity Streams has significant performance issues with SQL's particular IO patterns.

    In our testing, there was a slight performance improvement when using ReFS without Integrity Streams, but this is mostly in the areas of filesystem metadata (creating and deleting files, which is a somewhat rare event in normal operations).


    Microsoft SQL Server Storage Engine PM

    Monday, April 28, 2014 4:45 PM
  • Hi Kevin!

    As I understand it ReFS is still not supported by SSAS instances and according to Allan Hirt's recent Clustering SSAS white paper he states that the DB Engine in SQL 2014 still has limited support. (I'm guessing perhaps IFI is one area)

    Could confirm (or reject) these statements please since I haven't got around to validating.


    Regards,
    Mark Broadbent
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Want professional SQL Server High Availability Training? Click here

    Monday, April 28, 2014 5:31 PM
  • The only limitation that I'm aware of in the DB engine is that database and log files will not use the Integrity Streams features due to the performance issues referenced above.  You can create database and/or log files on a volume that has Integrity Streams turned on by default.  SQL will just override the default and create the files without Integrity Streams enabled.

    I'll contact Alan to see what he had in mind with his comment about limited support.

    To the best of my knowledge, the comment about AS is correct, but I will verify.


    Microsoft SQL Server Storage Engine PM

    Thursday, May 1, 2014 6:10 PM
  • Thanks for coming back to me Kevin, if you don't get the chance I can always ping him an email myself.

    Either way, thanks again!


    Regards,
    Mark Broadbent
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Want professional SQL Server High Availability Training? Click here

    Thursday, May 1, 2014 9:14 PM
  • You may be able to store databases or log files on a ReFS volumne, but the SQL 2014 installer will not allow you to create a SQL instance on an Refs volume.  The installer just hangs....

    JC


    John Carpenter Technical Services Consultant SVA Consulting LLC

    Tuesday, June 9, 2015 1:38 PM