Saving Pictures Inside the DB?

Hi,
I have a database that stores a lot of searchable data (I mean INT and
VARCHAR and ...) but also huge number of pictures are added regularly. My
estimation is that after a few months (when all clients become active), at
least 4GB of pictures will be inserted daily. So after 3 years the database
reaches to 4TB!
I'd like to know that whether this is a right way that we are going or not?!
I mean what will happen to size of our backups?
Some people suggest to store pictures outside and keep the path in rows.
I would be grateful if someone could evaluate these two methods.
Thanks in advance,
Leila
GOOGLE 'storing files database'.
Here's are 2 sites that discuss both options:
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=414
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
|||When SQL Server 2008 is released it will offer a better alternative using
the FILESTREAM data type:
http://blogs.msdn.com/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||You may also want to check out this paper;
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
Linchi
"Leila" wrote:

> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
>
|||Leila we use the database only to store the Thumbs and show them in the grid
when the client do a search, ours thumbs are under 2kb and our database
start becaming very big 17millions of records so far, another issue will be
your transacion log, think about people updating or deleting the images,
that will create a HUGE transaction log file but you can solve that using
"writetext".
Remember this: if you are going to show only 1 image at the time you can
save the image path on the row and always load the image and show in the
client.
Last thing to consider is your network traffic, you don't want huge images
going forward and backward in your network
hope this will help you
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
|||I appreciate everybody!
We won't have too much of manipulations over pictures. What if we store
pictures in another database within the same SQL Server instance? This way
we can have: transactional consistency, replication, smaller size for
backing up the main database.
Any ideas?
Thanks again.
"Jorge" <nospam@.austin.rr.com> wrote in message
news:480638d2$0$31736$4c368faf@.roadrunner.com...
> Leila we use the database only to store the Thumbs and show them in the
> grid when the client do a search, ours thumbs are under 2kb and our
> database start becaming very big 17millions of records so far, another
> issue will be your transacion log, think about people updating or deleting
> the images, that will create a HUGE transaction log file but you can solve
> that using "writetext".
> Remember this: if you are going to show only 1 image at the time you can
> save the image path on the row and always load the image and show in the
> client.
> Last thing to consider is your network traffic, you don't want huge images
> going forward and backward in your network
> hope this will help you
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
>
|||Just curious, are you EVER going to fix your system clock? I have asked you
multiple times to correct this, and the fact that you are ignoring my
requests demonstrates that you are doing it intentionally. This is very
inconsiderate.
On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
"Leila" <Leilas@.hotpop.com> wrote:

> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
|||I also have a problem with the system date\time. It just does not remember
the date\time after a short while. I guess it's because of the BIOS battery
(even if this laptop is just 2,5 years old?) of my laptop. However I keep it
up to date using Clock Synchronization of Windows.
Maybe Leila also has a problem like this and does not know how to work
around it.
Ekrem nsoy
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:C42D4D61.DFC%ten.xoc@.dnartreb.noraa...
> Just curious, are you EVER going to fix your system clock? I have asked
> you
> multiple times to correct this, and the fact that you are ignoring my
> requests demonstrates that you are doing it intentionally. This is very
> inconsiderate.
>
>
> On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
> "Leila" <Leilas@.hotpop.com> wrote:
>
|||If dangling relationships concerns you:
http://sql-server-performance.com/Community/forums/p/26369/144268.aspx#144268
Be sure to put images in a separate filegroup though.
/k
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>