locked
Using a database server for another role RRS feed

  • Question

  • Hi,

    The company I work for has a requirement to deliver a file storage area for files that can be uploaded on our web based system.  The only suitable server we have for this is our database server.

    However, I've been reading that storing files in the db is not recommended (we are using SQL 2005) therefore I'm looking into a file server solution.  Is there any security reason for not using the database server to also be a file server?  Obviously we'd isolate the responsibilites as much as possible and not allow access to the db files through any shares we open up.

    I've suggested we buy dedicated hardware for sharing but the management seem to be against this for cost reasons.

    Any advice would be appricated.
    Monday, April 27, 2009 8:13 AM

All replies

  • Hello,

     Storing them in a database isn't too hard either, but you need to be aware of a few things. First of all, you need to know what data type to use to store your files. With SQL Server 2000, you can use the image data type. Although its name suggests you can only use it to store image files, this is not true. You can store text files, Word documents, spread sheets or any other file type you have. With SQL Server 2005, you can use the new varbinary(max) data type. The sample application that comes with this article uses a SQL Server 2005 Express database and thus the varbinary(max) data type. If you want to use SQL Server 2000 instead, simply replace each occurrence of varbinary(max)with the image data type, including the FileData column in the Files table.

    Additionally, you need to know the code to access the database to store and retrieve the files

    Thanks,
    Shanthi

    Monday, April 27, 2009 8:30 AM
  • Hi,

    Thanks for the prompt response.  I've worked with a similar approach to what you've suggested in the past, but I've read that performance is better storing on the file system.

    Also with the system I last used this approach on backups became painful as we had to backup all the data in the database including the files in the image column (was SQL 2000).  With a file server we could just backup the file as required, I see the two types of data having different policies around backup etc.

    The issue isn't so much the code, it's the management, performance and scalability that's the issue.  Hence the need for a file server.
    Monday, April 27, 2009 8:38 AM
  • Yet another good reason to convince the business to switch to SQL Server 2008 so you can take advantage of FILESTREAM   :)
    Monday, April 27, 2009 9:03 AM