.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Creating Filegroups in SQL Server

Posted By:Dhiraj Ranka       Posted Date: July 21, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

In 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

USE EmployeeDB;
GO
ALTER DATABASE EmployeeDB
ADD FILEGROUP FG_Emp_ReadOnly
GO
Adding files to filegroup
ALTER DATABASE EmployeeDB
ADD FILE
(
NAME = FG_READONLY1,
FILENAME = 'C:\EmpDB_RO.ndf',
SIZE = 10MB,
MAXSIZE = 150MB,
FILEGROWTH = 10MB
) TO FILEGROUP FG_EMP_READONLY;
GO
Creating Objects in new filegroup
-- New Table
CREATE TABLE dbo.EmpDetail
(
EmpID int NOT NULL,
ProductID int NOT NULL,
CustomerID int NOT NULL,
Age int NOT NULL
)
ON FG_EMP_READONLY -- Index
CREATE INDEX IDX_EmpID ON dbo.EmpDetail(EmpID) ON FG_EMP_READONLY
GO
Moving an object from primary file group to another file group In order to move an existing table with a clustered index, issue the following command:
-- Table - The base table is stored with the
-- clustered index, so moving the clustered
-- index moves the base table
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.EmpDetail(ProductID)
ON FG_Emp_ReadOnly
GO
And in order to move a non-clustered index, issue the following command:
-- Non-clustered index
CREATE INDEX IDX_EmpID ON dbo.EmpDetail(EmpID)
WITH (DROP_EXISTING = ON)
ON FG_Emp_ReadOnly
GO
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:
-- Table without a clustered index + drop index
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.EmpDetail(ProductID)
ON FG_Emp_ReadOnly
GO
DROP INDEX IDX_ProductID ON dbo.EmpDetail(ProductID)
GO
Determining the objects which exist in a particular filegroup
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = 2 --* New FileGroup *
GO
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:
  • Option 1
    • Data filegroup
    • Index filegroup
  • Option 2
    • Read only tables filegroup
    • Read-write tables filegroup
    • Index filegroup
  • Option 3
    • Read only tables filegroup
    • Read-write tables filegroup
    • Index filegroug
    • Key table 1 filegroup
    • Key table 2 filegroup
    • Key table 3 filegroup

References


Files and Filegroups Architecture
http://msdn.microsoft.com/en-us/library/ms179316.aspx  

SQL Server Best Practices: Setting a Default Filegroup

http://blogs.msdn.com/b/buckwoody/archive/2009/05/21/sql-server-best-practices-setting-a-default-filegroup.aspx
 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend