Hello,

I must save up to 20GB of Image and Pdf's in a sqlserver db.

Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?

Are there a performance problem if the BLOB table ist in the same as
the other data?

Robert

Same DB.

If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup.  But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).

Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).

Show quoteHide quote

In addition, try to describe the contents about the LOB as much as possible
with other data.  I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table.  This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.

Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes.  The space reclaimation is lousy.  Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database.  It is just
convenient, high priced, storage and retreival.  It should be segmented away
anyway.

Sincerely,


Anthony Thomas


If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup.  But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).

Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).

The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.

I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.

Show quoteHide quote

sql data corruption
how to resolve deadlock issues
Differential and Transaction backup restore.
SQL brain twister
Maintanence Plans for Backups
Problem with different version of SQL Server
validate data of target database against source database structure..How?
Restore needs too much space
List all the logins that have a certain privileges
Error 21776 [SQL DMO]