7

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?

6

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

0

We had issues with restoring log backups for a logshipping scenario. Over time the restores were getting slower and slower, up to 15 seconds for a small file. After the SQL Instance was restarted the restore was going at 1 file per second (even less than a second actually) and then gradually slowing down. We kept restarting the SQL instance and every time the restores were fast after the restart and then slowing down. We rebuild the volumes using NTFS. Log backup restore is running fast and doesn't seem to go slower as it was when ReFS was used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.