If it was SQL2008 : Any views/experience on the comparative performance of FILESTREAM ?

I avoided Filestream because Kevin was asking about 2005. When I first looked at Filestream I had a pretty negative reaction, because of some initial misleading information about configuring it. I haven't used it in production, but I built a demo just so I can understand it.

It's certainly a big improvement over storing images in a database in 2005, but just like any 1.0 feature, there are some issues:

- You can't configure DB mirroring on databases with FS data

- Unlike other SQL data, FS data can't be encrypted

- Doesn't support database snapshots

- Can't use them in temp tables

- This one I haven't confirmed, but supposedly you can only store FS data on local disk volumes

- If you use transaction replication, all subscribers must be using 2008 (makes sense)

- Biggest of all, FS introduces issues in situations where images are updated frequently. You can't do in-place updates of FS data. When you try to change data in an FS column, a new zero-byte file gets created. There's an asynchronous job that fires of a garbage collector to remove the old file. But for some period of time, the discarded files stay around. (SQL 2008 has a number of new operations that run asynchronously). Becuase the collector doesn't run continuously, full backups can contain "garbage files". If images aren't updated frequently, this isn't a problem. (Even Books Online states that FileStream isn't ideal for data that gets updated frequently)

So even though FS is better than what existed before, I still maintain that the best overall solution is to keep this data out of the database. Quite honestly, it doesn't belong in there.