SQL Server 2005: Replication, varbinary

Question

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 using sp_configure and reconfigure 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.

Answer 1

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:

• We have a "document" table in our database, where document/file names and relative folders are stored (in order to get unique document/file names, we generate them from the primary key/uniqueIdentifier value of the 'Document' table).

This 'document' table is replicated among our different suscribers, like all other tables

• We have a "document" folder and subfolders, available on each of our database servers.
• Document folders are then replicated independently from the database, with some files and folders replication software (allwaysynch is an option)
• main publisher's folders are fully accessible through ftp, where a user trying to read a document (still) unavailable on his local server will be proposed to download it from the main server through a ftp client software (such as coreFTP and its command line options)

Answer 2

This blog post has some pointers of things you might want to try out to make sure everything is working correctly.

Answer 3

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.