2

Here is the scenario:

1) All the data currently is stored in SQL Server

2) There is one table X that stores the most important and highly accessed data

3) Each record in the table X has one column Y that stores up to 2GB of text

4) Number of records is growing (> 10 million and growing)

5) The column Y with max of 2GB of text per row takes about 90% of space of table X

Problem: database becomes unmanageable and we need to store data from Column Y somewhere else (outside of SQL Server)

Question:

What is the best solutions for storing large text?

I am looking for a solutions with least amount of overhead:

  • fast reads
  • fast writes
  • scalable
  • can handle concurrency well

I don't care about full text index or any extra overhead. Just plain storage and access.

PS

Some ideas that I have so far:

  • very simple NOSQL database
  • flat file - based database
  • custom solution that I can write from scratch using some library
Chicago
  • 1,619
  • 4
  • 19
  • 32
  • have you looked at filestream or filetable – Sebastian Meine Oct 09 '13 at 20:51
  • I have looked at filestream and filetable. But I am looking for something completely independent of SQL Server – Chicago Oct 09 '13 at 20:54
  • If the average record size is > 10MB, plain files are probably the best solution. However, design a good directory structure / hierarchy, as windows has problems with lots of files in a single directory. – Sebastian Meine Oct 09 '13 at 21:00
  • I was thinking about plain files, and even combining several records into one plain file because windows does not like lots of files. Is there a super-fast simple database that can read/write from/to plain text files? Or maybe there is a set of libraries that can help? Most importantly it needs to scale out and be perfromant – Chicago Oct 09 '13 at 21:05
  • average size of files varies. But most importantly numbers of records is hight enought that table X grows to Terrabytes – Chicago Oct 09 '13 at 21:10

1 Answers1

2

From what I read about NoSQL technologies, Riak may be a great fit, however, there's certainly overhead with this. Flat files sound promising, but highly OS and patch dependent - you don't want to be stuck explaining to a customer that Windows Updates caused performance problems.

user1599043
  • 121
  • 2
  • Do you have any experience with flat files storage technologies – Chicago Oct 11 '13 at 22:14
  • Here's a similar discussion, however your size requirements are on a different scale. Still, most of the responses in this thread are relevant. [Local Storage in .NET](http://stackoverflow.com/questions/1941928/best-way-to-store-data-locally-in-net-c) – user1599043 Oct 14 '13 at 13:31