Archive for April 12th, 2011

Files and FileGroups with SQL Server April 12th, 2011

Vinod Kumar

At MTC, in multiple customer engagements I have seen questions from DBAs getting confused with the Physical and logical structure of SQL Server databases. Rather than repeating what the BOL documents: “Files and Filegroups Architecture with SQL Server”, I thought will call out some of the lesser known facts of using files and filegroups with SQL Server in this post. I know some of these can be basic but still relevant for a good read for those who have not thought of it.

Generic file Usage:

  1. Files cannot be shared between databases.
    This is totally different from how you might share files in a clustering environment via SAN help. But inside a single database instance, the data files cannot be shared.
    The other lesser known feature is Shared Scalable Database (SSD) – is also a cool technology. More on the LINK: “setup is well documented” at the support site.
  2. Files can be created only on local drives, unless a special trace flag is used. For more information about the supported uses of this trace flag, see the Knowledge Base article, LINK: "Description of support for network database files in SQL Server".
  3. The drive should not be a compressed drive.
  4. The file system on the disk drive can be NTFS or FAT. But highly recommend using NTFS as there are features built specifically maximizing being on NTFS. Like SQL Server – LINK: Instant file initialization.
  5. Log files cannot be assigned to filegroups.

Best Practices: The data files and log files should be on separate drives so that a crash on one drive does not cause the loss of both data and log files.

Quick Quiz: Do you need the primary data file available to backup your transaction log after a crash? 

Do write your answer on your blog (link this post) or via twitter send me a note (@vinodk_sql) with proper documented links to support your answer.

Filegroups and beyond:

In a nutshell, filegroups are a named collection of one or more physical files that form a logical unit, filegroups allow files to be grouped together for administrative and data allocation/placement purposes.

  1. When a database is created, the primary filegroup is created to hold the primary data file (.mdf).
  2. Primary filegroup serves as the default filegroup for objects added to the database without having an alternate filegroup specified for them.
    Note: Assigning objects on a specific filegroup is simple – just use the ON <<filegroup>> option with the Tables, indexes etc objects creation / alteration command.
  3. All system tables are allocated in the primary filegroup.
  4. Filegroups per database is 32767. LINK: Source.
    Anything apart from the Primary FG are the User-defined filegroups – these are any filegroups that are specifically created by the user when first creating or later altering the database.
  5. A file can belong to one and only one filegroup.
  6. In general, a filegroup can contain many objects, objects reside on one and only filegroup. Except for the case of Partitioning with SQL Server. Link: Partitioning tables with SQL Server.

There are lots more interesting information DMVs can expose from SQL Server. I will reserve the same for a different day. Hope you have a great day and pass your comments.

Continue reading...