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
>
Showing posts with label stores. Show all posts
Showing posts with label stores. Show all posts
Wednesday, March 28, 2012
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.
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.
Monday, March 26, 2012
Saving Images
I'm curious if there are any bottle-neck issues I need to be aware of with
an application that stores scanned images into SQL. The app uses a standard
SQL Image field for the storage, and TIFF images are being stored in it.
Currently my client is adding about 500 records (about 100 megs) per day.
The current size of the image table is just over 4 gigs. In year they will
have a 35 - 40 gig table.
Other than running out of disk space, are there any issues, design or
otherwise I should know about?
TIA,
-Steve-Lookup TEXTIMAGE_ON in Books Online under CREATE TABLE for details on how to
use separate data files to store large data.
ML|||We developed something which had a similar situation, instead of
storing images in SQL, we stored them as images on a hard disk. We had
a column in a table which had a filereference to the image on disk.
We felt that managing large SQL databases could cause a problem and
hence adopted this approach which worked out well for us.
Joshi
Steve Zimmelman wrote:
> I'm curious if there are any bottle-neck issues I need to be aware of with
> an application that stores scanned images into SQL. The app uses a standa
rd
> SQL Image field for the storage, and TIFF images are being stored in it.
> Currently my client is adding about 500 records (about 100 megs) per day.
> The current size of the image table is just over 4 gigs. In year they wil
l
> have a 35 - 40 gig table.
> Other than running out of disk space, are there any issues, design or
> otherwise I should know about?
> TIA,
> -Steve-|||Thanks ML.
Are you referring to "FileGroups" ? The documentation isn't that clear to
me.
I'd appreciate any additional pointers.
Here are the Scripts used to create the image table. Given what I've stated
about the application, what would you change?
CREATE TABLE [dbo].[Images] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FamilyNo] [varchar] (15) NOT NULL ,
[PersonNo] [varchar] (2) NOT NULL ,
[Folder_ID] [int] NOT NULL ,
[Descr] [varchar] (100) NULL ,
[ImageData] [image] NULL ,
[ImageSize] [int] NULL ,
[ImageCount] [smallint] NULL ,
[DataType] [varchar] (1) NULL ,
[CreatedDate] [datetime] NULL ,
[CreatedUser] [varchar] (6) NULL ,
[LastUpdate] [datetime] NULL ,
[LastUser] [varchar] (6) NULL ,
[TimeToPost] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Images] WITH NOCHECK ADD
CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Images] ADD
CONSTRAINT [DF_Images_DataType] DEFAULT ('I') FOR [DataType],
CONSTRAINT [DF_Images_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate],
CONSTRAINT [DF_Images_TimeToPost] DEFAULT (0) FOR [TimeToPost]
CREATE INDEX [IX_ImagesFamNoPerNo] ON [dbo].[Images]([FamilyNo],
[PersonNo]) ON [PRIMARY]
CREATE INDEX [IX_Images_FolderID] ON [dbo].[Images]([Folder_ID]) ON
[PRIMARY]
"ML" <ML@.discussions.microsoft.com> wrote in message
news:87B0E552-EC1B-484B-8B1C-DC28C1079345@.microsoft.com...
> Lookup TEXTIMAGE_ON in Books Online under CREATE TABLE for details on how
> to
> use separate data files to store large data.
>
> ML|||Hi Joshi,
We looked at that option, but IT decided they didn't want to manage the
backups/restores. The other problem is the shear number of images. The app
has only been in production for a few months and there are over 30,000
records with more than 500 being added daily. We also have quite a few
remote sites across the country using a VPN for data access. It would
require a bit more tweaking for those remote sites to send physical image
files to the server. Right now they just send it using a SQL stored
procedure, and SQL does the rest. But if maintaining a large database is
not something that SQL can handle very well, then we'll have to rethink our
options. My clients' objective is to become "paperless" and store all
patient documentation electronically.
Thanks,
-Steve-
<joshidm@.gmail.com> wrote in message
news:1129132566.523707.107460@.g49g2000cwa.googlegroups.com...
> We developed something which had a similar situation, instead of
> storing images in SQL, we stored them as images on a hard disk. We had
> a column in a table which had a filereference to the image on disk.
> We felt that managing large SQL databases could cause a problem and
> hence adopted this approach which worked out well for us.
> Joshi
>|||Steve,
Yeah...from the BOL as ML suggested:
TEXTIMAGE_ON
Are keywords indicating that the text, ntext, and image columns are stored
on the specified filegroup. TEXTIMAGE ON is not allowed if there are no
text, ntext, or image columns in the table. If TEXTIMAGE_ON is not
specified, the text, ntext, and image columns are stored in the same
filegroup as the table.
HTH
Jerry
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:OeqlQe0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Thanks ML.
> Are you referring to "FileGroups" ? The documentation isn't that clear to
> me.
> I'd appreciate any additional pointers.
> Here are the Scripts used to create the image table. Given what I've
> stated about the application, what would you change?
> CREATE TABLE [dbo].[Images] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [FamilyNo] [varchar] (15) NOT NULL ,
> [PersonNo] [varchar] (2) NOT NULL ,
> [Folder_ID] [int] NOT NULL ,
> [Descr] [varchar] (100) NULL ,
> [ImageData] [image] NULL ,
> [ImageSize] [int] NULL ,
> [ImageCount] [smallint] NULL ,
> [DataType] [varchar] (1) NULL ,
> [CreatedDate] [datetime] NULL ,
> [CreatedUser] [varchar] (6) NULL ,
> [LastUpdate] [datetime] NULL ,
> [LastUser] [varchar] (6) NULL ,
> [TimeToPost] [smallint] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ALTER TABLE [dbo].[Images] WITH NOCHECK ADD
> CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Images] ADD
> CONSTRAINT [DF_Images_DataType] DEFAULT ('I') FOR [DataType],
> CONSTRAINT [DF_Images_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate],
> CONSTRAINT [DF_Images_TimeToPost] DEFAULT (0) FOR [TimeToPost]
> CREATE INDEX [IX_ImagesFamNoPerNo] ON [dbo].[Images]([FamilyNo],
> [PersonNo]) ON [PRIMARY]
> CREATE INDEX [IX_Images_FolderID] ON [dbo].[Images]([Folder_ID]) ON
> [PRIMARY]
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:87B0E552-EC1B-484B-8B1C-DC28C1079345@.microsoft.com...
>|||Steve,
Change nothing in your table.
Create a folder called IMAGES somewhere on your server. When someone
stores an image basically place the image into the IMAGES folder with
the name as ID.gif or whatever the extension is.
Joshi
Steve Zimmelman wrote:
> Hi Joshi,
> We looked at that option, but IT decided they didn't want to manage the
> backups/restores. The other problem is the shear number of images. The a
pp
> has only been in production for a few months and there are over 30,000
> records with more than 500 being added daily. We also have quite a few
> remote sites across the country using a VPN for data access. It would
> require a bit more tweaking for those remote sites to send physical image
> files to the server. Right now they just send it using a SQL stored
> procedure, and SQL does the rest. But if maintaining a large database is
> not something that SQL can handle very well, then we'll have to rethink ou
r
> options. My clients' objective is to become "paperless" and store all
> patient documentation electronically.
> Thanks,
> -Steve-
> <joshidm@.gmail.com> wrote in message
> news:1129132566.523707.107460@.g49g2000cwa.googlegroups.com...|||Thanks Joshi,
I understand the concept. But it would require quite a bit of code changing
to accomodate this. Not only the table structure, but also the application
in the way it loads, saves, and displays the images.
Thanks,
-Steve-
<joshidm@.gmail.com> wrote in message
news:1129134627.264554.161450@.o13g2000cwo.googlegroups.com...
> Steve,
> Change nothing in your table.
> Create a folder called IMAGES somewhere on your server. When someone
> stores an image basically place the image into the IMAGES folder with
> the name as ID.gif or whatever the extension is.
> Joshi|||How far back do you need to save these images in the database?
Can you archive them after a certain period?
Of course, the client will eventually want to access an archived image but
you have a while to figure that out.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23p05FH1zFHA.268@.TK2MSFTNGP09.phx.gbl...
> Thanks Joshi,
> I understand the concept. But it would require quite a bit of code
> changing to accomodate this. Not only the table structure, but also the
> application in the way it loads, saves, and displays the images.
> Thanks,
> -Steve-
> <joshidm@.gmail.com> wrote in message
> news:1129134627.264554.161450@.o13g2000cwo.googlegroups.com...
>|||Storing large data (text, ntext and/or image) on a separate filegroup
improves performance of queries that do not retrieve or test any large data.
You also say you expect a large quantity of images - when issuing a typical
search query the server only has to search the proportionally smaller
filegroup, containing descriptive data, and only needs to access the rather
large text/image filegroup to retrieve the few images that correspond to the
search criteria.
You should also consider designing an archive table (maybe even in a
separate database, but that's not mandatory). Of course the criteria which
the archiving process should be based upon cannot simply be the age of an
image (the amount of time elapsed since the image was inserted). Only *least
accessed* images should be archived. To accommodate this, you'd have to keep
a record of table access, and use a scheduled job to move less accessed
images to the archive table. Or something like that - in order to keep the
production table as small as necesairy.
But I really don't want to over-complicate this answer right now. :)
ML
an application that stores scanned images into SQL. The app uses a standard
SQL Image field for the storage, and TIFF images are being stored in it.
Currently my client is adding about 500 records (about 100 megs) per day.
The current size of the image table is just over 4 gigs. In year they will
have a 35 - 40 gig table.
Other than running out of disk space, are there any issues, design or
otherwise I should know about?
TIA,
-Steve-Lookup TEXTIMAGE_ON in Books Online under CREATE TABLE for details on how to
use separate data files to store large data.
ML|||We developed something which had a similar situation, instead of
storing images in SQL, we stored them as images on a hard disk. We had
a column in a table which had a filereference to the image on disk.
We felt that managing large SQL databases could cause a problem and
hence adopted this approach which worked out well for us.
Joshi
Steve Zimmelman wrote:
> I'm curious if there are any bottle-neck issues I need to be aware of with
> an application that stores scanned images into SQL. The app uses a standa
rd
> SQL Image field for the storage, and TIFF images are being stored in it.
> Currently my client is adding about 500 records (about 100 megs) per day.
> The current size of the image table is just over 4 gigs. In year they wil
l
> have a 35 - 40 gig table.
> Other than running out of disk space, are there any issues, design or
> otherwise I should know about?
> TIA,
> -Steve-|||Thanks ML.
Are you referring to "FileGroups" ? The documentation isn't that clear to
me.
I'd appreciate any additional pointers.
Here are the Scripts used to create the image table. Given what I've stated
about the application, what would you change?
CREATE TABLE [dbo].[Images] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FamilyNo] [varchar] (15) NOT NULL ,
[PersonNo] [varchar] (2) NOT NULL ,
[Folder_ID] [int] NOT NULL ,
[Descr] [varchar] (100) NULL ,
[ImageData] [image] NULL ,
[ImageSize] [int] NULL ,
[ImageCount] [smallint] NULL ,
[DataType] [varchar] (1) NULL ,
[CreatedDate] [datetime] NULL ,
[CreatedUser] [varchar] (6) NULL ,
[LastUpdate] [datetime] NULL ,
[LastUser] [varchar] (6) NULL ,
[TimeToPost] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Images] WITH NOCHECK ADD
CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Images] ADD
CONSTRAINT [DF_Images_DataType] DEFAULT ('I') FOR [DataType],
CONSTRAINT [DF_Images_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate],
CONSTRAINT [DF_Images_TimeToPost] DEFAULT (0) FOR [TimeToPost]
CREATE INDEX [IX_ImagesFamNoPerNo] ON [dbo].[Images]([FamilyNo],
[PersonNo]) ON [PRIMARY]
CREATE INDEX [IX_Images_FolderID] ON [dbo].[Images]([Folder_ID]) ON
[PRIMARY]
"ML" <ML@.discussions.microsoft.com> wrote in message
news:87B0E552-EC1B-484B-8B1C-DC28C1079345@.microsoft.com...
> Lookup TEXTIMAGE_ON in Books Online under CREATE TABLE for details on how
> to
> use separate data files to store large data.
>
> ML|||Hi Joshi,
We looked at that option, but IT decided they didn't want to manage the
backups/restores. The other problem is the shear number of images. The app
has only been in production for a few months and there are over 30,000
records with more than 500 being added daily. We also have quite a few
remote sites across the country using a VPN for data access. It would
require a bit more tweaking for those remote sites to send physical image
files to the server. Right now they just send it using a SQL stored
procedure, and SQL does the rest. But if maintaining a large database is
not something that SQL can handle very well, then we'll have to rethink our
options. My clients' objective is to become "paperless" and store all
patient documentation electronically.
Thanks,
-Steve-
<joshidm@.gmail.com> wrote in message
news:1129132566.523707.107460@.g49g2000cwa.googlegroups.com...
> We developed something which had a similar situation, instead of
> storing images in SQL, we stored them as images on a hard disk. We had
> a column in a table which had a filereference to the image on disk.
> We felt that managing large SQL databases could cause a problem and
> hence adopted this approach which worked out well for us.
> Joshi
>|||Steve,
Yeah...from the BOL as ML suggested:
TEXTIMAGE_ON
Are keywords indicating that the text, ntext, and image columns are stored
on the specified filegroup. TEXTIMAGE ON is not allowed if there are no
text, ntext, or image columns in the table. If TEXTIMAGE_ON is not
specified, the text, ntext, and image columns are stored in the same
filegroup as the table.
HTH
Jerry
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:OeqlQe0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Thanks ML.
> Are you referring to "FileGroups" ? The documentation isn't that clear to
> me.
> I'd appreciate any additional pointers.
> Here are the Scripts used to create the image table. Given what I've
> stated about the application, what would you change?
> CREATE TABLE [dbo].[Images] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [FamilyNo] [varchar] (15) NOT NULL ,
> [PersonNo] [varchar] (2) NOT NULL ,
> [Folder_ID] [int] NOT NULL ,
> [Descr] [varchar] (100) NULL ,
> [ImageData] [image] NULL ,
> [ImageSize] [int] NULL ,
> [ImageCount] [smallint] NULL ,
> [DataType] [varchar] (1) NULL ,
> [CreatedDate] [datetime] NULL ,
> [CreatedUser] [varchar] (6) NULL ,
> [LastUpdate] [datetime] NULL ,
> [LastUser] [varchar] (6) NULL ,
> [TimeToPost] [smallint] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ALTER TABLE [dbo].[Images] WITH NOCHECK ADD
> CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Images] ADD
> CONSTRAINT [DF_Images_DataType] DEFAULT ('I') FOR [DataType],
> CONSTRAINT [DF_Images_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate],
> CONSTRAINT [DF_Images_TimeToPost] DEFAULT (0) FOR [TimeToPost]
> CREATE INDEX [IX_ImagesFamNoPerNo] ON [dbo].[Images]([FamilyNo],
> [PersonNo]) ON [PRIMARY]
> CREATE INDEX [IX_Images_FolderID] ON [dbo].[Images]([Folder_ID]) ON
> [PRIMARY]
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:87B0E552-EC1B-484B-8B1C-DC28C1079345@.microsoft.com...
>|||Steve,
Change nothing in your table.
Create a folder called IMAGES somewhere on your server. When someone
stores an image basically place the image into the IMAGES folder with
the name as ID.gif or whatever the extension is.
Joshi
Steve Zimmelman wrote:
> Hi Joshi,
> We looked at that option, but IT decided they didn't want to manage the
> backups/restores. The other problem is the shear number of images. The a
pp
> has only been in production for a few months and there are over 30,000
> records with more than 500 being added daily. We also have quite a few
> remote sites across the country using a VPN for data access. It would
> require a bit more tweaking for those remote sites to send physical image
> files to the server. Right now they just send it using a SQL stored
> procedure, and SQL does the rest. But if maintaining a large database is
> not something that SQL can handle very well, then we'll have to rethink ou
r
> options. My clients' objective is to become "paperless" and store all
> patient documentation electronically.
> Thanks,
> -Steve-
> <joshidm@.gmail.com> wrote in message
> news:1129132566.523707.107460@.g49g2000cwa.googlegroups.com...|||Thanks Joshi,
I understand the concept. But it would require quite a bit of code changing
to accomodate this. Not only the table structure, but also the application
in the way it loads, saves, and displays the images.
Thanks,
-Steve-
<joshidm@.gmail.com> wrote in message
news:1129134627.264554.161450@.o13g2000cwo.googlegroups.com...
> Steve,
> Change nothing in your table.
> Create a folder called IMAGES somewhere on your server. When someone
> stores an image basically place the image into the IMAGES folder with
> the name as ID.gif or whatever the extension is.
> Joshi|||How far back do you need to save these images in the database?
Can you archive them after a certain period?
Of course, the client will eventually want to access an archived image but
you have a while to figure that out.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23p05FH1zFHA.268@.TK2MSFTNGP09.phx.gbl...
> Thanks Joshi,
> I understand the concept. But it would require quite a bit of code
> changing to accomodate this. Not only the table structure, but also the
> application in the way it loads, saves, and displays the images.
> Thanks,
> -Steve-
> <joshidm@.gmail.com> wrote in message
> news:1129134627.264554.161450@.o13g2000cwo.googlegroups.com...
>|||Storing large data (text, ntext and/or image) on a separate filegroup
improves performance of queries that do not retrieve or test any large data.
You also say you expect a large quantity of images - when issuing a typical
search query the server only has to search the proportionally smaller
filegroup, containing descriptive data, and only needs to access the rather
large text/image filegroup to retrieve the few images that correspond to the
search criteria.
You should also consider designing an archive table (maybe even in a
separate database, but that's not mandatory). Of course the criteria which
the archiving process should be based upon cannot simply be the age of an
image (the amount of time elapsed since the image was inserted). Only *least
accessed* images should be archived. To accommodate this, you'd have to keep
a record of table access, and use a scheduled job to move less accessed
images to the archive table. Or something like that - in order to keep the
production table as small as necesairy.
But I really don't want to over-complicate this answer right now. :)
ML
Subscribe to:
Posts (Atom)