Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite
1

I need to develop a production system capable of storing a 100 kb textfile and a serial number for every piece produced. I know the approximate output over 2 years, the time the data has to be stored, and have calculated a need of around 25-30 TB of storage.

I have some experience working with databases, but handling that amount of data is new to me.

My first guess would be to simply store the files as a blob inside a table in the database. What are the problems of storing that amount of data in a single table? What are possible backup scenarios?

Another approach would be to store the data in several tables (e.g. a table per month).

A third approach would be to store references to the data in SQL Server, zip the files, and store them somewhere else.

The purpose of this storage system is to query the text file by using the serial number.

What would you do?

share|improve this question
5  
"query the textfile" -- do you mean search inside it, or just return it? If all you need is essentially a key/value store, maybe think about using FILESTREAM. Also, it's possible that SQL Server may not be the best solution for this problem. – Jon Seigel Oct 31 '12 at 16:33
3  
some text files shrink to 1-2% when zipped. Have you tried to compress? – AlexKuznetsov Oct 31 '12 at 17:14

3 Answers

up vote 4 down vote accepted

Historically, I'd shy away from storing files in the database and instead include pointers like in your alternative 3. With SQL Server 2012, however, you might look into the FileTable feature. The files are still stored within SQL Server (meaning that you'd have to be aware of the implications on your backup strategy), but also are easily accessible through the standard Windows API so you can manipulate them in Windows Explorer or PowerShell. If you actually need to search within the text files as well, FileTable can do that.

On the other hand, storing 25-30 "extra" TB of data within a SQL Server database will be a challenge to maintain. If this is just for compliance/auditing purposes (i.e., the probability of somebody actually looking at a particular file is low) and files aren't inserted very often, I'd probably stick with the pointers-to-files approach. But if you need access frequently or there are a lot of changes, the advantage goes to FileTable because it takes care of the pointer-handling part of the process.

share|improve this answer
up vote 1 down vote

If all you need is a key/value store, you might want to look at some of the NOSQL options as an RDBMS may be overkill. If you stay with SQL Server, you should investigate the "table partitions" feature, which can help organize very large tables, and table/data compression feature, which can reduce the amount of space that data takes on disk and in memory.

share|improve this answer
up vote 0 down vote

For SQL Server 2012 you should evaluate row compression:

http://msdn.microsoft.com/en-us/library/cc280449.aspx

If your typical 100kb file compresses well you could find yourself with a much smaller database.

share|improve this answer
1  
No; LOB values are not compressed. From that link: "Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately." The files would have to be compressed by some other means before being inserted into a row. – Jon Seigel Nov 1 '12 at 22:40
If the contents of the text file are stored as text in the database then compression will work fine. – Jimbo Nov 2 '12 at 11:42
1  
Only data that fits on the data page. At 100 KB per column value, it will mostly row overflow. I tested with create table blarg(a int identity primary key, b varchar(MAX) default replicate(replicate('a', 8000), 8000)); set nocount on; GO insert into blarg default values; go 10000 (this is about 80 MB of data) then used the compression wizard in Management Studio: it gives roughly the same numbers before/after. – Jon Seigel Nov 2 '12 at 13:31
@Jimbo: sorry, but as Jon already pointed, the page compression is available only for the data page. Not for LOBs. – Marian Nov 6 '12 at 21:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.