5

I am about to build out a new SQL Server and I was planning to make extensive use of file groups. I expect heavy growth, and heavy read/write to 5 different databases on this server. I was planning on creating 2 additional file groups (one for user data and one for indexes) on each database for a total of 3 file groups per database. I was planning on splitting up the file groups among different drives/spindles. This server is a virtual server (VMWare) on an EMC SAN. I am new to SAN architecture and am I not the SAN administrator. I read a brief blurb on file groups and SANs in the book "Microsoft SQL Server 2012 Unleashed" that file groups were likely irrelevant when using SANs. Unfortunately, there wasn't much more detail than that, and I haven't found much else out there on the topic.

Is there a point to using file groups when using a SAN for storage?

If not, then why not? If so, then why?

What questions might I ask my SAN Admin on this topic?

Improve this question
4
  • Can you post the link to the blurb that said filegroups on SANs were irrelevant please?
    – dbafromthecold
    Commented Sep 22, 2014 at 21:26
  • Using SANs and RAID arrays for the database disk subsystem diminishes the need for filegroups. SAN and RAID systems typically have many disks mapped to a single data drive. This inherently allows for concurrent disk access without requiring the creation of a filegroup with multiple data files.
    – DMill
    Commented Sep 22, 2014 at 21:36
  • 1
    First question I would ask your SAN admin is:- Would IO be negatively affected by having one large file striped across the disks (in whatever RAID configuration set) or would IO be improved by striping multiple smaller files?
    – dbafromthecold
    Commented Sep 22, 2014 at 21:51
  • 2
    But don't forget the other benefits of filegroups. For example - piecemeal backups/restores.
    – dbafromthecold
    Commented Sep 22, 2014 at 21:53

1 Answer 1

Reset to default
2

I am not a SAN admin, but I have a very nice one in my team that takes the time to do his job proprely, and share his knowledge with us when we ask.

The way our SAN is configured makes it so there is a very large amount of cache between the SQL servers and the physical disks. In this situation, having multiple filegroups, or even splitting the DATA and LOG files on different drive letters is a moot point, as it all ends up in the same cache cluster and points to the same large array of 15000RPM disks. The cache is intelligent enough to keep the relevant stuff cached, and write to disk in a "Lazy Writer" method.

That said, althou we handle a very large amount of data, we are not overly heavy in throuput. This setup is lightning fast for our needs, and very safe between the usual backups and SAN automatic redundancy to another server room.

Sit down with your admin, tell him what your needs are, and balance the final setup with his knowledge and architecture. SAN architecture can vary greatly, so some of the "best practices" of SQL server installation may not even apply, like it did for us.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.