Creating Filegroups in SQL Server

Posted By:Dhiraj RankaPosted Date:July 21, 2011Points:200
Category: DataBaseURL:http://www.dotnetspark.comIn this article we will see how to create Filegroups in SQL Server. For allocation and database administration purpose database objects and file are group together in filegroups

Background

Daily data transaction will eventually anticipate growth of the database and there will be a need to scale to support the large amount of data. When we start we usually start with a very small database, but if we have a look at our capacity plan then we understand the growth which can vary from 2-6 times the current database in next 2 years. In order to solve this problem we have to take certain steps from the planning perspective.

Workaround

There are lot of known features available to support the database growth in SQL Server 2005, 2008. Focusing issues from capacitive planning, then we have one main thing to deal with and i.e "filegroups". For allocation and database administration purpose database objects and file are group together in filegroups. As a rule each database has a primary filegroup to support the system and user objects. In order to maintain the performance and needed growth, additional allocation of hard drives can be done to the server. Using new disk drives we can create other filegroups and files. So that we spread the IO operations to these new drives by moving objects to these newly created filegroups. Creating a filegroup

It might be possible that there is table does not contain any clustered index and we still want to move, then we can create the clustered index on the table by specifying the new file group. After doing all this, it will move the base table and clustered index to the new file group. Finally we can drop the clustered index. We can use the following commands:

Possible filegroup configurations As we say that sky is a limit and our application (nothing but software) and hardware specifications drives our most decisions, while designing a filegroup we can consider following options: