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:
- 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.
- 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".
- The drive should not be a compressed drive.
- 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.
- 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.
- When a database is created, the primary filegroup is created to hold the primary data file (.mdf).
- 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.
- All system tables are allocated in the primary filegroup.
- 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.
- A file can belong to one and only one filegroup.
- 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.
Share this article
This entry was posted on Tuesday, April 12th, 2011 at 11:55 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.