Feeds:
Posts
Comments

Posts Tagged ‘multiple files in a single filestream filegroup’

Adding multiple files in a single filestream filegroup was a very nice & helpful feature shipped with SQL Server 2012. Now, you can easily organize your data under multiple files in the same filestream filegroup. Also, it is not necessary that you place all of them in the same location. You can place them in multiple locations to divide the load as well.

Let me create a sample database to demonstrate. Given below is the script.
Note : Before executing the below script, kindly create a Data folder in C: drive.

USE [master]
GO
CREATE DATABASE [Sample_DB]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'Sample_DB',
FILENAME = N'C:\Data\Sample_DB.mdf',
SIZE = 500MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(
NAME = N'Filestream1',
FILENAME = N'C:\Data\FileStream1',
MAXSIZE = UNLIMITED
)
LOG ON
(NAME = N'Sample_DB_log'
,FILENAME = N'C:\Data\Sample_DB_log.ldf',
SIZE = 500MB,
MAXSIZE = 1GB,
FILEGROWTH = 10MB
)
GO

Let me add an additional file to the same filegroup. Given below is the script.

ALTER DATABASE [Sample_DB]
ADD FILE
(
NAME = N'Filestream2',
FILENAME = N'C:\Data\Filestream2',
MAXSIZE = 100MB
)TO FILEGROUP [FS]
GO

Lets check if the file has been created or not. Given below is the script.

Select * from sys.master_files where database_id =db_id('Sample_DB')
GO

multiplefilestream1.1

Let me know if you have done this in real world and what was the solution.

Advertisements

Read Full Post »