Storing documents in SQL SERVER 2000 for ASP.Net application

[Techeek]

I need to create an application in ASP.Net that will allow users to upload
files. Then the files are to be stored in a SQL Server table.

I have been reading up on the advantages/disadvantages of this and it seems
like the best thing to do is leave the files in the file system and only
store the file path in the table. I also read that binary files like Word
documents, Excel spreadsheets, etc. can be stored in varbinary or image type
columns in the SQL SERVER database.


I would like to know the standard and secure way of storing documents in SQL
SERVER 2000.

Please help.

 

[Marc Gravell]

Well, if you want to store (binary) documents *inside* SQL Server
2000, then your only option is "image". SQL Server 2005 introduces
varbinary(max) which can function in a very similar manner, and 2008
introduces file-streams.

Yes, there are pros and cons of documents inside the database. For my
document management system, I just keep a relative* path in the
database, with the files outside; this allows me to map them directly
into a virtual folder in IIS. However, you would probably need to use
a bespoke handler/module to enforce access control (or perhaps edit
the file ACLs through code; but this is possibly harder?).

Note that it is harder to get a single backup of the data+files if
they are segregated like this...

Marc

(*=relative to the root of my docstore tree, so I can easily move the
physical files without having to update the database)