About to put 1.5 GB of images into a DB

I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new serve
r
- just moved from SQL 2000.
We want to load 10000 images into it - with a raw JPG size of about 1.5 GB's
on disk. In the past we were storing the file path to these images - but no
w
want them in the DB.
So that's doubling the size of the DB - that does not scare me or scare the
DBA in charge of things.
But it's making us think about using a different FILEGROUP for this new
StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX) datatype
column in this new table.
Opinions?
Experiences'
Thanks!The main benefit of another filegroup is the ability to put that filegroup
onto a different physical disk array than the one that the rest of the data
is sitting on, should I/O contention become an issue. It certainly wouldn't
hurt to prepare for this upfront by going ahead with your plan, but it
probably won't help a whole lot either, unless you have more disks ready to
go...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:6FFC5611-5A7A-43D6-9C6F-24BC22783FDC@.microsoft.com...
> I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new
> server
> - just moved from SQL 2000.
> We want to load 10000 images into it - with a raw JPG size of about 1.5
> GB's
> on disk. In the past we were storing the file path to these images - but
> now
> want them in the DB.
> So that's doubling the size of the DB - that does not scare me or scare
> the
> DBA in charge of things.
> But it's making us think about using a different FILEGROUP for this new
> StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX)
> datatype
> column in this new table.
> Opinions?
> Experiences'
> Thanks!|||On Aug 7, 4:50 pm, Steve Z <Ste...@.discussions.microsoft.com> wrote:
> I've got a DB that's about 1.5 GB's right now. It's in SQL 2005 - new ser
ver
> - just moved from SQL 2000.
> We want to load 10000 images into it - with a raw JPG size of about 1.5 GB
's
> on disk. In the past we were storing the file path to these images - but
now
> want them in the DB.
> So that's doubling the size of the DB - that does not scare me or scare th
e
> DBA in charge of things.
> But it's making us think about using a different FILEGROUP for this new
> StuPhoto_T table. btw - the images are stored in a VARBINARY(MAX) datatyp
e
> column in this new table.
> Opinions?
> Experiences'
> Thanks!
Steve-
I was faced with a similar issue, and although we opted to not store
the images in the DB, just pointers to the filesystem, we also thought
of using a seperate filegroup. Our rational, was that our "data" was
going to account for only 10% of the size of the database, with the
rest being images. In case of disaster recovery, the users wanted the
Data portion back ASAP, even if that meant they cound not update/
delete/insert new images. We were looking into being able to backing
up and restoring the file groups seperately, so that we could meet
that requirement.
That being said, I much happier having a million plus files sitting on
the OS, and a database size of 60GB, rather then having a database
size of 600-700 GB.|||Thanks for the info! We obviously aren't nearly as large as the figures you
just stated!
The DBA has managed to shrink the images quite a lot - it appears that the
original 1.5 GB is down to around 150-200 MB - making the whole issue be a
lot less of a concern.
I'm still interested in hearing other experiences!
Thanks!
"walsham" wrote:

> I was faced with a similar issue, and although we opted to not store
> the images in the DB, just pointers to the filesystem, we also thought
> of using a seperate filegroup. Our rational, was that our "data" was
> going to account for only 10% of the size of the database, with the
> rest being images. In case of disaster recovery, the users wanted the
> Data portion back ASAP, even if that meant they cound not update/
> delete/insert new images. We were looking into being able to backing
> up and restoring the file groups seperately, so that we could meet
> that requirement.
> That being said, I much happier having a million plus files sitting on
> the OS, and a database size of 60GB, rather then having a database
> size of 600-700 GB.
>