Images in SQL database or filesystem

We are wondering which storage type to choose. The number of images will be
about 30 million and the size of one image will be about 30kb.
What are the advantages and divantages of the two storage areas?
Is there anyone who can help me with his expertise in this area?
Please no answers like: On my filesystem everythings works great.I prefer to store images on the file system, but I know that there are
others that don't share my view. In any case there are advantages and
divantages for each method.
The advantages of storing the BLOB in the database are:
1) You don't need to worry about synchronizing the data in the DB and
the images.
2) When you backup the database you already backup the images.
3) You can manage permissions in the database only.
The divantages of storing the files in the database are:
1) Backup and restore time for the database will increase
significantly.
2) Writing the code to handle the images will be harder.
3) When you work with image data type you'll might have performance
problem (comparing to storing the images on the file system). I have
to admit that I only read it and never tested it.
Adi|||Hi
You will probably get a few replies saying "Put it on the File System".
The issue with that is:
1. How do you keep the DB and FS in sync so during a restore, you don't
loose links? With difficulty.
2. All file systems start have performance problems if you have too many
files in one directory. 30 Million files does result in a very complicated
structure to keep the number of files at a manageable level (10'000 or less,
mileage may vary)
3. You have permissioning issues. If you want to read/write to the files,
the people need read/write access to the files. Files can get modified and
deleted very easily. You control the DB, and once written, you can stop
modifications.
I am in favor of DB storage.
Regards
Mike
"morerice" wrote:

> We are wondering which storage type to choose. The number of images will b
e
> about 30 million and the size of one image will be about 30kb.
> What are the advantages and divantages of the two storage areas?
> Is there anyone who can help me with his expertise in this area?
> Please no answers like: On my filesystem everythings works great.
>|||My rule of thumb (but rule are meant to be broken) is that if you have a
large number of small files, it is better to store them in the database
because of synchronization issues and file system problems managing a large
number of files.
In your case though, you say that you have them in a fileSystem and
everything works great.
I don't know how things will evolve in you situation (file modifications, 40
million files next year, etc.), but unless you have a very good reason to
change a good working system... don't.
"morerice" <morerice@.discussions.microsoft.com> wrote in message
news:FB2F0C09-5E26-4996-8022-CFBC57D3960B@.microsoft.com...
> We are wondering which storage type to choose. The number of images will
be
> about 30 million and the size of one image will be about 30kb.
> What are the advantages and divantages of the two storage areas?
> Is there anyone who can help me with his expertise in this area?
> Please no answers like: On my filesystem everythings works great.
>|||Morice,
It sounds like you will have a headache coming one way or the other. My
experience working with blobs is that they stink. Database size tend grow ou
t
of control, online backup start to become expensive if you pay for storage
like we do and maintenance on the database get longer and longer. Here's a
link to an article that describe a problem you will encounter one day.
http://support.microsoft.com/?id=324432
My point is this: DO YOUR HOMEWORK and communicate, communicate communicate
before you embark on putting the images in the database.
Joseph
"morerice" wrote:

> We are wondering which storage type to choose. The number of images will b
e
> about 30 million and the size of one image will be about 30kb.
> What are the advantages and divantages of the two storage areas?
> Is there anyone who can help me with his expertise in this area?
> Please no answers like: On my filesystem everythings works great.
>

Image Server Database

I am a semi-experienced DBA, but have some questions regarding the
storage of images inside of SQL Server (which I have never done before).
I am researching to pros and cons of storing images inside of SQL
Server verses a file server and DB combination (storing the file paths
in SQL). There is a push from my management to store all the images
within a very large database, but something is telling me that it's not
the best way to do it. Unfortunately, I don't have any concrete
experience/evidence to push in one direction verses another.
We are talking about the storage/cataloging of around 4 million images.
Our current image server is file-based and is around 1 tera in size.
The idea of moving all this into a large SQL Server database raises
allot of concern for me.
I guess I would just like some feed-back from others that may have
experience using SQL Server to house a large amount of images. I would
also like to hear if anyone may have pros/cons to one concept verses
another. Any help would be greatly appreciated.http://www.aspfaq.com/show.asp?id=2149
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
> I am a semi-experienced DBA, but have some questions regarding the
> storage of images inside of SQL Server (which I have never done before).
> I am researching to pros and cons of storing images inside of SQL
> Server verses a file server and DB combination (storing the file paths
> in SQL). There is a push from my management to store all the images
> within a very large database, but something is telling me that it's not
> the best way to do it. Unfortunately, I don't have any concrete
> experience/evidence to push in one direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size.
> The idea of moving all this into a large SQL Server database raises
> allot of concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.|||Hello Justin,
We used to have lot of Contracts that needed to be scanned in the insurance
application
and we used FileNET imaging server.
Gopi
"Justin Furch" <jlf@.anon.com> wrote in message
news:%23iW9Zu0wEHA.1512@.TK2MSFTNGP12.phx.gbl...
>I am a semi-experienced DBA, but have some questions regarding the storage
>of images inside of SQL Server (which I have never done before). I am
>researching to pros and cons of storing images inside of SQL Server verses
>a file server and DB combination (storing the file paths in SQL). There is
>a push from my management to store all the images within a very large
>database, but something is telling me that it's not the best way to do it.
>Unfortunately, I don't have any concrete experience/evidence to push in one
>direction verses another.
> We are talking about the storage/cataloging of around 4 million images.
> Our current image server is file-based and is around 1 tera in size. The
> idea of moving all this into a large SQL Server database raises allot of
> concern for me.
> I guess I would just like some feed-back from others that may have
> experience using SQL Server to house a large amount of images. I would
> also like to hear if anyone may have pros/cons to one concept verses
> another. Any help would be greatly appreciated.

Image rendering in IE

Hi All,
I cannot get any of my images to display in reports. I have tried all type
of image storage..embedded, external and database and they preview ok but
when i display the report through report manager or URL, the images will not
show. Any ideas?
regardsIt wouldn't be security settings preventing the display of images would it?
"ACC365" <ACC365@.discussions.microsoft.com> wrote in message
news:A865D853-BA28-4FAE-8EC9-14B305F02A3B@.microsoft.com...
> Hi All,
> I cannot get any of my images to display in reports. I have tried all type
> of image storage..embedded, external and database and they preview ok but
> when i display the report through report manager or URL, the images will
not
> show. Any ideas?
> regards|||Well I have found the solution. It is to do with the name of the
server...there is an underscore in it. IE doesn't view an underscore as a
valid charecter so it cannot resolve the pictures. The solution was to either
rename the server or change the "UseSessioncookies" flag in the reporting
services db. In the table configurationInfo I changes the flag to false and
lo and behold it worked.
much happier.
Andy

IIS Server

I have a SQL Database that is used for storage for an application located on a IIS server.

The users are having a hard time running reports, and automatically assume that it is the SQL Database. I have look in all of the logs, I have done a dbcc checkdb and everything looks fine.

However when I go to the IIS server itself in the event logs there are all sorts of error like the one below:

Error occurred in file:
'D:\teamtrack\Source\Servers\NotificationServer\NS ServerEvents.cpp', line 611.
Resource Msg Id = 474

An error occurred while connecting to
the server.

Message id 38340 could not be sent to (email_address.com.

Does this seems like a sql problem?

Please advise.

LystraSQL Server and IIS do not play well on the same server. You may have to throttle SQL Server's use of memory on the box so that IIS is not memory starved. Search the KnowledgeBase site or Google the microsoft.public.* groups with IIS , SQL Server, and memory keywords.

This sounds reminiscent of a question in the microsoft admin test that I took years ago.

HTH

Tom|||Thanks Tom,

It is not on the same server. I am getting error like below:



Exception occurred in file:
'D:\teamtrack\Source\Db\AppRecord.cpp', line 2827.
Resource Msg Id = 451


The '' record could not be updated in the 'Support Services' database table.

No rows were affected by the update or delete operation.

It looks like a update issue. How should I go about troubleshooting this problem???

Lystra|||In order to narrow down if this is a SQL server problem, or an IIS/Application problem, you coudl get profiler running on the SQL Server to trace all error events. The output will be somewhat confusing, as Profiler will not give you the text of the error message, and some of the messages are actually "normal". I never knew how many "Object not found" errors Enterprise manager could throw.

With this trace, you will find any overt SQL error that the database may be having. Things like the application having timeout issues would be missed by this, as Profiler will only trace server side events.

In order to catch possible timeouts, you could trace for any statement that takes more than 30,000 ms. If you see a lot of these, then you could have either poor database performance, or lock contention.|||I will try using the profiler.

I ran across an article form MS Knowledge base entitled:

There many not be enought virtual memory with large number of database.

Currently the database size is 1.40 GB, and log size is 208MB.

For the server properties on the memory tab it have User a fixed memory size 1494 and the minimun query memory is fixed at 1024.

Is there a way to check to see if memory is okay for the server itself?

Thanks

Lystra|||HELPPPP!!!

Does anyone have any ideas?

IIS + SQL (Not enough storage is available to complete this operation)

Sometimes we got an error (Not enough storage is available to complete this
operation) from one of our servers which on win2000 sp 4 iis5.0 (all hotfix
installed) sql server sp4. mdac 2.81. It occures random and we could not
find the error reason. When the error occured the server cpu or mem or I/O
usage is normal and I try to find out from internet and some says check the
mdac verision registery size or oracle etc. they do not indicate our
problem. we use adodb connection to connect sql server and all of them
close after the proccess also all recordset are close and set nothing. The
server transaction is kind of high but there are several indexes so it not
seems a problem there. When IIS restarts the problem solves but all users
sesions are closed. Have you ever met the error before or what can be
problem. Any advice would be helpful
Thanks"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
> Thanks
>
and the event error log says what?|||Do you see any errors in the Event Log (particularly about non-paged or
paged pool depletion)?
Cheers
Ken
"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
> this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
> hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
> the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
> Thanks
>|||"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
> this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
> hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
> the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
How much space is free in your database and the tempdb database? Are the
transaction logs full or not large enough? Are there any errors in the SQL
logs?
kind regards,
Karl Levinson, CISSP, CCSA, MCSE [MS MVP]
--
Microsoft Security FAQ:
http://securityadmin.info|||There is nothing in to event log even a warning also temp db size quite
large and the db transaction log is about 2 gbyte and %75 is free and there
is nothing also in sql server logs.
Thanks
"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
> Thanks
>

IIS + SQL (Not enough storage is available to complete this operation)

Sometimes we got an error (Not enough storage is available to complete this
operation) from one of our servers which on win2000 sp 4 iis5.0 (all hotfix
installed) sql server sp4. mdac 2.81. It occures random and we could not
find the error reason. When the error occured the server cpu or mem or I/O
usage is normal and I try to find out from internet and some says check the
mdac verision registery size or oracle etc. they do not indicate our
problem. we use adodb connection to connect sql server and all of them
close after the proccess also all recordset are close and set nothing. The
server transaction is kind of high but there are several indexes so it not
seems a problem there. When IIS restarts the problem solves but all users
sesions are closed. Have you ever met the error before or what can be
problem. Any advice would be helpful
Thanks"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
> Thanks
>
and the event error log says what?|||Do you see any errors in the Event Log (particularly about non-paged or
paged pool depletion)?
Cheers
Ken
"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
> this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
> hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
> the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
> Thanks
>|||"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
> this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
> hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
> the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
How much space is free in your database and the tempdb database? Are the
transaction logs full or not large enough? Are there any errors in the SQL
logs?
--
kind regards,
Karl Levinson, CISSP, CCSA, MCSE [MS MVP]
--
Microsoft Security FAQ:
http://securityadmin.info|||There is nothing in to event log even a warning also temp db size quite
large and the db transaction log is about 2 gbyte and %75 is free and there
is nothing also in sql server logs.
Thanks
"tolgay" <tgul@.tgul.com> wrote in message
news:uibNfg0qGHA.4676@.TK2MSFTNGP03.phx.gbl...
> Sometimes we got an error (Not enough storage is available to complete
this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could not
> find the error reason. When the error occured the server cpu or mem or I/O
> usage is normal and I try to find out from internet and some says check
the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of them
> close after the proccess also all recordset are close and set nothing. The
> server transaction is kind of high but there are several indexes so it not
> seems a problem there. When IIS restarts the problem solves but all users
> sesions are closed. Have you ever met the error before or what can be
> problem. Any advice would be helpful
> Thanks
>|||Sometimes we got an error (Not enough storage is available to complete
this
operation) from one of our servers which on win2000 sp 4 iis5.0 (all
hotfix
installed) sql server sp4. mdac 2.81. It occures random and we could not
find the error reason. When the error occured the server cpu or mem or I/O
usage is normal and I try to find out from internet and some says check
the
mdac verision registery size or oracle etc. they do not indicate our
problem. we use adodb connection to connect sql server and all of them
close after the proccess also all recordset are close and set nothing. The
server transaction is kind of high but there are several indexes so it not
seems a problem there. When IIS restarts the problem solves but all users
sesions are closed. Have you ever met the error before or what can be
problem. Any advice would be helpful
There is nothing in to event log even a warning also temp db size quite
large and the db transaction log is about 2 gbyte and %75 is free and there
is nothing also in sql server logs.|||tolgay wrote:
> Sometimes we got an error (Not enough storage is available to
> complete this
> operation) from one of our servers which on win2000 sp 4 iis5.0 (all
> hotfix
> installed) sql server sp4. mdac 2.81. It occures random and we could
> not find the error reason. When the error occured the server cpu or
> mem or I/O usage is normal and I try to find out from internet and
> some says check the
> mdac verision registery size or oracle etc. they do not indicate our
> problem. we use adodb connection to connect sql server and all of
> them close after the proccess also all recordset are close and set
> nothing. The server transaction is kind of high but there are
> several indexes so it not seems a problem there. When IIS restarts
> the problem solves but all users sesions are closed. Have you ever
> met the error before or what can be problem. Any advice would be
> helpful
> There is nothing in to event log even a warning also temp db size
> quite large and the db transaction log is about 2 gbyte and %75 is
> free and there is nothing also in sql server logs.
We were seeing this problem and tracked ours back to a huge number of
active directory groups - we use the following in a .reg file to
increase the size - works ok after reboot then - it affected all sorts
of things - we couldnt add a pc to the domain without seeing this error.
Copy the following into notepad and save with name of your choosing,
ours in mtsize.reg then run it to make the change in the registry.
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parame
ters]
"MaxTokenSize"=dword:00010000