FILESTREAMING SQL 2008 R2
in [Programming SQL]
|
Prev: Covering index
Next: FILESTREAMING SQL 2008 R2
From: Geoff Schaller on 12 May 2010 21:53 What doesn't "fit" is your advice regarding 256KB or 1MB as some kind of size constraint. Sharepoint, just to pick one example, will quite happily deal with documents and files of any size and it does not use filestreaming or any kind of trickery to save binary data into columns. This is a Microsoft product and I have not seen any advice from MS that there is such a genuine practical limitation. We save documents into columns also - I place no restriction on size - and although if I look at a database, the vast majority are well below 200KB, some do go up to 10MB. Particularly power points. We have not noticed any particularly problems. So I don't agree. I am just not happy with the physical concept of filestreaming nor the way it is architected so I have gone back to basics. It is simpler to install and manage and move around. Cheers, Geoff Schaller Software Objectives "Plamen(a)SQLStudio.com" <Plamen(a)SQLStudio.com> wrote in message news:alkmu5h59vv58j8ocmpmesh0uo98trmpa9(a)4ax.com: > What doesn't fit? Or you find the white paper by Jim Gray and his > colleagues wrong? I very much doubt that... > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Erland Sommarskog on 13 May 2010 08:38 Geoff Schaller (geoffx(a)softxwareobjectives.com.au) writes: > Sharepoint, just to pick one example, will quite happily deal with > documents and files of any size and it does not use filestreaming or any > kind of trickery to save binary data into columns. This is a Microsoft > product and I have not seen any advice from MS that there is such a > genuine practical limitation. You are confusing things. You can store 2GB in a BLOB column in SQL Server. That is perfectly possible. But that is not the same as this is the most optimal solution. In fact, it had been, Microsoft would not have added FILESTREAM to SQL Server. FILESTREAM achieves two things: it removes the limit of 2GB and it makes access to larger BLOBs faster. Microsoft says you can expect better performance when the size of the BLOB data generally exceeds 1MB of data. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Plamen Ratchev on 13 May 2010 12:39 I deal with few databases that heavily utilize BLOB storage both in DB and file system and from my experience the points made in the white paper I posted are very valid. Sharepoint is by far not near an "ideal" example for content storage. -- Plamen Ratchev http://www.SQLStudio.com
From: Geoff Schaller on 14 May 2010 22:48 Ok, so I wonder then if we should investigate more the actual scenario required? One problem we hit immediately was then need for a single solution for a column. Filestream involved additional over head, came with restrictions on column usage and a more complicated install and management environment. It was just easier to go back to a non-filestream solution. Also the vast majority of docs and images were below 500KB. But quite a lot are not. The other thing is that white paper was written a long time ago (in computer terms) and a lot has happened to storage engine in this sphere. It was definitely pre SQL Server 2008. All I can tell you is that in a live commercial instance we employ, we don't have the problems alluded to. Geoff Schaller Software Objectives "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D7794E0BD8F0Yazorman(a)127.0.0.1: > Geoff Schaller (geoffx(a)softxwareobjectives.com.au) writes: > > > Sharepoint, just to pick one example, will quite happily deal with > > documents and files of any size and it does not use filestreaming or any > > kind of trickery to save binary data into columns. This is a Microsoft > > product and I have not seen any advice from MS that there is such a > > genuine practical limitation. > > > You are confusing things. You can store 2GB in a BLOB column in SQL > Server. That is perfectly possible. But that is not the same as this > is the most optimal solution. In fact, it had been, Microsoft would > not have added FILESTREAM to SQL Server. FILESTREAM achieves two things: > it removes the limit of 2GB and it makes access to larger BLOBs faster. > Microsoft says you can expect better performance when the size of > the BLOB data generally exceeds 1MB of data. > > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Capri on 15 May 2010 07:49
I have decided that i will not use filestreaming, I will save image in a folder and will save path in a table field. Thanks for all "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:okaou55el78kq9fbnksmi4e9qrdu0jr4uo(a)4ax.com... >I deal with few databases that heavily utilize BLOB storage both in DB > and file system and from my experience the points made in the white > paper I posted are very valid. Sharepoint is by far not near an > "ideal" example for content storage. > > -- > Plamen Ratchev > http://www.SQLStudio.com |