SQL Server Data File Storage

585 pts.
Tags:
SAN
SQL 2008
SQL Server
SQL Server 2008
SQL Server database
We have a SQL Server with 32GB of RAM, 8 CPU 16 Cores, using SAN.

On our production database (1 database only) we have 3 file groups Primary, Core, Index with 1 file in the PRIMARY Group, 16 Files in the Core Group, and 16 Files in the Index Group. Each file group including all data files belonging to the file group is in its own drive on the SAN.

Is that a correct way to setup your database files and file groups?

What is best practice?



Software/Hardware used:
sql server 2008, SAN

Answer Wiki

Thanks. We'll let you know when a new response is added.

Based on your config, and the comments about your storage system you probably only need one or two physical files per file group. The smaller the files in each file group the quicker it would be to do a partial database restore allowing you to keep the system online and have less data missing (or just to keep the system down for less time if you don’t have Enterprise Edition) in the event of a corrupt database file. But other than that I don’t see the need for so many files.

(Also see my comment on your other thread.)

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Hlx
    Need more information. Please add information about your log file(s) and RAID type on the SAN drives as well as the typical workload for each file group.
    690 pointsBadges:
    report
  • Rick Martinez
    Log file is in its own volume, our SAN is a RAID 10 shared everything. Workload is about 900 users.
    585 pointsBadges:
    report
  • Hlx
    If RAID 10 shared everything means that all of the drives in the array are in a single mirrored stripe set (Xiotech?), it is unlikely that any file, filegroup, or drive configuration will perform much differently than any other. You are likely giving yourself additional management headaches for little to no performance increase. If you have the capability to do load testing, try your setup performance vs. a two file (data, log) db on the same drive and I believe you will likely notice < 5% difference. Many of the "best practices" with regards to dataindexlog separation that you read really apply to I/O subsystems with multiple I/O channels, various spindle counts and RAID configurations. These in general won't apply to your system simply due to the fact that every I/O will use the same channel and spindles.
    690 pointsBadges:
    report
  • Rick Martinez
    Yes! Xiotech! how did you guess?
    585 pointsBadges:
    report
  • Denny Cherry
    When you see people talking about enough storage arrays in these forums you can start to see the trends as people talk about the configs and you can start to guess pretty accurately which vendors they purchased from.
    66,115 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following