Solved

Sql Server 2012 RefS or NTFS for database drives

Posted on 2014-09-09
4
789 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 118
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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.
In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
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 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…

867 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

17 Experts available now in Live!

Get 1:1 Help Now