7

I am going to develop a system in C# in which documents have to be scanned and stored and I am expecting that the size of the dB will reach to 3-4 terabytes.

My question is it possible to store them in the database or there is a limit for the DB, or do I have to store them in folders?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eng HanGhul
  • 121
  • 1
  • 1
  • 2
  • A database is composed of files, which store the data. A database can grow as large as the memory available which stores it. – Ryan Wilson Jul 31 '18 at 13:48
  • Look what I found by pasting your title into Google: **[Maximum Capacity Specifications for SQL Server](https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017)** – Ňɏssa Pøngjǣrdenlarp Jul 31 '18 at 13:49
  • Sql Express limit 10GB, other no. But for store file look https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server?view=sql-server-2017 – Stanislav Kundii Jul 31 '18 at 13:49
  • Voting to close, as this can be easily answered by Microsoft product documentation. – Dan Wilson Jul 31 '18 at 13:55
  • [Compare options for storing blobs](https://learn.microsoft.com/en-us/sql/relational-databases/blob/compare-options-for-storing-blobs-sql-server?view=sql-server-2017) which I think is what you *needed* to ask. – Damien_The_Unbeliever Jul 31 '18 at 14:03

2 Answers2

8

Yes there is a limit, it depends on what version of SQL server you are using. If you are using Express:

https://www.gfi.com/support/products/what-are-the-size-limitations-for-sql-express-databases

Microsoft SQL Server 2005 Express edition has a database size limit to 4GB
Microsoft SQL Server 2008 Express edition has a database size limit to 4GB
Microsoft SQL Server 2008 R2 Express edition has a database size limit to 10GB
Microsoft SQL Server 2012 Express edition has a database size limit to 10GB
Microsoft SQL Server 2014 Express edition has a database size limit to 10GB
Microsoft SQL Server 2016 Express edition has a database size limit to 10GB

However other versions have much higher caps:

https://blog.sqlauthority.com/2013/07/21/sql-server-what-is-the-maximum-relational-database-size-supported-by-single-instance/

From the above link:

SQL Server 2008R2, 2012 and 2014 have maximum capacity of 524 PB (Petabyte) in the Enterprise, BI and Standard edition. SQL Server Express has a limitation of 10 GB due to its nature.

So if you are not using Express, pretty much no real limit.

You may consider simply storing those files somewhere and simply using SQL to point to where they are stored and perhaps some meta data about the file. Importing those huge files into SQL really won't get you much, and making managing the database that much harder (backups, performance, etc.) As TomTom and dlatikay pointed out leveraging FILESTREAM may be another option.

sniperd
  • 5,124
  • 6
  • 28
  • 44
  • This is not toally correct. In case of the OP I would assume he could move most documents into a FILESTREAM - which SQL Server manages - and guess what, those do not count then as they are not in the database. – TomTom Jul 31 '18 at 13:53
  • It's possible he would use a FILESTREAM, that's another good suggestion. I personally would prefer to stuff my files onto some huge storage or cloud solution and just use SQL as a pointer. – sniperd Jul 31 '18 at 13:55
  • In Express edition, if you reach the limit because you are storing BLOBs (pictures, documents, ...) you can overcome that limitations by adding a FILESTREAM filegroup because the contents of that do not count towards the database size limit. – Cee McSharpface Jul 31 '18 at 13:55
  • 1
    we need a merge comments feature. – Cee McSharpface Jul 31 '18 at 13:57
  • @sniperd - which is valid so long as you're not expecting *transactional* integrity. I.e. that SQL says "it's over here in this blob storage" and you need that to be true. – Damien_The_Unbeliever Jul 31 '18 at 14:05
  • Good point. Unless we really know more precisely what he is trying to do, it's hard to advise. :) Lots of options! – sniperd Jul 31 '18 at 14:08
  • thanks all ..I will try file tables and hope it works – Eng HanGhul Jul 31 '18 at 14:17
3

There isn't a size limit per say, but performance could be a real issue. I would however say that don't store the scanned documents themselves in the SQL server database as a blob, but rather store a pointer like a file location or S3 bucket location with the document. It would be a generally more performant solution

Eric Yang
  • 2,678
  • 1
  • 12
  • 18