Solved

Sql Server 2012 RefS or NTFS for database drives

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

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:ScottPletcher
ScottPletcher 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 34

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

HOW TO: Install and Configure VMware vSphere Hypervisor 6.5 (ESXi 6.5), Step by Step Tutorial with screenshots. From Download, Checking Media, to Completed Installation.
HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now