SQL Server 2005：复制、varbinary
场景
在我们的复制方案中，我们复制许多表，包括包含二进制图像数据的 photos 表。 所有其他表都会按预期进行复制，但 photos 表不会。 我怀疑这是因为 photos 表中的数据量较大，或者可能是因为图像数据是 varbinary 字段。 但是，使用较小的 varbinary 字段并没有帮助。
配置信息
这里是一些配置信息：
- 每个图像可以是 65-120 Kb 之间的任何地方
- 修订版和批准的副本与缩略图一起存储，因此单行可能接近 ~800Kb
- 我曾经遇到过“
max text repl”的问题size”配置字段，但我已使用sp_configure和reconfigure with override将其设置为最大值 - 照片是根据“已发布”字段进行过滤的，但是其他工作表也是如此
- 数据库使用相同的本地数据库服务器（在开发环境中）并配置为事务复制
- 复制的数据库使用“推送”订阅
另外，我注意到有时重新生成快照并重新初始化订阅会导致要复制的图像。 考虑到这一点，我将快照代理配置为每分钟左右重新生成一次快照以进行调试（显然这对于生产环境来说是多余的）。 然而，这并没有帮助事情。
问题
是什么导致 photos 表无法复制，而所有其他表都没有问题？ 有没有解决的办法？ 如果没有，我将如何进一步调试？
注释
我使用 SQL Server Profiler 以及复制监视器来查找错误。 不存在错误。 据我所知，该操作只是默默地失败了。
我在 Windows Server 2003 Service Pack 2 上使用带有 Service Pack 3 的 SQL Server 2005。
[更新]
我发现了一个困难的方法 Philippe Grondier 在下面的回答中绝对正确。 图像、视频和其他二进制文件不应存储在数据库中。 IIS 处理这些文件的效率比我高得多。
Scenario
In our replication scheme we replicate a number of tables, including a photos table that contains binary image data. All other tables replicate as expected, but the photos table does not. I suspect this is because of the larger amount of data in the photos table or perhaps because the image data is a varbinary field. However, using smaller varbinary fields did not help.
Config Info
Here is some config information:
- Each image could be anywhere from 65-120 Kb
- A revision and approved copy is stored along with thumbnails, so a single row may approach ~800Kb
- I once had trouble with the "
max text repl size" configuration field, but I have set that to the max value usingsp_configureandreconfigure with override - Photos are filtered based on a “published” field, but so are other working tables
- The databases are using the same local db server (in the development environment) and are configured for transactional replication
- The replicated database uses a “push” subscription
Also, I noticed that sometimes regenerating the snapshot and reinitializing the subscription caused the images to replicate. Taking this into consideration, I configured the snapshot agent to regenerate the snapshot every minute or so for debugging purposes (obviously this is overkill for a production environment). However, this did not help things.
The Question
What is causing the photos table not to replicate while all others do not have a problem? Is there a way around this? If not, how would I go about debugging further?
Notes
I have used SQL Server Profiler to look for errors as well as the Replication Monitor. No errors exist. The operation just fails silently as far as I can tell.
I am using SQL Server 2005 with Service Pack 3 on Windows Server 2003 Service Pack 2.
[update]
I have found out the hard way that Philippe Grondier is absolutely right in his answer below. Images, videos and other binary files should not be stored in the database. IIS handles these files much more efficiently than I can.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我对您的问题没有直接的答案，因为我们的标准政策始终是“绝不将（图片）文件存储在（数据库）字段中”。 我们的解决方案不仅适用于图片，而且适用于任何类型的文件或文档，现在已成为标准：
这个“文档”表在我们的不同订阅者之间复制，就像所有其他表一样
子文件夹，可在我们的每个
数据库服务器。
I do not have a straight answer to your problem, as our standard policy has always been 'never store (picture) files in (database) fields'. Our solution, that applies not only to pictures but to any kind of file, or document, is now standard:
This 'document' table is replicated among our different suscribers, like all other tables
subfolders, available on each of our
database servers.
对于这样的图像表，您是否考虑过将该文章移至单向（或双向，如果您愿意）合并出版物？ 这可能会缓解您的一些问题。
With an images table like that, have you considered moving that article to a one-way (or two-way, if you like) merge publication? That may alleviate some of your issues.