I rarely see this one implemented correctly in the field, and if things go wrong it will cripple you.
After creating a database, change the default filegroup from primary to anything else. If you only have one filegroup, create a secondary one. Then alter that filegroup to become the default.
My normal procedure is to create the database, specifying a secondary file group in the Filegroups page in the New Database screen in SSMS. After that, I create a file in the new Filegroup and make that new Filegroup the default.
--alter database, add file to Filegroup
ALTER DATABASE [DBNAME]
ADD FILE (NAME=[NewFileName], FILENAME='D:\Data\[NewFileName].ndf')
TO FILEGROUP [FileGroupName] --(the name used when creating the Filegroup
ALTER DATABASE [DBNAME]
MODIFY FILEGROUP [FileGroupName] DEFAULT
Reasoning: When the database is created the primary filegroup is by default marked as the default filegroup in which all new objects are stored. The primary filegroup also happens to be where all the system objects are held.
So once you create a database and make any other filegroup the primary, all the system objects (system tables for the database) are in their own filegroup with I/O isolation. Seeing as these system objects do not change as often as all the other database objects, this minimizes write activity to the primary data file and will assist with reducing corruption due to hardware failures.
Another benefit this will give you is to be able to do maintenance at the filegroup level. More to come about the benefits of filegroups and files in a future post.
