保存的PDF文件，在SQL Server中的二进制对象，是或否？ [英] Storing PDF files as binary objects in SQL Server, yes or no?

问题描述

我必须找到以下任务设计决定：

我有一个SQL Server数据库，它包含一个订单表。 PDF文档将由用户通过从网页一个简单的文件上传上传并指定给订单。没有每个订单多个文档（也许没有文件，从来没有超过一个）。为此，用户打开网页，输入订单号，获取显示顺序，然后点击上传按钮。所以我知道哪个命令上传文件属于

现在我正在考虑两种选择存储在Web服务器上的文件：

1）由VARBINARY（MAX）列延长我的订单表，并直接存储PDF文档成二进制字段。

2）在一个特定的文件夹保存在磁盘上的PDF文件，并​​给它涉及到订单生成一个唯一的名称（例如，我可以在附加存储我的订单号是在数据库中的主键或GUID顺序表的列）。也许我必须将文件存储在子文件夹，每月一个，子文件夹名称存入的顺序排在数据库中，以避免收到一个文件夹中过多万个文件。

在PDF文件被保存就可以进入相关的订单号后，可以下载，并通过浏览器查看。

我趋向于选项（1），因为数据管理似乎更容易具有在一个数据库中的所有相关数据箱。但我有点害怕，我可能会遇到性能问题，随着时间的推移，因为我的数据库大小将增长比溶液（2）要快得多。大约90％的总数据库大小，甚至95％的人只有那些存储PDF文件来弥补。

下面是一些额外的信息：

• 的PDF文件将具有一个大小约100千字节每个


• 1500左右的订单/每月PDF文件


• 在Windows Server 2008 R2 / IIS 7.5


• 的SQL Server 2008 SP1前preSS


• 不太清楚有关硬件，相信1四核PROC。和4 GB RAM


• 应用程序写在ASP.NET Web表单3.5 SP1

（我知道，我会经过约2年以上的数字达到SQL Server的防爆preSS版的4GB的限制，但我们可以在这里忽略这一点，无论是从数据库或升级删除旧数据一个完整的牌照将是一个可能的选择。）

我的问题是：什么是选项的Pro和反政府和你有什么建议？也许有人也有类似的任务，可他的体验报告。

感谢您事先的答复！

  

相关阅读：

  
  

在数据库中存储的图像 - 是啊，还是不是

解决方案

通过SQL Server 2008中，当你有大多是1 MB或更大尺寸的文件，将建议FILESTREAM功能。这是基于微软研究院发表的一篇论文叫为BLOB或不BLOB 哪些分析了利弊，并在大篇幅的数据库中存储BLOB的利弊 - ！伟大的阅读

对于小于256K的平均文件，将它们存储在 VARBINARY（MAX）列似乎是最合适的。

任何之间是有点难以取舍的，真的。

你说你有PDF文档主要是围绕10万左右 - >将那些非常漂亮存储到一个SQL Server表，没有问题。你可能要考虑的是具有链接到的主要事实表的文件一个单独的表一件事。这样，事实表将在使用更快，文件没有在其他数据的方式获得。

I have to find a design decision for the following task:

I have a SQL Server database and it contains a table of orders. PDF documents will be uploaded by users through a simple file upload from a web page and assigned to an order. There is not more than one document per order (perhaps no document, never more than one). For this purpose a user opens a web page, enters an order number, gets the order displayed and clicks on an upload button. So I know to which order the uploaded document belongs to.

Now I am considering two options to store the documents on the web server:

1) Extend my table of orders by a varbinary(MAX) column and store the PDF document directly into that binary field.

2) Save the PDF file in a specific folder on disk and give it a unique name related to the order (for instance my order number which is a primary key in the database, or a GUID which I could store in an additional column of the order table). Perhaps I have to store the files in subfolders, one per month, and store the subfolder name into the order row in the database, to avoid getting too many thousand files in one folder.

After the PDF files are stored they can be downloaded and viewed via browser after entering the related order number.

I'm tending towards option (1) because the data management seems easier to me having all relevant data in one database. But I am a bit afraid that I could encounter performance issues over time since my database size will grow much faster than with solution (2). Around 90% or even 95% of the total database size would be made up only by those stored PDF files.

Here is some additional information:

• The PDF files will have a size of around 100 Kilobyte each
• Around 1500 orders/PDF files per month
• Windows Server 2008 R2 / IIS 7.5
• SQL Server 2008 SP1 Express
• Not quite sure about the hardware, I believe one QuadCore Proc. and 4 GB RAM
• Application is written in ASP.NET Webforms 3.5 SP1

(I am aware that I will reach the 4GB-limit of the SQL Server Express edition after around 2 years with the numbers above. But we can disregard this here, either removing old data from the database or upgrading to a full license will be a possible option.)

My question is: What are the Pro and Contras of the options and what would you recommend? Perhaps someone had a similar task and can report about his experience.

Thank you in advance for reply!

Related:

Storing Images in DB - Yea or Nay?

解决方案

With SQL Server 2008, when you have documents that are mostly 1 MB or more in size, the FILESTREAM feature would be recommended. This is based on a paper published by Microsoft Research called To BLOB or not to BLOB which analyzed the pros and cons of storing blobs in a database in great length - great read!

For documents of less than 256K on average, storing them in a VARBINARY(MAX) column seems to be the best fit.

Anything in between is a bit of a toss-up, really.

You say you'll have PDF documents mostly around 100K or so -> those will store very nicely into a SQL Server table, no problem. One thing you might want to consider is having a separate table for the documents that is linked to the main facts table. That way, the facts table will be faster in usage, and the documents don't get in the way of your other data.

这篇关于保存的PDF文件，在SQL Server中的二进制对象，是或否？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！