BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
TIA,
ChrisR
Chris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
http://www.microsoft.com/technet/pro...rt3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR
|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR