one terabyte of data

Question

one terabyte of data

jugaljugal

Mr or Mrs. 500

Points: 539
More actions
July 18, 2003 at 12:18 pm

#82289

Hi All,
Currently we are using san storage to store files in pdf format. This has led to space issues on the disk. we have about 1 gb of data. What I wanted to know is that whether sql 2000 server would support blobs written into it for so much data or not? How scalable is it ? Will there be performance issues ? Currently we store index information in the database. Index information would have a particular users data files location . Our component then goes and reads that file from San storage disks. I wanted to store the files in pdf format in blobs in sql 2000 server. IS this possible ?
Thanks
Jugal

Viewing 12 posts - 1 through 11 (of 11 total)

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

NPeeters SSChampion Points: 12535 More actions · Answer 1

Shouldn't be a problem to store blobs in the database if it is 'only' 1GB worth of data (even a terabyte is possible). However, I don't see how storing the stuff in the database will solve your space issues on the SAN? You have to provide the room somewhere...

As for storing stuff in the DB, take into account that the load on your database server will increase tremendeously. So you might want to add some memory and/or processors.

jugaljugal Mr or Mrs. 500 Points: 539 More actions · Answer 2

Thanks for the info NPeeters.The total data would be about 1 terabyte. One blob will have a max of 2/3 mb. San Storage compatibility issue with NT is well known . Also purging from the database would be very simple

-Jugal

NPeeters SSChampion Points: 12535 More actions · Answer 3

If I where in your shoes, I would stick with the current method of storing files on the filesystem and indexing stuff in the database. In the end, that's what a filesystem is for.

Keep in mind that you'll need a very powerful database server if you want to achieve an acceptable response time for your users.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

In addition of performance, You also have to face the large database maintenance and database recovery issues.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Jugal,

quote:
Currently we are using san storage to store files in pdf format. This has led to space issues on the disk. we have about 1 gb of data. What I wanted to know is that whether sql 2000 server would support blobs written into it for so much data or not? How scalable is it ? Will there be performance issues ? Currently we store index information in the database. Index information would have a particular users data files location . Our component then goes and reads that file from San storage disks. I wanted to store the files in pdf format in blobs in sql 2000 server. IS this possible ?

just some facts about what I'm doing here.

I store pdf's in one db. This db serves no other purpose than storing binary data (mostly the mentioned pdf's, some doc's and xls's). The stored data is only for read purposes, no updates, delete. As of now there are ~2GB of data in the db. All in all about 12,000 records. No problem encountered this far. The vast majority of files are very small, I would say <10kb. The biggest ones go up to 10MB in size. I haven't seen a performance issue, but this could come from the number of users with access to that db. All in all there are not more than 10 users for this db. Another user of this forum (Hello Dale), has posted some time ago a really informative link on BLOB in a SQL Server db

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp

I recomment reading this, because it clarified some things for me.

So, to answer your question: Yes, it is possible, but if you really want to...?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Normally I would suggest pushing to the filesystem and stroing pointers to that location for you pages or app to be able to retrieve. But with changes in the way ADO can parse the data and the fact MS is pushing toward DB storage (the next generation file system they have coming is based on SQL Server database file system on top of NTFS) I see no issues with doing this.

However I would suggest creating another filegroup and putting the blobs (image, text, ntext, etc) in that filegroup seperate from the main data. I don't have the details in front of me but it does help in serveral ways with the performance of the rest of the data.

guarddata SSCertifiable Points: 6583 More actions · Answer 7

Keep in mind also - depending on your configuration you may actually be storing twice as much when saving in the database because of the transaction log. We have found small files to save faster to the DB than disk while large files (over about 50KB) to save faster to disk. Depends on your needs.

Guarddata-

dnafe Grasshopper Points: 24 More actions · Answer 8

Another thing to keep in mind is $$$. If you will need more processors and your license is per processor, you could be adding a large license cost.

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 9

As of today I would definitly not recommend storing image files on the DB.

Storing those files will be much faster and you will use much less ressources then storing them in the DB.

Of cours you can define the apropriate security setting to allow only your DB server accessing to that folders where your images are located.

And my last argument: if you store your files as file you do not have to write your own pdf reader which handles the data stream coming from the DB

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Hi nyulg,

quote:
As of today I would definitly not recommend storing image files on the DB.
Storing those files will be much faster and you will use much less ressources then storing them in the DB.
Of cours you can define the apropriate security setting to allow only your DB server accessing to that folders where your images are located.

as has been mentioned above, you must judge this on your individual situation. Personally I think there isn't a general YES or NO top this question.

quote:
And my last argument: if you store your files as file you do not have to write your own pdf reader which handles the data stream coming from the DB

well, when using asp I can live with this two extra lines (one time written and reused) of code:

Response.ContentType = "application/pdf"

Response.BinaryWrite oRecSet.Fields("MsgAttachment")

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jugaljugal Mr or Mrs. 500 Points: 539 More actions · Answer 11

Everybody,

thanks for your advices. I think storing pdf files in file system is the option I have to stick to as of now.

Jugal