Re: SQL Server 2005 and file systems, any recommendations

Erland Sommarskog wrote:
bjorn.augestad@xxxxxxxxx (bjorn.augestad@xxxxxxxxx) writes:
We're planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.

Right now we're thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))

From what I can tell, NTFS is a journaling file system, at least it
does some journaling. I'd prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?

Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLastAccessUpdate.

How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?

No, FAT32 is not a very good alternative. There are several features in
SQL Server that are not available with FAT32. I got this list from one
of the SQL Server developers:

1. Database snapshots - require support for sparse files which is available
only under NTFS
2. DBCC snapshot usage - requires support for alternate streams which is
available only under NTFS
3. Mount points under cluster
4. Data file compression which uses NTFS compression
5. Encryption of database files is supported only under NTFS
6. Differential backup, restore of full-text catalog files is supported only
under NTFS

He says the list may not be complete. He also added "I believe that recovery
mechanisms are more robust under NTFS and protects against system failures.
I think the user is worrying too much about NTFS when there are other
obvious performance bottlenecks."

I get your point(s) :-) So FAT32 is out, not a big surprise.

Once upon a time I was playing around with file systems(JFS, XFS and
others), Oracle and AIX as well as OSF/1. I spent a considerable time
measuring performance for different configurations and journaling file
systems *with* the journal on the same disk as the db data(or translog)
was awfully slow compared to other configurations. I assumed that is
was due to disk head movements back and forth between the journal and
the file.

So even if the file system isn't the performance bottleneck right now,
I'd prefer to configure all the new disk drives optimally from day one.
I read the NTFS spec and browsed through all the options, but couldn't
find anything about tuning the journal. I'll have to reread it, I
guess...

Thanks,
Bjørn

.

Relevant Pages

  • Re: SQL Server 2005 and file systems, any recommendations
    ... systems *with* the journal on the same disk as the db data ... I read the NTFS spec and browsed through all the options, ... There is the option of raw partition. ... The best solution, IMHO, would be to have NTFS without journaling or NTFS with the journal on a separate disk. ...
    (comp.databases.ms-sqlserver)
  • Re: File Searching, how to speed it up?
    ... If you have more data than FAT32 can hold, use ntfs with a larger cluster size. ... If the average disk queue length goes over 2 it does terrible things ... On the other hand, if you have server ... drives you might get a doubling ...
    (microsoft.public.dotnet.framework.performance)
  • Re: SQL Server 2005 and file systems, any recommendations
    ... NTFS with the journal on a separate disk. ... journaling applies only to file allocation, ... My interpretation, hopefully wrong, is that NTFS journal entries will be written to disk whenever a file is modified. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Shared Storage
    ... The LUN masking/mapping allows both servers to access the same NTFS ... >if I create a file on>one server, it is not visible on the other. ... two separate computers cannot access the same disk at the same ... clustering but then again I really do not understand your solution. ...
    (microsoft.public.windows.server.general)
  • Re: FAT32 or NTSF
    ... There are certainly good reasons for using NTFS over FAT-32. ... there is no journaling on ... >changes can be committed to disk. ...
    (microsoft.public.windowsxp.general)
Loading