Using BLOB

[KR]

Hi,

One of our third-party software vendors is planning to implement BLOB
in their database for storing certain documents. We are not too
thrilled about it, since it can be a drain on our resources, but I
would like to get the expert opinion out there on the pros and cons of
implementing this.

Also, the database is in Full recovery mode and we back up the
transaction log every 15 minutes. We also do a process similar to log
shipping. We have two servers to which these transaction logs are
restored to periodically. What will the impact on the transaction logs
due to changes to the BLOB fields.

If you could also point me to any resources that talks in detail about
performance, backup and recovery in relation to BLOB that would be
great.

Thanks in advance

KR

[Erland Sommarskog]

KR (kr****@bastyr.edu) writes:

One of our third-party software vendors is planning to implement BLOB
in their database for storing certain documents. We are not too
thrilled about it, since it can be a drain on our resources, but I
would like to get the expert opinion out there on the pros and cons of
implementing this.
The first question I need to ask: is there a requirement to store
these documents at all?

Asssuming that the answer is in the affirmative, there are two options:
1) Store it in the database.
2) Store it on disk, and only save the file path in the database.

The latter is usually easier to implement in the application, and
takes less toll in the database.

However, when you think of it, the first solution is much easier to
manage. What if there is a crash and you need to restore? If everything
is in the database, you know that what you restore is transactionally
consistent. If you need to restore database + file system, get a
consistent restore is much more difficult.

Another aspect is that file system is more prone to accidents, like
people deleting files by mistake.
Also, the database is in Full recovery mode and we back up the
transaction log every 15 minutes. We also do a process similar to log
shipping. We have two servers to which these transaction logs are
restored to periodically. What will the impact on the transaction logs
due to changes to the BLOB fields.

And if you store the documents in the file system, how do you do
log shipping?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[KR]

Where we store these documents are not under our control - the
application is designed by a third-party. So we have to go with their
decision - what we are trying to do is to analyze any problems that
may present due to this and devise a solution or go to the software
vendor with recommendations.

The documents in the file system are backed up separatley from the
database. We cannot do Log shipping on our databases since we have SQL
Standard edition. We backup our transaction logs periodically to a
network 'store' and restore the transaction logs periodically on the
two standby server using a stored procedure.

What we are also concerned here is the possible effect on the size of
transaction log due to updates and inserts on these BLOB fields, the
backup and restore time.

[Erland Sommarskog]

KR (kr****@bastyr.edu) writes:

Where we store these documents are not under our control - the
application is designed by a third-party. So we have to go with their
decision - what we are trying to do is to analyze any problems that
may present due to this and devise a solution or go to the software
vendor with recommendations.

The documents in the file system are backed up separatley from the
database. We cannot do Log shipping on our databases since we have SQL
Standard edition. We backup our transaction logs periodically to a
network 'store' and restore the transaction logs periodically on the
two standby server using a stored procedure.

What we are also concerned here is the possible effect on the size of
transaction log due to updates and inserts on these BLOB fields, the
backup and restore time.

It goes without saying that the more data you store in the database,
the longer time the backup will take, and the more size is needed for
the transaction log.

I don't have any information about BLOBs and backups per se, but I find
it difficult to believe that a 1MB blob would take any significant more
toll on backup and log than 10000 rows of 100 bytes.

What you should do, is to find an estimate on many documents there
will be in the database, and how many that will be inserted, updated
and deleted per day. And of course, the expected average size of the
documents.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx