MS SQL Server 2012 - DBA Articles

CREATE DATABASE - Secondary Data Files with User-defined Filegroup

This article describes how to create a database with four secondary data files along with one user-defined filegroup in SQL Server 2012 by using SQL Server Management Studio. By default, a user-defined Filegroup can be created to group secondary data files together for administrative, data allocation, and placement purposes.
For example, Sales_DW is a simple Sales data warehouse database that estimation data volume is 100 GB. This database is defragmented into four different 25 GB secondary data files that located in four different disks. Sales_North.ndf, Sales_East.ndf, Sales_West.ndf and Sales_South.ndf, can be created on four disk drives, respectively, and assigned to the filegroup Sales_Data_Mart.
A table can then be created specifically on the filegroup Sales_Data_Mart. Queries for data from the table will be spread across the four disks; this will improve performance. The same performance improvement can be accomplished by using a single file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups let you easily add new files to new disks.
Select SQL Server Name and then click Connect.

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Right-click Databases, and then click New Database.

In New Database, enter a database name = Sales_DW.

Select Filegroups in the left pane and then click Add.

Type User-defined Filegroup Name = Sales_Data_Mart.

Press Enter Key.

Select General in the left pane.

* * * * *

Your Comment To AUTHOR