how to store large files in sqlserver 2008

Question

how to store large files in sqlserver 2008

RockingAdmin

SSCrazy

Points: 2828
More actions
October 1, 2009 at 1:10 am

#385477

hi all,
How to store very large files(10GB) such as videos,audios,word doc,pdf,excel etc in sqlserver. Sqlserver varbinary(max) will allow to store upto 2GB.
but how to store files more than 2GB size.currently my requirement can store upto 10GB. using the filestream can do this. but the document will be kept in the machine only after we upload a document, The path of that document will be stored in the column. what happens if that document was removed later.
can any one guide me
Thanks
Rock...

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You want to use filestream. It puts a sort of lock on the file to ensure database integrity. As long as the record exists in the SQL Server, the file can't be removed from the associated file system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RockingAdmin SSCrazy Points: 2828 More actions · Answer 2

is there any way other than file stream in sqlserver 2008 for storing large files suchas 10gb file.

Silverfox SSC-Insane Points: 23150 More actions · Answer 3

better to use filestream, that is what it was designed for and if you are using sql 2008, it is the best way.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

rockingadmin (10/2/2009)
is there any way other than file stream in sqlserver 2008 for storing large files suchas 10gb file.

None that I'm aware of. You could create 8 or 10 different fields in a table & split the file across them, or find some way of associating X number of rows and splitting it up... Not approaches I'd advocate. FileStream is designed for this, I'd use it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RockingAdmin SSCrazy Points: 2828 More actions · Answer 5

Hi,

file stream was introduced in sql2008. I am bit eager to know, what about earlier versions of sqlserver2000 and 2005?

i thank each and every one for sharing ideas.

Regards,

Rock..

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

rockingadmin (10/3/2009)
Hi,
file stream was introduced in sql2008. I am bit eager to know, what about earlier versions of sqlserver2000 and 2005?
i thank each and every one for sharing ideas.
Regards,
Rock..

For files of that size, I don't store them in the database... I just store the file name and path.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 7

jeff has right 😉

1) bigger files shouldnt be stored in SQL Server

2) filestream is a real nice solution

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Yet more agreement with Jeff. It's what I've done in the past and it's what third party vendors like Documentum do.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

Yet even more agreement with Jeff. Big files stored in SQL is just not the best solution. Link in SQL pointing to the file is the best solution..

CEWII

EdVassie SSC Guru Points: 60472 More actions · Answer 10

I also agree with Jeff. When you get files into the GB range then Filestream is the only way to go.

Google 'To Blob or not to Blob', a good whitepaper from Microsoft that looks at the relative performance of storing large objects in SQL tables compared to the file system. Sometimes there are reasons beyond just performance that might mean the best solution for you is to store objects sized in many MB in SQL tables, but by the time you get to GB sized objects you have to think of Filestream as being the right approach if you are using SQL Server as the storage mechanism.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

Just to be clear, the suggestion I made was for pre-2k8 solutions. Pass the file path/name from the database to the app and let the app get the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RockingAdmin SSCrazy Points: 2828 More actions · Answer 12

i accept it. but i have a problem using the file stream in sql server 2008.

I have created a table using file stream

ex: USE TEST_DB

GO

CREATE TABLE FILETABLE

(

GUID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

DATA VARBINARY(MAX) FILESTREAM

);

executed successfully........

and i inserted sample data with one ms word document into the above table.

ex:

insert into FILETABLE values(NEWID(),cast('C:\test.doc' as varbinary(max)));

executed successfully.........

after that i deleted the word document from c drive.

as Grant Fritchey said, there will be a lock on the file to ensure database integrity. As long as the record exists in the SQL Server, the file can't be removed from the associated file system.

But i have deleted the file. where will be the lock? how the database integrity will be available with out the file in c drive? then how this file stream will be useful with out the lock in that file?

Thanks

Rock..

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

I think you misunderstand FILESTREAM.. All you stored was the text 'C:\test.doc' it stores the file in IT's location and it doesn't keep the same name, it ends up as a GUID. I'll see if I can generate an example..

CEWII

RockingAdmin SSCrazy Points: 2828 More actions · Answer 14

I agree. I know it will be stored in file group which contains files stream data. i inserted a 10mb file in the file stream column, i didn't find any file that contains size of 10mb file in file stream data(file group). In the file group location i saw only 1kb files.

where will be the 10mb file after inserting. as i said previously, i can able to remove the original files. if the original file is removed, how sql server points to the original document that contains 10mb file?