FILESTREAMING SQL 2008 R2
in [Programming SQL]
|
Prev: Covering index
Next: FILESTREAMING SQL 2008 R2
From: Erland Sommarskog on 15 May 2010 11:47 Capri (NoEmail(a)NoDomain.com) writes: > I have decided that i will not use filestreaming, > > I will save image in a folder and will save path in a table field. That's a solution that seems simple at first. But in fact it is a very complex one. You have the challenge of consistency. You can't do a two- face commit between database and file system. Someone may delete a file or do other bad things when fiddling around in the directory. Add to that backup/restore will be a nightmare. I think a regular BLOB column is to prefer, if you don't like FILESTREAM. -- 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: Geoff Schaller on 15 May 2010 21:08 Hi Nameless, No, this is NOT a good solution. I agree with Erland here in that you now have the worst possible implementation. Whether you choose Filestreaming or internal column, the issue is that when you back up a database, everything is backed up. When you back up your way you run the risk of inconsistencies because you have to guarantee the file store being in synch with the SQL Server backup. A further problem is that nothing prevents users adding, editing or deleting items in the file store independent of the reference you have in your DB. This is just a recipe for disaster. At least the other way you have genuine content integroity. Geoff Schaller Software Objectives "Capri" <NoEmail(a)NoDomain.com> wrote in message news:73CED582-8C3C-406B-9B7E-B6888382DFC0(a)microsoft.com: > 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
From: Kalle Olavi Niemitalo on 16 May 2010 04:24 Erland Sommarskog <esquel(a)sommarskog.se> writes: > You can't do a two-face commit between database and file system. I think you mean two-phase commit. In Windows Vista (and presumably Windows Server 2008), I think you can already use Transactional NTFS and SQL Server in the same DTC transaction; although perhaps the commit will then have more than two phases. IKernelTransaction: http://msdn.microsoft.com/library/aa344210.aspx
From: Erland Sommarskog on 16 May 2010 05:04
Kalle Olavi Niemitalo (kon(a)iki.fi) writes: > Erland Sommarskog <esquel(a)sommarskog.se> writes: > >> You can't do a two-face commit between database and file system. > > I think you mean two-phase commit. Oops! I think I lost phase there. :-) > In Windows Vista (and presumably Windows Server 2008), I think you can > already use Transactional NTFS and SQL Server in the same DTC > transaction; although perhaps the commit will then have more than two > phases. Hm, but it's not exactly trivial is it? There is a lot of restrictions in SQL Server with distributed transactions. And getting MSDTC to work can be a nightmare. -- 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 |