Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql Server 2012 RefS or NTFS for database drives

Posted on 2014-09-09
4
Medium Priority
?
1,061 Views
Last Modified: 2014-09-09
We are building a new sql server 2012 on a Windows 2012 R2 Datacenter which is a vmware 5.5 vitural machine. We have separate raw device mappings for the data,log,system databases and temp files. The first question I had was is it better to format the OS and the other drives as Refs? And for the Allocation unit size what would you recommend?
Thanks
Example of current setup
OS = NTFS
Sql Data Folder = NTFS
Sql LogFiles Folder = NTFS
Sql System Database = NTFS
Sql TempFiles = NTFS
They all have the Default Allocation unit size.
0
Comment
Question by:CityInfoSys
4 Comments
 
LVL 125
ID: 40312494
I would opt for ReFS if you are using very large disks and lots of files, e.g, 2TB+
0
 
LVL 60

Assisted Solution

by:Cliff Galiher
Cliff Galiher earned 400 total points
ID: 40312509
This depends on the version of SQL. For now, most editions of SQL *require* NTFS because of how they use file handles. ReFS is very good for fileserver and hyper-V servers, but for now most LOB applications will still need NTFS.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 40312618
SQL data files usually work best with 64K allocation unit size.

SQL log files usually work well with 16K to 32K, although smaller or large sizes are acceptable.

Windows files -- and SQL system/binary files, if they are actually on a separate drive -- usually work best with a 4K to 8K allocation size.
0
 
LVL 36

Accepted Solution

by:
Seth Simmons earned 1200 total points
ID: 40313012
Recommended to keep NTFS since it has features that SQL uses that are not part of ReFS.

SQL Server and Windows Server 2012's ReFS File System
http://sqlmag.com/blog/sql-server-and-windows-server-2012s-refs-file-system

Using ReFS with SQL Server
http://windowsitpro.com/sql-server-2012/using-refs-sql-server

Also, ReFS only supports 64k allocation unit size.
0

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question