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,
LeilaGOOGLE '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...
>> 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
>|||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:
>> 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
>>
>|||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
>|||> 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.
No, Leila has been told about this multiple times, and her system clock is
always magically 24 hours ahead. If it were a BIOS battery issue, the clock
should lose time, so she should be posting in the past in that case, not the
future. Future posting is a slimy way to keep your post at the top of the
message list when you sort by sent descending, since the server obeys the
date/time in the headers of your message, as opposed to the date/time they
actually received the post.|||Hi Aaron,
Thank you for mentioning this issue. Actually I had checked this multiple
times before and my clock was fixed. It was a long time that I didn't come
to this newsgroup and I wasn't aware that this problem has turned up again.
I have the same problem with my Pocket PC, sometimes my reminder displays my
tasks of tomorrow and I realize that my PPC's clock is wrong. Probably this
happens when I sync my PPC with notebook.
I guess some software which is resident in the memory is doing this due to
some wrong setting, but really have no idea what can this be.
I apologize again!
Leila
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:7971D90C-DBB1-43FB-92AA-95B8959A9B04@.microsoft.com...
>> 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.
> No, Leila has been told about this multiple times, and her system clock is
> always magically 24 hours ahead. If it were a BIOS battery issue, the
> clock should lose time, so she should be posting in the past in that case,
> not the future. Future posting is a slimy way to keep your post at the
> top of the message list when you sort by sent descending, since the server
> obeys the date/time in the headers of your message, as opposed to the
> date/time they actually received the post.