如何使用 SQL Server FILESTREAM 功能处理大型数据库

SQL Server FILESTREAM 功能是什么？

此功能允许将非结构化文档添加到数据库中。您可以在 SQL Server 数据库中存储图像、PDF、视频或任何您想要的文件。它允许您拥有图像的事务一致性。

我们曾与大型 FILESTREAM SQL Server 实施合作过。我将展示此功能如何导致许多问题。以及您可以采取哪些措施来解决这些问题。

 

何时 MS SQL FILESTREAM是一个好的解决方案？

1. 如果 3-5 年内数据大小将增长到 1TB 以下 – FILESTREAM 可能可以发挥作用。
2. 如果您需要对存储的文档或图像进行时间点恢复。
3. 如果您存储小文件（每个文件小于 1MB）。请查看此白皮书进行确认。其中提到了文件大小。

如果一切属实——那就去做吧！

您在下面读到的所有问题并非都可以归咎于 SQL Server。

有些数据库管理很糟糕。

这是一个业务问题— 如何使用 SQL Server —业务量每 9 个月翻一番，但没有人为这种类型的数据爆炸做好计划。

有些编码本来可以更好。

SAN 和一些网络使一些事情变得过于复杂。

有些人已经尽了最大努力，但结果却更糟。

重点是——这种解决方案的边缘很锋利，如果不小心，你会割伤自己。

SQL Server FILESTREAM 的问题和一些解决方案

备份

1. DB 备份持续时间。
   随着 FILESTREAM 数据库的增长，我们无法再快速进行完整数据库备份。备份持续时间开始超过 24 小时，然后很快 48 小时就不够了。最后，完整备份将于周五下午 6 点开始，而周一早上，它仍在进行中。
   • 修复。加快 SQL 备份。将备份拆分为 32 个文件很有帮助。此外，更改BUFFERCOUNT, BLOCKSIZE和– 也很有帮助。请在此处MAXTRANSFERSIZE 查看更多信息。
   • 您如何调整备份速度，如何找到最适合您情况的备份文件数、和的数字BUFFERCOUNT？BLOCKSIZE ……MAXTRANSFERSIZE测试。我们进行了很多次。
     我们测试了一个非常低的数字（BUFFERCOUNT），然后得到了很高的数字。我测量并记录了数据。找到了一个最佳参数。
     然后转到下一个参数（BLOCKSIZE）。找到了最佳参数。转到下一个。您明白了。
     当所有参数都 亚马逊数据库 有最佳值时，我们将它们全部合并。我不记得确切的数字，但它大大缩短了备份时间。缩短了 30-40% 左右。
2. 备份大小。
   备份大小变得难以管理。由于设计方式的原因，我们不得不使用完整备份。
3. 愚蠢的备份。
   这是我的“技术术语”。这意味着每次完整备份都包含 10 年前的数据。继续备份不会改变的数据是愚蠢的吗？但要达到只备份最近数据的程度，需要做一些工作——稍后会详细介绍。
4. SAN 存储。
   由于这是一个具有共享存储和有限 SAN 空间的集群，我们开始遇到 SAN 容量问题。
   您不仅必须存储大型数据库。然后，您至少需要两个完整备份。而且数据压缩效果不佳。因为存储的数据都是 PDF/jpg 等 – 已经压缩。因此，现在我们需要数据库大小乘以三……至少。
   添加差异和事务备份。您可以看到 50TB 数据库很容易需要 150-200TB 的 SAN 存储。这不是每个人都能拥有的奢侈品。
5. 低级环境。
   将 FILESTREAM 数据库还原到 DEV、QA、UAT 环境变得不可能。这不仅是因为所需的时间几乎是“永远”。而且，低级环境通常没有那么多 TB 可供您使用。
   • 修复– 我们将部署由 T-SQL 脚本构建的数据库，而不是备份和恢复来刷新 DEV、QA 和 UAT。
   • 然后运行另一个脚本，将文档填充到每个表中的 100,000 条记录。这样，我们只需要 1GB，而不是 50TB。
6. SAN 速度。
   这不是 SQL Server 问题。但 SAN 配置严重错误。吞吐量数字很糟糕。在某些时候，传输速度比 USB2 速度.

 

 

1. 如果您还在这里，您可能仍在考虑将 FILESTREAM 用于您的项目。继续阅读，因为到最后您可能不太喜欢此功能。

分区

1. 备份 10 年前的数据。
   在某个时候，我们意识到唯一被修改 来自各种颜色 的数据是过去 90 天内存储的文档。如果我们可以将数据划分为单独的文件组 (FG)，我们可能只备份过去 90 天的数据。而不是 10 年的数据。现在我们开始讨论！
2. 你好，SQL 分区！
   • 我们对数据进行了分区，并将文件组 (FG) 拆分为每年。将这些文件组设置为 READ_ONLY。备份 READ_ONLY FG 一次。这包含了大部分存储空间。
   • 留下了过去 90 天的活动分区。现在可以将此 FG 移至更快的 SAN 层，因为我们不再需要大量的空间。

但在实现分区之前，我们必须撤消别人失败的分区尝试。

防病毒和数据库损坏

当我们开始处理这个 FILESTREAM DB 时，首先要做的事情之一就是运行DBCC CHECKDB。

发现了大约 40 个损坏错误。这些错误无法通过 SQL 命令修复。

我们最终致电微软支持，他们的 议是将所有数据导出到新数据库中。然后扔掉旧的。

– 嗯……是的……那大概需要嗯……永远……谢谢，但我们将其作为最后的选择。

我将这个损坏的数据库恢复到另一台服务器上，并测试了几项。并且能够修复损坏！！哈哈！

基本上，问题在于 FS 将图像存储在磁盘上。采用奇怪的目录结构。当 DB 中的记录被删除时，磁盘上的文件却没有被删除。这让 SQL Server 认为 – DB 已损坏。

我所要做的就是弄清楚如何将损坏的记录转换为目录路径（不，我现在不记得怎么做了），然后进入磁盘并删除该路径。重新运行DBCC CHECKDB，然后砰！我少了一个错误。

又做了39次之后，全部消失了！

强烈怀疑其中涉及防病毒软件，因此重新配置了防病毒例外。

RAM 使用情况

FILESTREAM 的另一个问题是应用程序如何检索数据。

如果应用程序运行“SELECT”语句 by 列表 来获取文档，并且文件大小为 5MB，则所有 5MB 都会进入 SQL 缓冲区，即 RAM 内存。

如果对大量查询执行此操作（5MB 乘以 500 个请求），则意味着会浪费大量 RAM。不仅如此，每个 5MB 文档都会从 RAM 中剔除真正有用的数据。

建议：不要使用 SELECT 来获取图像。改用应用程序中的Win32 API 。这样可以绕过这个问题，并且运行速度更快！

FILESTREAM DB 中的某些数据完全是垃圾

这不是 SQL 问题。这是应用程序开发能力差造成的。

事实证明，该应用程序并未 限制用户上传仅允许的数据类型，例如图像或 PDF。并且该应用程序没有大小限制。

因此，我们发现上传了 EXE 文件、300MB 文件、mp3 和其他垃圾文件。

始终开启

在所有变更进行到一半时，我们不得不迁移到新的数据中心。为了不浪费机会，我们做了一些事情。

• 我们不再使用纯 Win 故障转移群集和共享存储，而是使用 AlwaysOn。
• 已升级 SQL 版本。
• 使用硬件、操作系统和 SQL 设置的所有最佳实践。

对于AlwaysOn，我们不能将此文件流数据库放入与用户数据库相同的可用性组中。

它必须是自己的 AG 组。这意味着用户数据库不会总是在同一台服务器上。这会产生问题。因为我们有从用户数据库延伸到文件流数据库的查询。

我不会深入讨论我们是如何解决这个问题的，但我们确实解决了。重点是——这需要一种解决方法。因为该死的 FILESTREAM 怪物。

AlwaysOn 可以使用本地存储。这很酷。不好的是，我现在必须在每台服务器上为这个大家伙留出磁盘空间。4 个副本，加上一个 DR 站点。

 FILESTREAM 的流行度

1. 这不是一个被广 泛采用的 SQL Server 功能。在网上寻找答案 – 很有挑战性。
2. 只有一份FILESTREAM 白皮书。
3. 关于 FILESTREAM 的书只有一本。看看这本书有多少评论。一本！而且是一本好书。但这应该说明只有极少数公司采用了此功能！

DB 增长预测

在某个时候，我们决定看看增长预测。DB的规模每 9-11 个月就会翻一番。

嗯……等等……什么？你是说明年这个问题会加倍严重吗？

该死……我最好把这颗定时炸弹留给别人。是的，我脑子里闪过这个想法！

解决这个问题的唯一办法就是重新架构。

结论

SQL Server FILESTREAM 功能的用例非常少。

在 SQL Server 内部存储文件的方式有很多种，可能会造成损害。

实现无限增长的唯一方法是不要将文件存储在 SQL Server 内部。