October 1, 2009 at 1:10 am
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...
October 1, 2009 at 4:59 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 2, 2009 at 1:55 am
is there any way other than file stream in sqlserver 2008 for storing large files suchas 10gb file.
October 2, 2009 at 2:14 am
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]
October 2, 2009 at 6:27 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 3, 2009 at 4:34 am
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..
October 3, 2009 at 11:13 am
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
October 4, 2009 at 6:40 am
jeff has right 😉
1) bigger files shouldnt be stored in SQL Server
2) filestream is a real nice solution
October 4, 2009 at 10:35 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 4, 2009 at 10:48 am
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
October 5, 2009 at 2:32 am
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
October 5, 2009 at 6:03 am
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
October 6, 2009 at 6:02 am
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..
October 6, 2009 at 9:33 am
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
October 7, 2009 at 1:01 am
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?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply