Issues with Image data

Hello.
We're running SQL Server 2000.
I am considering creating Image columns for one of our databases. Currently
we have a varchar field that holds the path to the actual image which is
stored on an NTFS file system. The average image size is less than 256K.
What issues arise when considering Backup/Restore and Database recovery.
Any other insights are welcome.
Thanks in advance,
Mike
"Mike Lopez" <MichaelLopez@.inds.com> wrote in message
news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hello.
> We're running SQL Server 2000.
> I am considering creating Image columns for one of our databases.
> Currently we have a varchar field that holds the path to the actual image
> which is stored on an NTFS file system. The average image size is less
> than 256K.
> What issues arise when considering Backup/Restore and Database recovery.
> Any other insights are welcome.
> Thanks in advance,
> Mike
>
>
There are all kinds of performance issues to think about.
1. Someone may correct me on this one, but.. Each image will store a 16
byte pointer in the row in your regular base table. That pointer will then
point to a private 8k data page where your image will be stored. That means
that each image will take up at least 8k of database storage space and in
your case, a chain of 32 8k data pages that will need to be read from and
written to. That is a killer on disk i/o for SQL Server.
2. Having those images in the same table *could* really slow down the table
on your joins. You may wish to place the images in a 1 to 1 relationship
in a separate table.
2a. If you place those images in a separate table, you *could* put that one
table on it's own filegroup and then perform filegroup backups and restores.
This may improve your backup and recovery strategy especially if the images
don't change often. (eg. You would back up the images filegroup far less.
Restores would be quicker as you only need to do the affected filegroups and
TLogs.)
3. If you use the images frequently (reads/writes) put them on the fastest
read/write drives as possible. I would suggest a RAID 1 (striped - no
parity) and mirror that drive (RAID 0) if you need that type of failsafe.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Thank you Rick. Yes, that helps.
I'm leaning towards keeping the current system intact (image file in a
directory, image file's path in table column). I feel there's too much
overhead involved. I also don't like the idea that the image would not be
directly viewable by third-party image viewers.
Thanks again,
Mike
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eoFr3FO7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Mike Lopez" <MichaelLopez@.inds.com> wrote in message
> news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
>
> There are all kinds of performance issues to think about.
> 1. Someone may correct me on this one, but.. Each image will store a 16
> byte pointer in the row in your regular base table. That pointer will
> then point to a private 8k data page where your image will be stored.
> That means that each image will take up at least 8k of database storage
> space and in your case, a chain of 32 8k data pages that will need to be
> read from and written to. That is a killer on disk i/o for SQL Server.
> 2. Having those images in the same table *could* really slow down the
> table on your joins. You may wish to place the images in a 1 to 1
> relationship in a separate table.
> 2a. If you place those images in a separate table, you *could* put that
> one table on it's own filegroup and then perform filegroup backups and
> restores. This may improve your backup and recovery strategy especially if
> the images don't change often. (eg. You would back up the images
> filegroup far less. Restores would be quicker as you only need to do the
> affected filegroups and TLogs.)
> 3. If you use the images frequently (reads/writes) put them on the
> fastest read/write drives as possible. I would suggest a RAID 1
> (striped - no parity) and mirror that drive (RAID 0) if you need that type
> of failsafe.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>