SQL Server 2014 – Should ReFS Be Used for New SQL Server?

configurationsql serversql server 2014storage

We are in the process of building a new production Windows Server 2012 R2 server to run SQL Server 2014 Enterprise Edition.

There is a bit of advice on the internet that suggests for Windows Server 2012 and SQL Server 2012 I should stick to NTFS (e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.

However, there is also advice that SQL Server 2014 now supports ReFS (e.g. Brent Ozar, msdn social)

Is there a recommendation from Microsoft or a general rule of thumb on when to use NTFS and if/when to use ReFS for data and log files in SQL 2014 and Server 2012 R2?

Best Answer

There is a bit of advice on the internet that suggests for Server 2012 and SQL 2012 I should stick to NTFS e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.

This is no longer true with SQL Server 2014 and is fully documented in KB 2974455

This problem is resolved in Microsoft SQL Server 2014. Beginning in this version of SQL Server, the DBCC CHECKDB command does not use named streams to create the internal snapshot database.

In fact, In-Memory OLTP (Hekaton) is supported as well (initially it had problems), but I would not recommend to put your mission critical database servers on ReFS (it's new and there might be bugs that people have not yet discovered as opposed to NTFS which is around since ages) - check

SQL Server AND REFS Part 1 and 2 by @swasheck

Adding for completeness :

Bob Dorr - Principal SQL Server Escalation Engineer recently published a blog post on this:

Sparse Files – Supported on both NTFS and REFS

Related Question