I am using SQL Server 2005.
I have the option to store a Tiff image inside SQL Server or In the file
system.
I have 15,000 images per day. I have the option of storing them in PCL
format orTIff format.
One of our concerns is convenience of retrieving the file and backing up the
file.

--
Arne Garvander
Certified Geek
Professional Data Dude

My questions is it best to store the image inside SQL Server or in the file
system.

--
Arne Garvander
Certified Geek
Professional Data Dude


"Arne Garvander" wrote:

> I am using SQL Server 2005.
> I have the option to store a Tiff image inside SQL Server or In the file
> system.
> I have 15,000 images per day. I have the option of storing them in PCL
> format orTIff format.
> One of our concerns is convenience of retrieving the file and backing up the
> file.
>
> --
> Arne Garvander
> Certified Geek
> Professional Data Dude

> My questions is it best to store the image inside SQL Server or in the
> file
> system.

http://databases.aspfaq.com/database...ilesystem.html

You have to think not only in storing the files inside or outside SQL
Server.Maybe the most important thing is what are you going to do with the
files when they are stored. This is the MOST important thing. You have to
know your data and its use.

SQL Server is very good searching for data and resolving queries If you
are going to work a lot with metadata of the images (date, exposure, size,
and so on) it will worth to have these in the database. You have to think
about the retention period of the data or if you will use more frequently
some images or not. The performance for streaming files from SQL Server is
not as good as NTFS (this is one reason why we have the new FileStream
datatype in SQL Server 2008 )

It is a big and difficult decision. In our experience SQL Server (prior
2008) is better for small files (<1MB) or for files that have metadata to
query (great performance vs NTFS).

--
Rubén Garrigós
Solid Quality Mentors

"Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
news:9B426916-74F0-49D2-A4C5-25651C892ED7@microsoft.com...
>I am using SQL Server 2005.
> I have the option to store a Tiff image inside SQL Server or In the file
> system.
> I have 15,000 images per day. I have the option of storing them in PCL
> format orTIff format.
> One of our concerns is convenience of retrieving the file and backing up
> the
> file.
>
> --
> Arne Garvander
> Certified Geek
> Professional Data Dude

Each file would be 20-40 K. They will not change once created. I wonder how
the design would impact backups.

--
Arne Garvander
Certified Geek
Professional Data Dude


"Rubén Garrigós" wrote:

> You have to think not only in storing the files inside or outside SQL
> Server.Maybe the most important thing is what are you going to do with the
> files when they are stored. This is the MOST important thing. You have to
> know your data and its use.
>
> SQL Server is very good searching for data and resolving queries If you
> are going to work a lot with metadata of the images (date, exposure, size,
> and so on) it will worth to have these in the database. You have to think
> about the retention period of the data or if you will use more frequently
> some images or not. The performance for streaming files from SQL Server is
> not as good as NTFS (this is one reason why we have the new FileStream
> datatype in SQL Server 2008 )
>
> It is a big and difficult decision. In our experience SQL Server (prior
> 2008) is better for small files (<1MB) or for files that have metadata to
> query (great performance vs NTFS).
>
> --
> Rubén Garrigós
> Solid Quality Mentors
>
> "Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
> news:9B426916-74F0-49D2-A4C5-25651C892ED7@microsoft.com...
> >I am using SQL Server 2005.
> > I have the option to store a Tiff image inside SQL Server or In the file
> > system.
> > I have 15,000 images per day. I have the option of storing them in PCL
> > format orTIff format.
> > One of our concerns is convenience of retrieving the file and backing up
> > the
> > file.
> >
> > --
> > Arne Garvander
> > Certified Geek
> > Professional Data Dude
>
>
>

You would have to backup the Tifs either way if you use SQL2005. Having them
in the db is easier to manage from a backup standpoint since it is all in
the db. If you store them out of the db you have to backup the files as a
separate operation and you have to worry about integrity if the backups are
not synced. In 2008 even though the files are on the file system with
Filestream datatype you will still get an integrated backup directly thru
SQL Server.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
news:73BA04A4-67B1-48C9-A271-8A57B7CCAD5C@microsoft.com...
> Each file would be 20-40 K. They will not change once created. I wonder
> how
> the design would impact backups.
>
> --
> Arne Garvander
> Certified Geek
> Professional Data Dude
>
>
> "Rubén Garrigós" wrote:
>
>> You have to think not only in storing the files inside or outside SQL
>> Server.Maybe the most important thing is what are you going to do with
>> the
>> files when they are stored. This is the MOST important thing. You have to
>> know your data and its use.
>>
>> SQL Server is very good searching for data and resolving queries If
>> you
>> are going to work a lot with metadata of the images (date, exposure,
>> size,
>> and so on) it will worth to have these in the database. You have to think
>> about the retention period of the data or if you will use more frequently
>> some images or not. The performance for streaming files from SQL Server
>> is
>> not as good as NTFS (this is one reason why we have the new FileStream
>> datatype in SQL Server 2008 )
>>
>> It is a big and difficult decision. In our experience SQL Server (prior
>> 2008) is better for small files (<1MB) or for files that have metadata to
>> query (great performance vs NTFS).
>>
>> --
>> Rubén Garrigós
>> Solid Quality Mentors
>>
>> "Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in
>> message
>> news:9B426916-74F0-49D2-A4C5-25651C892ED7@microsoft.com...
>> >I am using SQL Server 2005.
>> > I have the option to store a Tiff image inside SQL Server or In the
>> > file
>> > system.
>> > I have 15,000 images per day. I have the option of storing them in PCL
>> > format orTIff format.
>> > One of our concerns is convenience of retrieving the file and backing
>> > up
>> > the
>> > file.
>> >
>> > --
>> > Arne Garvander
>> > Certified Geek
>> > Professional Data Dude
>>
>>
>>

Aaron,

I have a similar problem. I use SQL 2005 as the back end for an Access
application. I have set up an image field in SQL which translates to an OLE
Object field in Access (exactely what I want). I have placed an unbound
object control on a form that uses the OLE object field from the table. When
I right-click and insert object I have a problem. I can choose to create
from file, browse and select the file. I am also choosing to link to save
space. When I click on okay Access hangs and I have to stop the task. If I
set up a new table in Access with an OLE Object field and use it there are
not problems - only with SQL. Any suggestions?

Rob


"Aaron Bertrand [SQL Server MVP]" wrote:

> > My questions is it best to store the image inside SQL Server or in the
> > file
> > system.
>
> http://databases.aspfaq.com/database...ilesystem.html
>

Andrew,

I have a similar problem. I use SQL 2005 as the back end for an Access
application. I have set up an image field in SQL which translates to an OLE
Object field in Access (exactely what I want). I have placed an unbound
object control on a form that uses the OLE object field from the table. When
I right-click and insert object I have a problem. I can choose to create
from file, browse and select the file. I am also choosing to link to save
space. When I click on okay Access hangs and I have to stop the task. If I
set up a new table in Access with an OLE Object field and use it there are
not problems - only with SQL. Any suggestions?

Rob


"Andrew J. Kelly" wrote:

> You would have to backup the Tifs either way if you use SQL2005. Having them
> in the db is easier to manage from a backup standpoint since it is all in
> the db. If you store them out of the db you have to backup the files as a
> separate operation and you have to worry about integrity if the backups are
> not synced. In 2008 even though the files are on the file system with
> Filestream datatype you will still get an integrated backup directly thru
> SQL Server.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
> news:73BA04A4-67B1-48C9-A271-8A57B7CCAD5C@microsoft.com...
> > Each file would be 20-40 K. They will not change once created. I wonder
> > how
> > the design would impact backups.
> >
> > --
> > Arne Garvander
> > Certified Geek
> > Professional Data Dude
> >
> >
> > "Rubén Garrigós" wrote:
> >
> >> You have to think not only in storing the files inside or outside SQL
> >> Server.Maybe the most important thing is what are you going to do with
> >> the
> >> files when they are stored. This is the MOST important thing. You have to
> >> know your data and its use.
> >>
> >> SQL Server is very good searching for data and resolving queries If
> >> you
> >> are going to work a lot with metadata of the images (date, exposure,
> >> size,
> >> and so on) it will worth to have these in the database. You have to think
> >> about the retention period of the data or if you will use more frequently
> >> some images or not. The performance for streaming files from SQL Server
> >> is
> >> not as good as NTFS (this is one reason why we have the new FileStream
> >> datatype in SQL Server 2008 )
> >>
> >> It is a big and difficult decision. In our experience SQL Server (prior
> >> 2008) is better for small files (<1MB) or for files that have metadata to
> >> query (great performance vs NTFS).
> >>
> >> --
> >> Rubén Garrigós
> >> Solid Quality Mentors
> >>
> >> "Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in
> >> message
> >> news:9B426916-74F0-49D2-A4C5-25651C892ED7@microsoft.com...
> >> >I am using SQL Server 2005.
> >> > I have the option to store a Tiff image inside SQL Server or In the
> >> > file
> >> > system.
> >> > I have 15,000 images per day. I have the option of storing them in PCL
> >> > format orTIff format.
> >> > One of our concerns is convenience of retrieving the file and backing
> >> > up
> >> > the
> >> > file.
> >> >
> >> > --
> >> > Arne Garvander
> >> > Certified Geek
> >> > Professional Data Dude
> >>
> >>
> >>
>
>