Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 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 '11 at 17:15
    
@mfinni these are customers local databases, not company owned. @TomTom That may not be an option. –  msarchet Jan 12 '11 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 '11 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 '11 at 18:19

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
this is interesting and makes sense get's a more chaotic distribution rather. –  msarchet Jan 12 '11 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 '11 at 19:18

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.

share|improve this answer
    
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 '11 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 '11 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 '11 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 '11 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 '11 at 17:57

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

share|improve this answer
    
we aren't using 2008 R2 –  msarchet Jan 12 '11 at 17:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.