RE: SQL for File Storage?
From the SP 2010 class we recently attended, I wouldn't think MS is pushing BLOBS with SP, just that they've added it. I was eager to hear about this feature, thinking it would be more like a friendly explorer-based file system that we could use, but it is complicated to set up and doesn't seem to have much, if any, advantage (yet?) that we can see. We are not planning on implementing BLOBs--maybe if they can improve the back-end to be more useful for admins. I'm sure there have been bugs, but the only real problem I can think of with the way SP stores files in SQL has to do with accessing them via IIS--there are hard limits to the file sizes you can use. -Original Message- From: Sam Cayze [mailto:sca...@gmail.com] Sent: Friday, August 05, 2011 11:08 AM To: NT System Admin Issues Subject: RE: SQL for File Storage? Yes, I think there were problems though, and iirc MS is trying to move away from that. To 'BLOBS' or something. Some sort of external SQL storage mechanism. -Original Message- From: Tigran K [mailto:tigr...@gmail.com] Sent: Friday, August 05, 2011 1:01 PM To: NT System Admin Issues Subject: Re: SQL for File Storage? Sharepoint stores files in SQL with no problem. --T On Fri, Aug 5, 2011 at 10:37 AM, Roger Wright rhw...@gmail.com wrote: We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
RE: SQL for File Storage?
Small files are stored in the NTFS MFT. That probably becomes a bottleneck unless you have the IOPS throughput to back it. SQL Server 2008 has Filestream data type, which is a way of combining NTFS with SQL Server. Otherwise, products like SharePoint do store documents in a DB. If you through enough hardware at SharePoint, you can keep it working. That said - SQL Server is not a scale-out system - it's a scale up system. Each instance of SQL Server can only run on a single cluster node - so that cluster node needs to have enough grunt. There are many considerations here: reads versus writes. Size of docs. Indexing/searching doc contents etc. I doubt there is a single right answer to this problem. Cheers Ken From: David Lum [mailto:david@nwea.org] Sent: Saturday, 6 August 2011 2:15 AM To: NT System Admin Issues Subject: RE: SQL for File Storage? With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. +1,000,000 Dave From: Webster [mailto:webs...@carlwebster.com]mailto:[mailto:webs...@carlwebster.com] Sent: Friday, August 05, 2011 10:54 AM To: NT System Admin Issues Subject: RE: SQL for File Storage? I did some work for a bank processing company one time where they processed all the checks, deposits, etc for numerous banks. The software they used put everything nice and neat in a very organized folder system with files for everything processed for that day in a separate folder. After 2 months the system was begging for mercy. When they scanned in an item, the item was turned into a front and back GIF and JPG and PNG and BMP and then a PDF of all the above. Process a small bank's daily intake of say 5,000 items and each folder would contain around 45,000 files. Multiply that by 6 work days a week, 4 (or so) weeks month times 2 months and then all this for around 50 banks. The file servers was begging for mercy. After the first week on the new software it was taking 30+ hours to do a daily backup so they went to a weekly backup. The backup started at 10PM Friday night and was not finished by 8AM the following Monday. As a former app dev guy, I always wondered why they didn't just use SQL, only the PNG image type and build a PDF on an as needed basis. The processing company was sold after 4 months to a company that moved them to an AS/400 system where everything was scanned in and stored in a database. With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. Carl Webster Consultant and Citrix Technology Professional http://www.CarlWebster.comhttp://www.carlwebster.com/ From: Roger Wright [mailto:rhw...@gmail.com]mailto:[mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
Re: SQL for File Storage?
I'd say it comes down to the implementation and understanding the data set and query activity... have a look at SQL Server's FILESTREAM feature (which uses NTFS for object storage + does not muck up SQL's memory cache) and MS's guidance on when to use it vs. a plain old varbinary or image column. There's no one right answer for this stuff--you could have a nice d:\data\a\b\c\1.txt prefix-tree structure that provides nearly constant-time access to files if you know the name, but is horrible to back up or search once there are millions of files in the tree... but maybe you can layer smart incremental backups and a Lucene or Windows Search index on top and the problem goes away... and direct SQL storage of blob data could be a perfect match for a while, until the data volume grows, and you have to think about partitioning, more filegroups, etc. Each database has different strategies for handling blobs and their tendency to make a mess out of the ordered page and index structures. Likewise different filesystems have strengths and weaknesses in managing tree depth, number of files in a single directory, number of files in the global allocation table, and so on. --Steve On Fri, Aug 5, 2011 at 4:31 PM, Roger Wright rhw...@gmail.com wrote: Thanks, all. Sounds like SQL isn't the problem I imagined and may actually be the solution. Roger Wright ___ Dr. Seuss is my favorite rapper! Cat - Hat... sheer genius! On Fri, Aug 5, 2011 at 2:48 PM, Kurt Buff kurt.b...@gmail.com wrote: I've experienced problems with directories that have as few as 10k files in them. This usually manifests itself first as problems browsing the directory with explorer.exe. Once you have problems with explorer hanging, I've found that it's time to start dividing the files into smaller directories. Kurt On Fri, Aug 5, 2011 at 10:53, Webster webs...@carlwebster.com wrote: I did some work for a bank processing company one time where they processed all the checks, deposits, etc for numerous banks. The software they used put everything nice and neat in a very organized folder system with files for everything processed for that day in a separate folder. After 2 months the system was begging for mercy. When they scanned in an item, the item was turned into a front and back GIF and JPG and PNG and BMP and then a PDF of all the above. Process a small bank’s daily intake of say 5,000 items and each folder would contain around 45,000 files. Multiply that by 6 work days a week, 4 (or so) weeks month times 2 months and then all this for around 50 banks. The file servers was begging for mercy. After the first week on the new software it was taking 30+ hours to do a daily backup so they went to a weekly backup. The backup started at 10PM Friday night and was not finished by 8AM the following Monday. As a former app dev guy, I always wondered why they didn’t just use SQL, only the PNG image type and build a PDF on an as needed basis. The processing company was sold after 4 months to a company that moved them to an AS/400 system where everything was scanned in and stored in a database. With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. Carl Webster Consultant and Citrix Technology Professional http://www.CarlWebster.com From: Roger Wright [mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here:
RE: SQL for File Storage?
I did some work for a bank processing company one time where they processed all the checks, deposits, etc for numerous banks. The software they used put everything nice and neat in a very organized folder system with files for everything processed for that day in a separate folder. After 2 months the system was begging for mercy. When they scanned in an item, the item was turned into a front and back GIF and JPG and PNG and BMP and then a PDF of all the above. Process a small bank's daily intake of say 5,000 items and each folder would contain around 45,000 files. Multiply that by 6 work days a week, 4 (or so) weeks month times 2 months and then all this for around 50 banks. The file servers was begging for mercy. After the first week on the new software it was taking 30+ hours to do a daily backup so they went to a weekly backup. The backup started at 10PM Friday night and was not finished by 8AM the following Monday. As a former app dev guy, I always wondered why they didn't just use SQL, only the PNG image type and build a PDF on an as needed basis. The processing company was sold after 4 months to a company that moved them to an AS/400 system where everything was scanned in and stored in a database. With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. Carl Webster Consultant and Citrix Technology Professional http://www.CarlWebster.comhttp://www.carlwebster.com/ From: Roger Wright [mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.commailto:listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
Re: SQL for File Storage?
Sharepoint stores files in SQL with no problem. --T On Fri, Aug 5, 2011 at 10:37 AM, Roger Wright rhw...@gmail.com wrote: We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
RE: SQL for File Storage?
We have a project management system that stores everything outside the DBs. That model works great for us, everything is served up instantaneously. We have about 100GB worth of active data in it (And we archive the data out about 4 times a year). From: Roger Wright [mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
RE: SQL for File Storage?
Yes, I think there were problems though, and iirc MS is trying to move away from that. To 'BLOBS' or something. Some sort of external SQL storage mechanism. -Original Message- From: Tigran K [mailto:tigr...@gmail.com] Sent: Friday, August 05, 2011 1:01 PM To: NT System Admin Issues Subject: Re: SQL for File Storage? Sharepoint stores files in SQL with no problem. --T On Fri, Aug 5, 2011 at 10:37 AM, Roger Wright rhw...@gmail.com wrote: We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
RE: SQL for File Storage?
With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. +1,000,000 Dave From: Webster [mailto:webs...@carlwebster.com] Sent: Friday, August 05, 2011 10:54 AM To: NT System Admin Issues Subject: RE: SQL for File Storage? I did some work for a bank processing company one time where they processed all the checks, deposits, etc for numerous banks. The software they used put everything nice and neat in a very organized folder system with files for everything processed for that day in a separate folder. After 2 months the system was begging for mercy. When they scanned in an item, the item was turned into a front and back GIF and JPG and PNG and BMP and then a PDF of all the above. Process a small bank's daily intake of say 5,000 items and each folder would contain around 45,000 files. Multiply that by 6 work days a week, 4 (or so) weeks month times 2 months and then all this for around 50 banks. The file servers was begging for mercy. After the first week on the new software it was taking 30+ hours to do a daily backup so they went to a weekly backup. The backup started at 10PM Friday night and was not finished by 8AM the following Monday. As a former app dev guy, I always wondered why they didn't just use SQL, only the PNG image type and build a PDF on an as needed basis. The processing company was sold after 4 months to a company that moved them to an AS/400 system where everything was scanned in and stored in a database. With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. Carl Webster Consultant and Citrix Technology Professional http://www.CarlWebster.comhttp://www.carlwebster.com/ From: Roger Wright [mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.commailto:listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.commailto:listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
RE: SQL for File Storage?
A lot of times depending on how large the files are its might be better to use Remote Storage instead of storing stuff in SQL blobs. Z Edward E. Ziots CISSP, Network +, Security + Security Engineer Lifespan Organization Email:ezi...@lifespan.org Cell:401-639-3505 -Original Message- From: Sam Cayze [mailto:sca...@gmail.com] Sent: Friday, August 05, 2011 2:08 PM To: NT System Admin Issues Subject: RE: SQL for File Storage? Yes, I think there were problems though, and iirc MS is trying to move away from that. To 'BLOBS' or something. Some sort of external SQL storage mechanism. -Original Message- From: Tigran K [mailto:tigr...@gmail.com] Sent: Friday, August 05, 2011 1:01 PM To: NT System Admin Issues Subject: Re: SQL for File Storage? Sharepoint stores files in SQL with no problem. --T On Fri, Aug 5, 2011 at 10:37 AM, Roger Wright rhw...@gmail.com wrote: We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
RE: SQL for File Storage?
Don't forget how you're going to back it up as well. Stating the obvious I know but backing up a large SQL database is very different to backing up a large drive full of files. From: Roger Wright [rhw...@gmail.com] Sent: 05 August 2011 6:37 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.commailto:listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin -- MIRA Ltd Watling Street, Nuneaton, Warwickshire, CV10 0TU, England Registered in England and Wales No. 402570 VAT Registration GB 100 1464 84 The contents of this e-mail are confidential and are solely for the use of the intended recipient. If you receive this e-mail in error, please delete it and notify us either by e-mail, telephone or fax. You should not copy, forward or otherwise disclose the content of the e-mail as this is prohibited. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
Re: SQL for File Storage?
I've experienced problems with directories that have as few as 10k files in them. This usually manifests itself first as problems browsing the directory with explorer.exe. Once you have problems with explorer hanging, I've found that it's time to start dividing the files into smaller directories. Kurt On Fri, Aug 5, 2011 at 10:53, Webster webs...@carlwebster.com wrote: I did some work for a bank processing company one time where they processed all the checks, deposits, etc for numerous banks. The software they used put everything nice and neat in a very organized folder system with files for everything processed for that day in a separate folder. After 2 months the system was begging for mercy. When they scanned in an item, the item was turned into a front and back GIF and JPG and PNG and BMP and then a PDF of all the above. Process a small bank’s daily intake of say 5,000 items and each folder would contain around 45,000 files. Multiply that by 6 work days a week, 4 (or so) weeks month times 2 months and then all this for around 50 banks. The file servers was begging for mercy. After the first week on the new software it was taking 30+ hours to do a daily backup so they went to a weekly backup. The backup started at 10PM Friday night and was not finished by 8AM the following Monday. As a former app dev guy, I always wondered why they didn’t just use SQL, only the PNG image type and build a PDF on an as needed basis. The processing company was sold after 4 months to a company that moved them to an AS/400 system where everything was scanned in and stored in a database. With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. Carl Webster Consultant and Citrix Technology Professional http://www.CarlWebster.com From: Roger Wright [mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
Re: SQL for File Storage?
Thanks, all. Sounds like SQL isn't the problem I imagined and may actually be the solution. Roger Wright ___ Dr. Seuss is my favorite rapper! Cat - Hat... sheer genius! On Fri, Aug 5, 2011 at 2:48 PM, Kurt Buff kurt.b...@gmail.com wrote: I've experienced problems with directories that have as few as 10k files in them. This usually manifests itself first as problems browsing the directory with explorer.exe. Once you have problems with explorer hanging, I've found that it's time to start dividing the files into smaller directories. Kurt On Fri, Aug 5, 2011 at 10:53, Webster webs...@carlwebster.com wrote: I did some work for a bank processing company one time where they processed all the checks, deposits, etc for numerous banks. The software they used put everything nice and neat in a very organized folder system with files for everything processed for that day in a separate folder. After 2 months the system was begging for mercy. When they scanned in an item, the item was turned into a front and back GIF and JPG and PNG and BMP and then a PDF of all the above. Process a small bank’s daily intake of say 5,000 items and each folder would contain around 45,000 files. Multiply that by 6 work days a week, 4 (or so) weeks month times 2 months and then all this for around 50 banks. The file servers was begging for mercy. After the first week on the new software it was taking 30+ hours to do a daily backup so they went to a weekly backup. The backup started at 10PM Friday night and was not finished by 8AM the following Monday. As a former app dev guy, I always wondered why they didn’t just use SQL, only the PNG image type and build a PDF on an as needed basis. The processing company was sold after 4 months to a company that moved them to an AS/400 system where everything was scanned in and stored in a database. With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. Carl Webster Consultant and Citrix Technology Professional http://www.CarlWebster.com From: Roger Wright [mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
Re: SQL for File Storage?
I think this was more of an issue in versions of SQL prior to 2005. YMMV :) -Jeff Steward On Fri, Aug 5, 2011 at 4:31 PM, Roger Wright rhw...@gmail.com wrote: Thanks, all. Sounds like SQL isn't the problem I imagined and may actually be the solution. Roger Wright ___ Dr. Seuss is my favorite rapper! Cat - Hat... sheer genius! On Fri, Aug 5, 2011 at 2:48 PM, Kurt Buff kurt.b...@gmail.com wrote: I've experienced problems with directories that have as few as 10k files in them. This usually manifests itself first as problems browsing the directory with explorer.exe. Once you have problems with explorer hanging, I've found that it's time to start dividing the files into smaller directories. Kurt On Fri, Aug 5, 2011 at 10:53, Webster webs...@carlwebster.com wrote: I did some work for a bank processing company one time where they processed all the checks, deposits, etc for numerous banks. The software they used put everything nice and neat in a very organized folder system with files for everything processed for that day in a separate folder. After 2 months the system was begging for mercy. When they scanned in an item, the item was turned into a front and back GIF and JPG and PNG and BMP and then a PDF of all the above. Process a small bank’s daily intake of say 5,000 items and each folder would contain around 45,000 files. Multiply that by 6 work days a week, 4 (or so) weeks month times 2 months and then all this for around 50 banks. The file servers was begging for mercy. After the first week on the new software it was taking 30+ hours to do a daily backup so they went to a weekly backup. The backup started at 10PM Friday night and was not finished by 8AM the following Monday. As a former app dev guy, I always wondered why they didn’t just use SQL, only the PNG image type and build a PDF on an as needed basis. The processing company was sold after 4 months to a company that moved them to an AS/400 system where everything was scanned in and stored in a database. With a lot of files, and especially a lot of small files, Windows is VERY inefficient. Move into the millions of files and the Windows file system is not where I would want to store stuff. Carl Webster Consultant and Citrix Technology Professional http://www.CarlWebster.com From: Roger Wright [mailto:rhw...@gmail.com] Sent: Friday, August 05, 2011 12:38 PM To: NT System Admin Issues Subject: SQL for File Storage? We have a company pitching a project management solution that stores all project data, including photos, docs, etc. in SQL. I'm leary because most of our projects have about 200 MB of related files and I'm envisioning huge databases. Should I be, or is SQL more ideal for this type of application than I suspect? Roger Wright ___ Bozone (n.): The substance surrounding stupid people that stops bright ideas from penetrating. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
Re: SQL for File Storage?
On Fri, Aug 5, 2011 at 2:48 PM, Kurt Buff kurt.b...@gmail.com wrote: Once you have problems with explorer hanging, I've found that it's time to start dividing the files into smaller directories. Hell, I've had Explorer hang on empty folders... ;-) One more reason to use the command line. :) -- Ben ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
Re: SQL for File Storage?
On Fri, Aug 5, 2011 at 14:02, Ben Scott mailvor...@gmail.com wrote: On Fri, Aug 5, 2011 at 2:48 PM, Kurt Buff kurt.b...@gmail.com wrote: Once you have problems with explorer hanging, I've found that it's time to start dividing the files into smaller directories. Hell, I've had Explorer hang on empty folders... ;-) Heh. One more reason to use the command line. :) Well, that's my default, of course... Kurt ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/ ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
