1

So currently in our system we have been storing image files in the database (SQL Express 2005). Unfortunately it wasn't perceived that this would reach the max database size allowed by the SQL Express License. So I have proposed a plan of storing the images in the file system and only indexing where the file is in the database.

The plan is to save the root path in our OptionsTable as something like ImagesRoot and then only saving the actual imageID in the table, which is basically a FK from the PK of the record with the image. I have determined that it would be best to then split this down into sub-directories inside of the ImagesRoot based on every 1000 images so basically (ImagedID / 1000)\(ImageID % 1000) (e.g. ImageID is 1999 it would be in %ImageRoot%\1\999).

I'm looking for any potential pitfalls of this system and any thing that could be improved as I am already receiving some resistance from the owner of the company who wants everything to be in databases. Along those lines I would also take reasons why it should all be in databases.

I should mention we have in place already automated backups that run for all of our customers databases and any files that are generated by our program that are required to be saved over a period of time These are optional but if someone isn't using our system it is expected that they are using their own or data loss isn't our problem (it is if our system fails and they are using it!).

Thanks

Edit

Some people have suggested using 2008 R2 as a possible fix for this. It's a good idea I'm just going to have to look at issues involving legacy programs that use these databases. Not all of them use Stored Procedures and some aren't very maintenance friendly.

Improve this question
7
  • If the owner "wants everything in databases", then he should be willing to, you know, pay for databases that will fit everything.
    – mfinni
    Jan 12, 2011 at 17:05
  • Update to 2008 R2 Express. Limit is 10gb. Use FILESTREAM to store the binary cokluumns - size does nto count against limit. Finished, no recoding necessary.
    – TomTom
    Jan 12, 2011 at 17:15
  • @mfinni these are customers local databases, not company owned. @TomTom That may not be an option.
    – msarchet
    Jan 12, 2011 at 17:52
  • 1
    That doesn't explain it any better. If you're selling a product/service and your boss wants it to use a database, then you need to use a DB that's properly suited for it.
    – mfinni
    Jan 12, 2011 at 17:54
  • 1
    If the client buys a sports car and you give them a Reliant K, who's fault is that? And if you charged them only for a Reliant K but promised it could do 0-60 in 7 seconds flat, who's on the hook for that? Talk to your boss.
    – mfinni
    Jan 12, 2011 at 18:19

3 Answers 3

Reset to default
2

If you're going to break up the directory structure like that (and you should), you shouldn't use the method you propose, which will be prone to clustering. You should implement the same concept but based on a hashed value of the file name.

For instance, instead of what you propose, make a hash of the file name (I'll use md5), then created the subdirs based on the hash value:

Your proposal:
FILE=1999, H1=1, H2=999, FILEPATH=1\999\1999
FILE=1998, H1=1, H2=998, FILEPATH=1\998\1998
FILE=1997, H1=1, H2=997, FILEPATH=1\997\1997

Hashed solution:
FILE=1999, MD5=2554fe5cd0a1b3fb7f9ec112fd326744, H1=2, H2=54, FILEPATH=2\54\1999
FILE=1998, MD5=82ec15656dd2b8a3e50ff36643a713ad, H1=8, H2=2e, FILEPATH=8\2e\1998
FILE=1997, MD5=9cc2e1e538bd538014d294138a85e20b, H1=9, H2=cc, FILEPATH=9\cc\1997

The advantage to doing this is that it spreads the utilization of the folders more evenly, allowing you to do things like spread the folders across drives for performance, etc.

It's likely that your DB has hashing functions built in which would allow you to calculated the path on the fly, you you could also easily calculate it once in code and save the whole path.

My example of MD5 may not be the standard, (I think SHA1 is more common), but it was just to get a working example out there.

Improve this answer
2
  • this is interesting and makes sense get's a more chaotic distribution rather.
    – msarchet
    Jan 12, 2011 at 17:53
  • I'm picking this as the correct answer as it answers my question the closest however the ideal answer would be a combination of this and the R2 answers.
    – msarchet
    Jan 12, 2011 at 19:18
1

The main reason to put all things (mainly images and files) in db is for protection issue. For protection I mean, in this case, access control in order to protect copywrited stuff. In this way you could control who and when has seen/access a particular resource and track it and by a login procedure you can also decide who has the right to access to those assets.

Improve this answer
11
  • If you never make the filesystems available to the users, that takes care of the problem too. If they can only get to the assets through the application front-end, then it doesn't matter if the assets are stored in the DB or the filesystem.
    – mfinni
    Jan 12, 2011 at 17:04
  • @mfinni it's quite sure, but not absolutely (if we can use this term) sure. There's an off-chance to guess or to know in other way the asset's URI and so access it. For copywrited stuff, it's a possibility to keep in mind
    – Nicola Boccardi
    Jan 12, 2011 at 17:42
  • True, it does depend on the architecture of the application. A multi-tier architecture is what I'm used to, where the only thing that can connect to the backend in any way is the app server.
    – mfinni
    Jan 12, 2011 at 17:44
  • In my imagined scenario, images and/or files to protect could be access also in front-end app. So I can't imagine how a multi-tier arch could resolve this prob. If you know, please tell me
    – Nicola Boccardi
    Jan 12, 2011 at 17:50
  • 1
    We use an application from IBM called CMOD, so that's me example. It has a web-based front-end. The users only connect to the web tier. They cannot connect to the backend, where the assets are. They can only come in through the application, which has its own access controls. Thus, users can only see what they're allowed to see in the application.
    – mfinni
    Jan 12, 2011 at 17:57
0

Why do you reach the amximum db size (of 10gb) in R2?

  • Create an ew file group, use FILE SYSTEM STORE for storing the data.
  • Voila, finished.

BLobs stored in the file system do NOT COUNT AGAINST THE SIZE LIMIT.

More info on this really nice feature:

http://technet.microsoft.com/en-us/library/bb933993.aspx

Improve this answer
1
  • we aren't using 2008 R2
    – msarchet
    Jan 12, 2011 at 17:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .