Friday, June 13, 2008

SQL Server 2008 (RC0), FILESTREAM changes

At this year's Microsoft NT Conference in Portorož fellow MVP Andrej Tozon and I have presented a workshop on designing solutions with Visual Studio 2008 and SQL Server 2008 using the Windows Presentation Foundation and a few new SQL Server 2008 features. Andrej has blogged about this last month and has made all the workshop files available on his site.

You might also already know that SQL Server RC0 has been released on June 10th, which has been announced in the SQL Server documentation team blog – amongst several other places. Of course, a new version of Books Online is also available.

Why all these old news now?

There have been a few changes in RC0 regarding the FILESTREAM feature that I feel I should mention here – given the fact that as a consequence I'll also have to amend the workshop files. However, I think I'd better delay any changes until RTM has been released. Who knows what else I'll have to change... ;)

Long story short, the changes refer to the way FILESTREAM is enabled, and the new procedure is described in the current version of Books Online:

In addition, there's currently one more problem regarding the use of SQL Server Configuration Manager when enabling FILESTREAM. It's been reported in the SQL Server Storage Engine blog:

Well, that's about it. For now. But be sure to go through all the Books Online articles regarding FILESTREAM before attempting to use it in RC0 (in case I missed something).



Leonard Lobel said...

Hi, My name is Leonard Lobel and I'm the lead author of the new MS Press book "Programming Microsoft SQL Server 2008".

I had already writting the chapter on FILESTREAM against CTP6 and earlier, and now have come to realize that I need to update the chapter as the process of enabling FILESTREAM at the server instance level has changed. However, I find the nature of the change confusing, and was hoping you could shed some light on it for me.

In CTP6 (and earlier), only *one* step was required to enable FILESTREAM, and that step could be executed from SSMS (server properties dialog) or in script (by calling sp_filestream_configure). In either case, you could choose from *four* different levels (0=disabled, 1=enabled for T-SQL only, 2=enabled for local file system access, 3=enabled for remote file system access).

Now, in RC0, it appears that *two* steps are required to enable FILESTREAM. First, you need to enable it in Configuration Manager using checkboxes that appear to correspond to the four-levels previously associated with sp_filestream_configure (as far as I can tell, there is no way to script this). You must then perform the second step to enable FILESTREAM. As with CTP6, this can be done either in SSMS (server properties dialog) or in script (by calling sp_configure filestream_access_level; the sp_filestream_configure stored proc no longer exists).

In either case, this second step requires you to select from one of *three* different levels (0=disabled, 1=enabled for T-SQL access only, 2=enabled for Win32 streaming access). The highest level does not draw a distinction between local and remote file access).

So, if you could please answer these questions, I'd greatly appreciate it:

1) Why do we need to set the level TWICE now... once in Configuration Manager, and again in either SSMS or via sp_configure filestream_access_level?

2) What's the meaning of separate 4-level (Configuration Manager) and 3-level (SSMS/sp_configure) settings for FILESTREAM access?

Many thanks, in advance...

~ Lenni

Matija Lah said...

What can I say?

You'd be much better off asking the vendor - I can guess, but I prefer not to.

After all, this is only RC0. I suggest waiting for the RTM. I know I will.


Matija Lah said...

I think this post by Bob Beauchemin explains the change very well: