There has always been a disconnect for storing unstructured data (e.g. documents, media files) in relational databases.  These were big blobs of binary data that couldn't be organized appropriately inside a database (or at least in a way that could be considered optimal).  So, most the time we stored those items in a file system outside of the database, and then simply stored pointers to those files inside the database.  Although this solution works, it is a bit awkward ... because the database isn't self-contained.  Since it refers to those other files, they could be thought of as "part of the data" in the database ... but those files are outside of the control of the database.  When an administer makes a backup of the database ... that isn't really all the data.  Also, if you move the file to another location ... you have to remember to update all of the paths in the database that point to that file.  SQL Server 2008 makes an attempt to bridge this disconnect with a new FILESTREAM data type. 

With this data type, files can still be stored outside of the database, but the data is considered part of the database for transactional consistency. This allows for the use of common file operations while still maintaining the performance and security benefits of the database." - TechNet Magazine - April 2008

You can pass the binary data to the SQL Server, and insert it similar to how you could already do for the existing VARBINARY or IMAGE data types.  However, instead of saving the bits with the rest of the structured, relational data ... SQL pretty much does the same thing we were doing before (i.e. saving the data somewhere on the file system and storing a pointer to the file), but completely takes care of it for us.  It saves all of the binary data in file containers in a special, hidden directory.  It also goes one step further ... only the account under which the SQL Server service account runs is granted NTFS permissions to the FILESTREAM container.

MSDN suggests that you use the FILESTREAM data type when you need fast read access on files larger than 1MB.  If the file is smaller than that size, you will probably get better performance just using VARBINARY data types which store the data in-line with the structured, relational data.  But, I can already see how this new data type is just one of the new features in SQL Server 2008 that will make my life easier.  I can already see how it could used for content management systems.  Such systems often save a lot of unstructured binary data like documents, images, videos, etc. that can be in excess of 1MB ... so the FILESTREAM data type seems like an obvious fit. 

For more info on this new data type, check out this documentation from SQL Books Online.