使用SQL Server作为映像存储 [英] Using SQL Server as Image store
问题描述
SQL Server 2008是否可以用作电子商务网站的图像存储？它将用于存储各种尺寸和角度的产品图像。 Web服务器将输出这些图像，通过群集ID读取表。总图像大小约为10 GB，但需要扩展。我发现使用文件系统有很多好处，但我担心没有O（1）查找的SQL服务器不是最好的解决方案，因为该站点有很多流量。这甚至会成为一个瓶颈？有什么想法，或许是其他选择？
Is SQL Server 2008 a good option to use as an image store for an e-commerce website? It would be used to store product images of various sizes and angles. A web server would output those images, reading the table by a clustered ID. The total image size would be around 10 GB, but will need to scale. I see a lot of benefits over using the file system, but I am worried that SQL server, not having an O(1) lookup, is not the best solution, given that the site has a lot of traffic. Would that even be a bottle-neck? What are some thoughts, or perhaps other options?
推荐答案
10 Gb的数据量不是很大，所以你可能会使用数据库存储它并没有大问题，但当然使用文件系统是最好的性能，安全管理明智地使用数据库（备份和一致性）更好。
10 Gb is not quite a huge amount of data, so you can probably use the database to store it and have no big issues, but of course it's best performance wise to use the filesystem, and safety-management wise it's better to use the DB (backups and consistency).
很高兴，Sql Server 2008允许你吃蛋糕并吃掉它：
Happily, Sql Server 2008 allows you to have your cake and eat it too, with:
在SQL Server 2008中，可以将FILESTREAM属性应用于varbinary列，然后SQL Server将该列的数据存储在本地NTFS文件系统上。将数据存储在文件系统中有两个主要好处：
In SQL Server 2008, you can apply the FILESTREAM attribute to a varbinary column, and SQL Server then stores the data for that column on the local NTFS file system. Storing the data on the file system brings two key benefits:
- 性能与文件系统的流式传输性能相匹配。
- BLOB大小仅受文件系统卷大小的限制。
但是，列可以像任何一样管理SQL Server中的其他BLOB列，因此管理员可以使用SQL Server的可管理性和安全性功能将BLOB数据管理与关系数据库中的其余数据集成，而无需单独管理文件系统数据。
However, the column can be managed just like any other BLOB column in SQL Server, so administrators can use the manageability and security capabilities of SQL Server to integrate BLOB data management with the rest of the data in the relational database—without needing to manage the file system data separately.
在SQL Server中将数据定义为FILESTREAM列还可确保数据库中的关系数据与物理存储在文件系统上的非结构化数据之间的数据级一致性。 FILESTREAM列的行为与BLOB列完全相同，这意味着完全集成了维护操作，如备份和还原，与SQL Server安全模型的完全集成以及完全事务支持。
Defining the data as a FILESTREAM column in SQL Server also ensures data-level consistency between the relational data in the database and the unstructured data that is physically stored on the file system. A FILESTREAM column behaves exactly the same as a BLOB column, which means full integration of maintenance operations such as backup and restore, complete integration with the SQL Server security model, and full-transaction support.
应用程序开发人员可以通过两种编程模型之一处理FILESTREAM数据;他们可以像标准BLOB列一样使用Transact-SQL来访问和操作数据，或者他们可以使用带有Transact-SQL事务语义的Win32流API来确保一致性，这意味着他们可以使用对FILESTREAM的标准Win32读/写调用如果与文件系统上的文件进行交互，就会产生BLOB。
Application developers can work with FILESTREAM data through one of two programming models; they can use Transact-SQL to access and manipulate the data just like standard BLOB columns, or they can use the Win32 streaming APIs with Transact-SQL transactional semantics to ensure consistency, which means that they can use standard Win32 read/write calls to FILESTREAM BLOBs as they would if interacting with files on the file system.
在SQL Server 2008中，FILESTREAM列只能在本地磁盘卷上存储数据，而某些功能如透明FILESTREAM列不支持加密和表值参数。此外，您不能在数据库快照或数据库镜像会话中使用包含FILESTREAM列的表，但支持日志传送。
In SQL Server 2008, FILESTREAM columns can only store data on local disk volumes, and some features such as transparent encryption and table-valued parameters are not supported for FILESTREAM columns. Additionally, you cannot use tables that contain FILESTREAM columns in database snapshots or database mirroring sessions, although log shipping is supported.
这篇关于使用SQL Server作为映像存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
