Storing Tiff image in SQL Server.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arne Garvander - 31 Mar 2008 17:15 GMT

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.

Signature

Arne Garvander
Certified Geek
Professional Data Dude

Reply to this Message

Arne Garvander - 31 Mar 2008 17:24 GMT

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

Signature

Arne Garvander
Certified Geek
Professional Data Dude

> I am using SQL Server 2005.
> I have the option to store a Tiff image inside SQL Server or In the file
[quoted text clipped - 3 lines]
> One of our concerns is convenience of retrieving the file and backing up the
> file.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 31 Mar 2008 17:36 GMT

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

http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-th
e-filesystem.html

Reply to this Message

Rob - 18 Jul 2008 14:39 GMT

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

> > My questions is it best to store the image inside SQL Server or in the
> > file
> > system.
>
> http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-th
e-filesystem.html

Reply to this Message

Rubén Garrigós - 31 Mar 2008 17:39 GMT

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).

Signature

Rubén Garrigós
Solid Quality Mentors

>I am using SQL Server 2005.
> I have the option to store a Tiff image inside SQL Server or In the file
[quoted text clipped - 4 lines]
> the
> file.

Reply to this Message

Arne Garvander - 31 Mar 2008 17:49 GMT

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

Signature

Arne Garvander
Certified Geek
Professional Data Dude

> 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
[quoted text clipped - 21 lines]
> > the
> > file.

Reply to this Message

Andrew J. Kelly - 31 Mar 2008 18:08 GMT

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.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Each file would be 20-40 K. They will not change once created. I wonder
> how
[quoted text clipped - 31 lines]
>> > the
>> > file.

Reply to this Message

Rob - 18 Jul 2008 14:40 GMT

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

> 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
[quoted text clipped - 39 lines]
> >> > the
> >> > file.

Reply to this Message