I won’t complain about yet-another-configuration-stored-procedure, since I hold out hope that managing FILESTREAM’s status at the server level will be rolled into sp_configure or DDL before RTM… but this is me not holding my breath. *sigh*
Anyway, if you want to use FILESTREAM data on your SQL Server 2008 instance (hopefully with the November 2007 CTP (aka CTP5), you should check the readme for some of the little gotchas. Then you’ve got to turn it on (Books Online for CTP5 makes this pretty obvious if you look):
sp_filestream_configure [ [ @enable_level = ] level ]
[ [ , @share_name = ] ‘share_name’ ] ;
Seems easy enough… (Hint: you want @enable_level = 3.) Unless you’re running Windows Vista and if you don’t remember to launch your SQL Server Management Studio 2008 (SSMS) elevated. If you forget (like I do occasionally – until I remember to flip the run-as-administrator bit on the shortcut) then you’ll get this error (or one just like it):
(local)(MYDOMAIN\myusername): Msg 5587, Level 14, State 1, Procedure sp_filestream_configure, Line 1
You do not have permissions to configure FILESTREAM feature. You need Windows Administrator and sysadmin rights to configure FILESTREAM feature.
Whoops. I’m an administrator, but UAC forgot to tell SQL Server that (on purpose). Doh. Elevate! Either right-click and Run as administrator or change your shortcut to do the same.
Why am I warning you about this? Because we’re planning to ship AdventureWorks2008 with some FILESTREAM data in it and if you want an easy way to demo FILESTREAM, AdventureWorks2008 is it! No promises on exact features to be included in anything, including AdventureWorks2008, so [Insert standard Microsoft pre-RTM disclaimer here]. Caveat downloader.