Solved

Sql Server 2012 RefS or NTFS for database drives

Posted on 2014-09-09
4
908 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 121
ID: 40312494
I would opt for ReFS if you are using very large disks and lots of files, e.g, 2TB+
0
 
LVL 58

Assisted Solution

by:Cliff Galiher
Cliff Galiher earned 100 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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 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 35

Accepted Solution

by:
Seth Simmons earned 300 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ransomware is a malware that is again in the list of security  concerns. Not only for companies, but also for Government security and  even at personal use. IT departments should be aware and have the right  knowledge to how to fight it.
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

728 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