June 25, 2009 at 2:45 pm
Hi all,
I'm looking for a suggestion and I don't necessarily want to use the new FILESTREAM option unless it makes sense.
Here's the situation:
We have an intake document that we have our developers and business users fill out for new servers, instances, and databases (contact info, expected growth, etc), and we use this information on the initial setup and configuration of their applications. We maintain a relational DB with server names, DB names, sizes, etc, and want to find a way to store the document that they fill out and link it back to a specific DB.
Without putting a link to a file share, we want to simply store the docs within SQL Server somehow, and be able to pull them up when needed.
I tried searching online for storing word documents in a filestream column but couldn't find anything that related back to my situation. Many of the examples only have "insert into table1(filestream_col) values (convert(varbinary(max)), 'This is my filestream data')" which doesn't really related back to how that would get stored in there.
Looking forward to your opinions on trying to get this implemented. I've never worked with BLOB data so I'm open to all options.
Thanks in advance!
Steve
June 25, 2009 at 3:51 pm
I tried searching online for storing word documents in a filestream column but couldn't find anything that related back to my situation. Many of the examples only have "insert into table1(filestream_col) values (convert(varbinary(max)), 'This is my filestream data')" which doesn't really related back to how that would get stored in there.
In .NET there is a technical reason for storing the files in Varbinary(max) because most Blob files are passed to MemoryStream one of the child classes of the TextWriter abstract class in system.IO and the bytes gets used at the end of the stream.
To store document either Word file in Varbinary (max) depending on the size of the file and Text of the same document so you could use fulltext to search the content of the file, it takes extra space but improve performance.
Kind regards,
Gift Peddie
June 25, 2009 at 4:40 pm
In front-end it doesn't matter if your data are stored as FILESTREAM or as usual VARBINARY(MAX) data directly in database. I just saw a Microsoft webcast which said rule of thumb is:
* If your data are usually less than 1 MB use VARBINARY(MAX)
* Otherwise use FILESTREAM
... but I did not yet investigate this deep enough to confirm or disagree with.
Generally it should be no problem to store your documents directly in database.
Depending on the count of documents and how you work with you probably should consider about compressing the files. If you just store the documents in your database most time this saves a lot of disk space. (De)compression is a build in feature in most frameworks like .NET and it works really fast.
I would store the original documents instead of the plain text only but an additional column which stores the text, as Gift Peddie suggested, should be a really nice feature to enable content searach!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply