?
Solved

Sql Server 2012 RefS or NTFS for database drives

Posted on 2014-09-09
4
Medium Priority
?
946 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 123
ID: 40312494
I would opt for ReFS if you are using very large disks and lots of files, e.g, 2TB+
0
 
LVL 59

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 69

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 35

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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

777 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