You will find there's project approaching where we are creating a whole after sales Content management systems system that perseverence our entire extranet and intranet with one package. The question I've been looking for an response to is what's best: storing images within the database (SQL Server 2005) therefore we might have integrity, single replication plan, etc OR storing around the file system?

One problem we now have is the fact that we now have multiple servers load balanced that need to achieve the same data whatsoever occasions. By we now have SQL replication being careful of this but file replication appears to become a little harder. Another concern we now have is the fact that we wish to have multiple resolutions of the identical image, we're not confident that creating and storing each version around the file system might be best or possibly dynamically tugging and creating the resolution image we wish upon request.

Our concerns would be the using the following:

  • Data integrity
  • Data replication
  • Multiple resolutions
  • Speed of database versus file system
  • Overhead load of database versus file system
  • Data management and backup

Does anybody have the identical situation or have input on which could be suggested? Thanks ahead of time for that help!

There is a pleasant research paper released by Microsoft Research known as To Blob or otherwise to Blob where they looked whatsoever types of variables and impacts.

Their finding ultimately:

  • as much as 256 KB in dimensions, blobs are saved within the database more effectively compared to the file system
  • for 1 Megabytes and bigger, the file product is more effective
  • among it is a toss-up

Since that paper was released, SQL Server 2008 has additionally added the FILESTREAM attribute making storing stuff within the file system, but under transactional control, a real possibility. Highly suggested you make sure that out!

This pops up frequently - see this SO google listing.

There's nobody right answer - it is dependent on conditions.

Personally - have a file path within the DB and also the file around the filesystem. Each features its own talents. You are able to backup files in addition to databases. This is the final outcome of this person, who handles The best spinner's of information.

Replication of static files, especially across numerous servers, can be challenging to handle. It comes lower to some tradeoff between controlling, monitoring and debugging replication problems versus. the database size and load.

I believe I'd most likely select the database approach, and when load grew to become an problem take a look at setting up some kind of cache layer round the image calls.

Tips to store a path within the db are missing the actual problem, that is replicating this across multiple machines.

Well, in case your top two needs are integrity and replication, then the reply is certainly DB.

You other points though:

  • Integrity - DB, this is exactly why databases exist versus. flat file systems.

  • Replication - Unsure should you mean image replication, but when so, then clearly DB while you will not be load balancing this, surely.

  • Multiple resolutions could be carried out in the DB image, however this adds processing costs. Also, the greater the resolution, the higher the dimensions, the more the network wait. Multiple resolutions trades space for speed.

  • Speed - Based on use of the pictures, it may be minimal. If you're taking images across personal files share, you will need to wait around the network regardless and also the network is virtually always the bottleneck.

  • Overhead - Frankly, it is dependent in your meaning of overhead and just how you access the pictures.

  • Management, DB, hands lower. Singular storage = One less worry, and you ought to continually be running backup copies around the database regardless. File system backup copies over multiple servers is pricey in lots of ways.

Your concerns break lower into two camps. The next concerns favour storing documents within the database:

  • Data integrity
  • Data replication
  • Multiple resolutions
  • Data management and backup

These concerns (most likely) favour storing documents around the file system:

  • Speed of database versus file system
  • Overhead load of database versus file system

So, decide what matters probably the most and judge accordingly.