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
3  1827 
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****@sommarsk og.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
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.
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****@sommarsk og.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Carolyn Longfoot |
last post by:
Help! This is driving me crazy...
I'm trying to read a BLOB from a db and display it in a browser, like
so:
$query="SELECT blob from table where blob_id=9";
$result=mysql_query($query);
$blob=mysql_fetch_assoc($result);
Now when I do
print_r (array_values($blob));
|
by: Ed |
last post by:
I've search around endlessly for a solution to this problem but have
not found anything yet.
I'm using Crystal 9 with .NET. I am not using a DSR to create the
report. Instead I am creating a report at run-time, using a .ttx file
and using a DataSet as the source.
My problem is that I want to dynamically change an image (user's pic)
on the report based on the user parameter.
|
by: sime |
last post by:
Hi,
I have a blob field in a mysql database table. I want to copy a blob
from one record to another. I am having trouble transferring the data
via a php variable. Maybe I need to addslashes or convert to Hex or
something. I've tried a few things but can't quite get it. Here is
simplified code.
mysql_select_db($dbname, $connection);
|
by: hamvil79 |
last post by:
I'm implementig a java web application using MySQL as database.
The main function of the application is basically to redistribuite
documents. Those documents (PDF, DOC with an average size around 2Mb)
are stored in BLOB column.
The amount of documents for the first year should not exceed 5/6 Giga,
but I cannot make prevision for the next years.
Those documents are mainly just accessed (update and delete are not so
|
by: Chris Fink |
last post by:
I have setup an Oracle table which contains a blob field. How do I insert
data into this field using C# and ADO.net?
| | |
by: Markusek Peter |
last post by:
Hi,
I'm using MySQLDriverCS. I've got no problem to store BLOB into database,
but I can't get it back(save to file).
Problem is with DataTable(returns string:( )
My code:
--
DataTable dt = new MySQLSelectCommand(...; //select that row and column
where is BLOB
string dest = Server.MapPath("image.jpg");
FileStream binFile = new
|
by: Chucker |
last post by:
Hi Community,
I think I can store Binary Data in SQL Server but when I try to retrieve it,
I always only get one byte.
I think I stored my Binary Data in SQL Server in a Colum of Type Image. At
least when I execute the following code, I get some significant network
traffic. When I check the database with query analyzer, I see 4 Hex Chars in
the image colum. Like 0xe0 etc.
|
by: msc.batra |
last post by:
I want to load multiple rows of BLOB and CLOB into DB2 using CLI. Which
are the possible ways to do it? Any help...
|
by: Cerebrus |
last post by:
Hi all,
Using : .NET 1.1, SQL Server 2000, Win 2K.
Could anyone confirm if there is a bug in the SqlDataReader.GetChars()
method when using CommandBehavior.SequentialAccess to get a BLOB
string from the database ? I have searched all over, and found many
threads with the same problem as I, but no word from the MS folks that
this is a bug. This problem only seems to occur when using GetChars,
not with GetBytes. But since it is a ntext...
|
by: matt |
last post by:
hello,
im doing my first ASP.NET app that inserts & retrieves files from
Oracle (no need for a discussion on *that*!).
i learned first-hand of the somewhat awkward technique for inserting
binary data into an Oracle BLOB column via ADO.NET. since my files are
larger than 33k, it seemed had to use this technique:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322796
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
