OK, back to PathName() with AlwaysOn, which I started on in the previous blog post. PathName() in SQL Server 2012 doesn't return the computer name by default ("AlwaysOn1" or "AlwaysOn2" in my example) but returns the VNN name (virtual network name). That is, it returns the availability group "listener share" name. In my case, the PathName() would start with \\AlwaysOnAG1. There's an additional option in SQL Server 2012 PathName(), that allows you to return the current replica name. So, when "AlwaysOn1" is the current primary replica, it returns that; when we fail over to "AlwaysOn2", that's what name is returned. In addition, GetFileNamespacePath() and FileTableRootPath() always return the availability group listener name, they don't have an option to return the current replica name.

Finally, I'll discuss using AlwaysOn, FilePath(), T-SQL access and secondary replicas.In theory, ADO.NET 4.02 (and, of course, SQLNativeClient 11ODBC/OLE DB and Microsoft JDBC 4.0 driver) allows you to declare your ApplicationIntent as ReadOnly or ReadWrite. ReadWrite is the default. Adding either ApplicationIntent=ReadOnly or ApplicationIntent=ReadWrite in the SSMS Connection dialog is accepted, but either one allows connection to the secondary replica. This is because, by default, secondary replicas are configured with "Readable Secondary=Yes" rather than "Readable Secondary=Read-intent". Configuring a secondary as "Readable secondary=Read-Intent" disallows connections that do not specify ApplicationIntent=ReadOnly in the connection string. Obviously, connecting as "ReadWrite" to a ReadOnly replica errors out if you try and do something other than read. Check out Sunil's excellent blog post on the motivations for these secondary replica settings.

Now that we're connected (in T-SQL) to the secondary replica, let's try and get the PathName() from here:

SELECT Name, file_stream.PathName() FROM NWTab;

Msg 33447, Level 16, State 1, Line 1
Cannot access file_stream column in FileTable 'NWTab', because FileTable doesn't support row versioning. Either set transaction level to something other than READ COMMITTED SNAPSHOT or SNAPSHOT, or use READCOMMITTEDLOCK table hint.

Now, this was surprising...I checked the snapshot info in sys.databases and also DBCC USEROPTIONS. Neither snapshot isolation level was turned on or being used. And setting the iso level to, say, repeatable read or even the dreaded read uncommitted didn't help. But using the READCOMMITTEDLOCK did. Hmmm...

But of course, Sunil had the answer for this one too..."To avoid this blocking completely, all transaction isolation levels used in an application on the secondary replica are mapped transparently to Snapshot Isolation..." And this behavior is more obliquely documented in the BOL, here. But Sunil's blog post does go on to say "You may ask how about locking hints? Well, all locking hints are ignored." But the READCOMMITTEDLOCK locking hint does work in this case (modulo the effects it might have on performance, as Sunil mentions). So at this point I wonder if the READCOMMITTEDLOCK locking hint on a FileTable has a special dispensation (other locking hints, do, in fact, fail to have the desired effect), but I'll need to revisit this in 2012 RTM. BTW, it's only using the file_stream column of the FileTable that causes this, other columns work fine. But PathName() and GetFileNamespacePath() are methods on the column, so you can't use these, or access the column in T-SQL, on readonly secondaries without the hint.

Back to BOL comment, changing the NON_TRANSACTED_ACCESS to READ_ONLY (on the primary replica) changes the behavior on the secondary as promised, but now I can't write to the FileTable using the file system. As expected.

Two more things to mention. Firstly, Msg 33447 isn't a mistaken error message. You can, of course, attempt to access the file_stream column in a FileTable from the *primary* replica using Snapshot isolation. Then you'll receive the error, and changing the iso level WILL help. When enabling read-committed snapshot or snapshot you DO get this useful (but slightly misleading, IMHO) message on the ALTER DATABASE DDL statement: "When the FILESTREAM database option NON_TRANSACTED_ACCESS is set to FULL and the READ_COMMITTED_SNAPSHOT or the ALLOW_SNAPSHOT_ISOLATION options are on, T-SQL and transactional read access to FILESTREAM data in the context of a FILETABLE is blocked.". That's not quite true; if the options are on and you *USE* snapshot iso levels, you'll get the error. Changing the iso level to a non-snapshot iso level or using the READCOMMITTEDLOCK hint WILL succeed on the primary.

Finally, remember that "ordinary" (i.e. non-FileTable) tables containing filestream columns DO support snapshot isolation levels as of SQL Server 2008 R2. It should be only the FileTable, with its non-transacted access, that has this behavior with snapshot isolation on the primary and readonly secondaries. And, in fact, this does test out to be the case, an "ordinary" filestream column works fine, even in the same database.

Enough? ;-) I even turned moderated blog comments back on (against my better judgement, the amount of spam comments is amazing), if this is a useful discussion vehicle. Or, you know where to find me... Cheers.

@bobbeauch

When I was covering the FileTable-specific functions and methods, I didn't mention PathName(), a filestream-related function, that (naturally) can be used with FileTables. This posting is about PathName(), but, mostly about what happens with FileTable in an AlwaysOn availability group configuration. There were some surprises there. None of the FileTable-specific functions and methods mention AlwaysOn, but PathName() mentions it. There is an additional parameter you can specify that matters if you use PathName() in an AlwaysOn availability group environment.

Remember that, in addition to FileTable, there have been enhancements to filestream in SQL Server 2012. Namely, you can have multiple filestream containers (filespecs) per-filestream filegroup. This has an effect on the paths returned by PathName(). In SQL Server 2008/R2, PathName() returns a path that looks something like this:

\\ZMV08\MSSQLSERVER\v1\Northwind\dbo\Employees2\Photo\DF707B77-9FA4-4837-91C6-363E23BCEBDD.

In SQL Server 2012, an analogous request would produce a path that looks like this:

\\ZMV04\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\FTTest\dbo\Documents\file_stream\D46814E2-633D-E111-982F-000C2959648F\VolumeHint-HarddiskVolume1.

So, for at least one reason, there's a new "version" of filestream namespace names (I'm just looking at the v01 vs. v02 part of the pathname).

So let create an AlwaysOn Availability Group setup to try it out with FileTable and PathName(). Availability group is a new feature in SQL Server 2012 that appears, at first glance, to be an big extension to the database mirroring feature. An availability group can contain multiple databases, and up to 4 secondary replicas, in addition to the primary replica. The secondary replicas can be (optionally) read-only. Using availability groups requires using Windows Failover Clustering. And, perhaps because of this, availability groups support filestream storage (and by extension, FileTables). Availability groups also can have "listeners", separate endpoints that abstract connecting to an availability group.

So I've defined two SQL Server instances, each a default instance. On machines named "AlwaysOn1" and "AlwaysOn2" (disclaimer: I never proported to have an imagination for making up names). My availability group is named "AlwaysOnAG1" and encompasses the pubs and northwind database. 'Cause we all know sample databases need to be highly available. ;-) For reference, the availability group is using synchronous-commit mode and is set up for automatic failover (which means it could do automatic, manual, or even forced failover, if need be. Both nodes are configured to be read-only when they are the secondary node. I also have a listener set up named "AlwaysOnAG1" that has a hardcoded IP and listens on port 7001.

I've decided to put a FileTable on Northwind. And, from a previous experiement, I have a FileTable on a database named "FTTest". This database does NOT participate in the availability group and only exists on the "AlwaysOn1" instance, not on "AlwaysOn2". But it produced one of the surprises.

After enabling Northwind for FileTables (AlwaysOn1 is the current primary), I define a FileTable named "NWTest". With this configuration:
 --When SSMS connected to "AlwaysOn1"
    --The share opens from SSMS Object Explorer
    --Machine "AlwaysOn2" has an MSSQLServer share, but nothing is visible underneath.
    --Using the share \\AlwaysOn1\mssqlserver, I see both Northwind's filetable and FTTest's filetable.
 

--When SSMS connected to "AlwaysOn2"
    --Machine "AlwaysOn2" has an MSSQLServer share, but nothing is visible underneath.
    --Attempting to open the share from ObjectExplorer AlwaysOn2 node produces error: "The file location cannot be opened. Either access is not enabled or your do not have permission for the same."

--When Connected to the "listener share" (i.e. \\AlwaysOnAG1\mssqlserver) I see both Northwind's filetable and FTTest's filetable. This was surprising to me because FTTest is not a member of the availability group and I'm using it's "listener share" name. I can see both FileTables even when I open the listener share from a different machine on the network (like the "AlwaysOn2" machine). Just an observation...

The file system and SMB are more tolerant of temporary outages than SQL Server connections. But that's a subject for another blog post. So, using the "listener share" I create open/edit close Somefile.txt in Notepad. Now, open the file again. Make some changes in the text. Now failover the availability group from "AlwaysOn1" to "AlwaysOn2"...

During failover, I get the "Network resource unavailable" message for my Notepad. Then, as failover completes, the message goes away. Now I can save my changes. Without incident.

So, after failover, what do things look like?
--When SSMS connected to "AlwaysOn1" (now secondary)
   --The share receives an error from SSMS Object Explorer.
   --Machine "AlwaysOn2" has an MSSQLServer share, with Northwind FileTable subdirectory.
   --Using the share \\AlwaysOn1\mssqlserver, I see FTTest's filetable (this database is not in the AG, remember), but not Northwind's FileTable subdirectories.
 

--When SSMS connected to "AlwaysOn2"(now primary)
   --The share opens from SSMS Object Explorer
   --Using the share \\AlwaysOn2\mssqlserver, I see Northwind's FileTable subdirectories/files.
 

--When connected using the listener share
   --I see only Northwind's FileTable subdirectories/files.

Notice that this is only observations with one specific configuration of the availability group. No guarentees as to what happens with other configurations.

OK, long-ish post again. But this post began with discussion of PathName() method on the filestream column. I'll conclude with that next.

@bobbeauch

So finally, we get to using FileTable's path_locator column with the methods of hierarchyid. To pick up where we left off, remember that hierarchyid has a method named GetLevel(). Given the following directory structure in the FileTable's share:

File1.txt
File2.txt
SQLFiles
  SubdirFile1.txt
  SubdirFile2.txt

I have a few more levels of subdirectory here, but hopefully, you get the idea. Issuing the query

SELECT path_locator.GetLevel() as Level, Name
FROM dbo.Documents;

show, unsurprisingly, File1.txt, File2.txt, and SQLFiles directory at level 1, and the subdirectory files at level 2. So to be sure we're getting the "right" SQLFiles directory, we could change that query in the previous blog entry to:

SELECT @pathstring = path_locator.ToString() FROM dbo.Documents WHERE Name = 'SQLFiles' AND path_locator.GetLevel() = 1;

Note also that, the way the FileTable namespace works, the dbo.Documents directory is the root of the hierarchy. We can prove it using hierarchyids.

-- Level Number of table is 0. Table PathLocator is root
SELECT FileTableRootPath('dbo.Documents') as RootPath,
       GetPathLocator(FileTableRootPath('dbo.Documents')) as Path,
       GetPathLocator(FileTableRootPath('dbo.Documents')).GetLevel() as Level;

As long are we're on that subject, let's use the rest of the hierarchyid methods on the FileTable as well.

-- an interesting way to get all direct children of the top-level SQLFiles directory node
SELECT p.name as ParentName, c.name as ChildName, c.is_directory,
 c.file_stream.GetFileNamespacePath() as ChildPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON p.name = 'SQLFiles'
AND p.path_locator.GetLevel() = 1
AND c.path_locator.GetAncestor(1) = p.path_locator;

-- but we have a persisted computed column for that...
SELECT p.name as ParentName, c.name as ChildName, c.is_directory,
 c.file_stream.GetFileNamespacePath() as ChildPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON p.name = 'SQLFiles'
AND p.path_locator.GetLevel() = 1
AND c.parent_path_locator = p.path_locator;

How about all children of that node, at any subdirectory level?

-- child of the top-level SQLFiles node, any
SELECT p.name as ParentName, c.name as ChildName, c.is_directory,
 c.file_stream.GetFileNamespacePath() as ChildPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON p.name = 'SQLFiles'
AND p.path_locator.GetLevel() = 1
AND c.path_locator.IsDescendantOf(p.path_locator) = 1;

And of course, wind our way up the tree:

-- parents of a specific node
SELECT p.name as ParentName, c.name as ChildName, p.is_directory,
 p.file_stream.GetFileNamespacePath() as ParentPath
FROM dbo.Documents p
JOIN dbo.Documents c
ON c.name = 'SQLSubdirSomeText.txt'
AND c.path_locator.IsDescendantOf(p.path_locator) = 1;

And, we can see the obvious use of GetReparentedValue. It moves a file from one subdirectory to another. You can't just set parent_path_locator because that's a computed column.

DECLARE @oldpath hierarchyid, @newpath hierarchyid;
SELECT @oldpath = path_locator from documents where name = 'SQLServerSubdir' AND path_locator.GetLevel() = 2;
SELECT @newpath = path_locator from documents where name = 'SQL Server' AND path_locator.GetLevel() = 1;
UPDATE dbo.Documents SET path_locator = path_locator.GetReparentedValue(@oldpath, @newpath)
WHERE name = 'SubSyntheticFile2.txt';

Enjoy!

@bobbeauch

So the functions/methods that I wrote about in previous post are needed because FileTables don't store the UNC path name of the file, they store the path_locator as a hierarchyid data type. Wonder what encoding scheme they're using. Let's see, by doing

SELECT path_locator.ToString(), Name
FROM dbo.Documents

We get hierarchyid strings that look like this: "/192992825631153.73945086322524.2119705196/" Turns out that the encoding scheme involves newid() as you can see by looking at the definition for the default constraint for the path_locator column. It looks like this:

convert(hierarchyid, '/' +    
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/')

In fact, almost all of the columns in a FileTable have defaults or are computed columns. So, to create a row in a FileTable, let's say, a file named "Testfile1.txt" in the root of the file share, all that's required is:

INSERT INTO dbo.Documents(Name, file_stream) VALUES('Testfile1.txt', 0x);

But, if I have a directory named "SQLFiles" at the root? How do I create a file in that directory? You might think parent_path_locator, but that's a computed column. Well, how about...

SELECT @pathstring = path_locator.ToString() from documents where name = 'SQLFiles';
SET @newpath = @pathstring +  convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.'
                           +  convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.'
                           +  convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/';

INSERT INTO dbo.Documents(Name, path_locator, file_stream) VALUES('SQLFilesTest.txt', @newpath, 0x);

You could also just use "@pathstring + '1/'" but this sticks to the "native" encoding scheme. And no, newid() can't be used in a function, if that's what you're thinking.

Now that I've got a zero-length file, can I open it and edit it with Notepad.exe? Well, you can if its empty. But once there is data in the file, attempting to edit with Notepad.exe returns an error, "This request is not supported". This is because Notepad.exe uses memory-mapped files, a win32 feature which isn't supported by FileTable. Paint.exe uses them too. So you'd need to open it from a remote location. Actually, connecting the share as a network drive will work as well.

But what if there are directories named "SQLFiles" at different subdirectory levels? How can we distinguish between them? That's where the hierarchyid comes in. I'll continue with that next.

@bobbeauch

I've been working with the SQL Server 2012 FileTable feature lately. Besides learning to appreciate the esoteric features of the NTFS file system and SMB protocol, only some of which are supported by FileTables, I've been trying to work with FileTables in SQL Server using T-SQL. This turns out to be an interesting exercise, especially if you're trying to brush up on your skills with the hierarchyid data type.

It turns out that a FileTable is just like a normal SQL Server table with a filestream varbinary(max) column (named, unsurprisingly, file_stream). It uses computed columns and constraints rather extensively. Behind the scenes, SQL Server functions as a Win32 namespace owner and exposes a virtual file share. Although the namespace management and fitting non-transactional access into a transaction-based system, and its easy to think of the share as a real file share, SQL Server manages everything. And you can manage the FileTable data completely with T-SQL. This one's for the folks that think *everything* ought to be managed in T-SQL. But before trying to create and manage files with T-SQL, I looked into the FileTable specific functions, GetFileNamespacePath(), FileTableRootPath(), and GetPathLocator(). These will be useful and they have some interesting options.

GetFileNamespacePath() is exposed as a method on the file_stream column.Besides being used without options, it has two options. The first option allows you to get a full namespace path instead of a relative path. Relative path is the default. So if, for example, you have a FileTable named "Documents" on a database named FTTest on a machine named ZMV04, your full path for a file at the root named Testfile1.txt will be "\\ZMV04\MSSQLSERVER\FTTest\Documents\Testfile1.txt", and relative path would be "\Documents\Testfile1.txt". A second option lets you decide whether you want a NETBIOS name, Machine name, or Domain name in your path.

FileTableRootPath() is a global function. It produces slightly different names for the root depending on whether or not you use the first parameter, the name of the filetable. Without this parameter, FileTableRootPath() doesn't include the part of the path that includes the filetable. So, in our case its "\\ZMV04\MSSQLSERVER\FTTest". When the filetable name is specified this function returns "\\ZMV04\MSSQLSERVER\FTTest\Documents. This option makes a difference when using this method to construct full paths, as we'll soon see. There's a second option for path using NETBIOS name, Machine name, FQDN is analogous to GetFileNamespacePath().

Finally there is another global function, GetPathLocator(). This one was kind of strange, even though the use case provided by the BOL was clear enough. You'd use it if you have existing (pre-FileTable) SQL Server tables that use path locators. Path locators, if you're unfamiliar with the term, refer to storing a path name as a column value in a table. You'd use that name, stored in the database, to locate the file to open and read/write. The drawback to traditional path locators is that its up to you to keep the file location in the database in sync with the filesystem. And, of course, the files that file locators point to have no intergrated backup/restore with the database. With FileTables, management is taken care of by the database engine, along with coordinated backup and restore and other nice database features.

The twist is that FileTables don't directly expose file paths, although you can derive them using our first two functions. FileTables use the hierarchyid data type, stored in a column path_locator. The GetPathLocator() function can get you a path_locator (hierarchyid) given a valid string data type FileTable-produced path name. So, if I have a file "TestFile1.txt" in the root of a FileTable named "Documents", I can get its path locator hierarchyid by using GetPathLocator(file_stream.GetFileNamespacePath(1)). Of course, you can simply use the column value in the path_locator column, but this function may come in handy later on.

This posting has gone a little long, so I'll continue it in a subsequent posting.

@bobbeauch

SQL Server Denali's new Filetable feature and full-text search are made for each other. Set up database for non-transactional filestream access with a directory name, create a table "AS FILETABLE" with a subdirectory name, drag over a bunch of files and turn on full-text search. Voila, instant search with two predicates and two TVFs. Even better, if you add the keyword SEMANTIC_STATISTICS after your column name in the CREATE FULLTEXT INDEX statement you have three new semantic search table-valued functions as well (provided that they're supported in your language). Add a property list and you can search on documents' extended properties. What could be easier? Looks quite like the functionality of the long-forgotten WinFS, if you ask me (although I don't remember any Semantic Search in WinFS, just property search).

There's just one thing. Creating a fulltext index on using a FILETABLE uses an additional internal table the fulltext docidmap.The reason for this table is to map full-text's docid (an integer) to the the FTS index's key column. In the FILETABLE's case this would be the stream_id column, which as marked as a ROWGUIDCOL. CREATE FULLTEXT INDEX will use the ROWGUIDCOL column as a key column if you don't specify a key column. FTS uses the docid to do its lookup.

But...

In SQL Server 2008, an optimization was introduced if and only if the FTS index key column was an INT or BIGINT. In the case where FTS index key column is an INT/BIGINT, no docidmap table is created when the FTS index is. Better yet, this optimization does away with the docidmap lookup step in the query plan. This step which basically adds another JOIN to every FTS and every Semantic Search query, is simply removed, improving the plan.

Because the FILETABLE's FTS key column is a UNIQUEIDENTIFIER, it can't take advantage of this optimization. You can't add columns to a FILETABLE either, so this isn't an option. Darn. One last question...because a UNIQUEIDENTIFIER (GUID) has a  bigger value space than BIGINT, what happen's when you get more than VALUESPACE_OF(BIGINT) documents in your FTS/Semantic corpus? Guess I'd need to have over 18,446,744,073,709,551,616 documents to find out...wait, how many are on that C drive again?

@bobbeauch

As long as I'm blogging about filestreams...

Folks always want to know if the filestream information can be stored on a remote share. They look kind of disappointed when they hear that the filestream filegroup must exist on a local (to the SQL Server instance) drive. However... (isn't there always a however?)

For SQL Server 2008, the SQL Server team released a companion feature to filestream called remote blob storage (RBS). RBS consists of a set a stored procedures and an SDK that allows you to store your blob on a remote blob store. The SDK works differently than the filestream storage feature. And, to use the feature, you need a RBS blob store provider. Looking around, EMC² has an RBS provider for the Centera product line. And there's a sample provider and code to use it up on CodePlex.

The SQL Server team released the first version of RBS as part of the SQL Server 2008 Feature Pack.

They'd always said they'd be bridging the gap between filestream storage and RBS in future. We'll...the future is soon (or now, depending on how much you like CTPs). The November CTP of the SQL Server 2008 R2 Feature Pack comes with an RBS provider for Filestream. You still have to use the RBS APIs, but you can have your (filestream) cake and eat it too. Remoting. There's little info right now, but there are a number of postings on the RBS team's blog.  Including a comparison of filestream storage and RBS. In addition, RBS & filestream appears to hook into SharePoint 2010, but I'll leave the description of that to the SharePoint 2010 documentation (which has just been updated today).

And, BTW, if you were going to ask "Are features that appear in the SQL Server Feature Packs supported?", the answer is yes. I asked.

I was under the impression that the database engine/programming model changes in SQL Server 2008 R2 were minimal and could be counted on one hand. Today I was running an old demo that I had, having to do with filestreams on the R2 Nov CTP. Usually I run this one line-by-line, but I was in a hurry and ran the whole thing. Funny...I didn't remember this few errors caused by limitations when using filestream. Hmmm...

In SQL Server 2008 R2, filestream storage now support snapshots transaction isolation levels. Both flavors, read-committed snapshot and snapshot. This should expand the possibilities for using this feature because, in SQL Server 2008 (non-R2), you can't even enable either of these isolation levels at the database level if you have a filestream filegroup/column. Now, you can not only enable the levels, but the filestream goes exhibits the transactional semantics.

This is actually doc'd in the SQL Server 2008 R2 BOL, right here.  BTW, although the chart in the BOL lists the streaming access doesn't support ReadUncommitted, RepeatableRead, or Serializable iso levels, in my experiments, you don't use an error using these iso levels with streaming. The stream just doesn't exhibit the expected transactional semantics.

So you can count 'em on one hand, 'eh? Between this enhancement and the perf improvements blogged about recently on the SQL Server Storage Engine blog, maybe I'll need another hand.

It's a good idea when talking to a database to save on network roundtrips. The table-valued parameter in SQL Server 2008 is an example of a feature that can reduce them in the "1 order, 1-n detail items" use case.

So its always been mildly irritating that in order to insert a row with a filestream column, you'd need to make 2 database roundtrips. One roundtrip is to execute the INSERT statement, inserting an empty value in the filestream column. This causes the file to be created, a NULL value won't cause file creation, and you need the PathName to open a file handle or use the new SqlFileStream .NET class. The second roundtrip is used to get the FILESTREAM_TRANSACTION_CONTEXT and PathName. Then you have the info you need for the handle/SqlFileStream.

However, with the OUTPUT clause introduced in SQL Server 2005 you can do it all in one roundtrip. Here's the SQL statement, based on the BOL filestream example. You can run this code using an ADO.NET DataReader to retrieve the values, and construct a SqlFileStream instance to write to.

INSERT INTO dbo.student
OUTPUT Inserted.Resume.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
VALUES (newid (), 'Mary', CAST ('' as varbinary(max)))";

Of course, if you're writing a large value into a file anyway, the savings of one database roundtrip in the overall scheme of things is questionable, but using minimum roundtrips is a good habit to get into in any case.

I've been working with Filestream storage in SQL Server 2008 since it appeared in CTP5. The way I've always set it up is to use sp_filestream_configure. During the CTP6 setup process, I noticed you could now configure Filestream as part of setup. Because I knew how to use sp_filestream_configure I skipped that part of setup. And everything just worked as expected when I used it. A friend of mine, not wanting to miss anything, configured Filestream as part of setup. When he tried to use it, the following "Catch 22-like error messages occurred":

EXEC sp_filestream_configure @enable_level = 3;

"The FILESTREAM feature is already configured to the specified level. No change has been made."

CREATE DATABASE ... with a filegroup for FILESTREAM

Msg 5591, Level 16, State 1, Line 1
FILESTREAM feature is disabled.

Huh? After checking the short list of usual suspects (e.g. NTFS file system, running SSMS as admin under Vista) we were both puzzled. Especially because it "worked for me" as it always had. There's nothing more frustrating to hear during problem resolution then "Well, I'm not sure what you did wrong because it works for me". Grrrr...

There is finally a resolution thanks to the storage engine team (confrmed and expounded on by Joanna's blog entry on March 3), and it turns out that the way filestream is configured changed in CTP6 for some very good reasons. You see, sp_filestream_configure sets up Filestream in the OS (requires OS privs) and SQL Server (requires SQL Server privs). Unless you're running sp_filestream_configure as an OS Admin who's also a SQL Server sysadmin, it may not work completely. But sp_filestream_configure is "IN" for one last CTP (CTP6). Gone before RTM.

So filestream configuration was broken into two parts:
1. Configuring filestream at an OS level is moved into setup. Or use SQL Server Configuration Manager. Or WMI scripting (with SMO/WMI) is you like scripting (I do). In the SQL Server Configuration Manager GUI, you configure it by select SQL Server Services (left pane), right-click on your SQL Server service instance and choose "properties" and use the FILESTREAM tab. Note that the equivalent FILESTREAM tab does NOT appear when using Services Control Panel applet. I hope you switched (as you should have) to SQL Server Configuration Manager back in SQL Server 2005 days.

2. Configuring filestream at a SQL Server instance level requires EXEC sp_configure 'filestream access level', '2'. Note that the access level choices in SQL Server are 0,1,2.

3. BOTH configurations steps/setting (OS and SQL Server instance) must be compatible for filestream to work. If, for example, filestream is enabled at the OS level but disabled in SQL Server, you'll get message 5591. See above.

So how did my friend get the "Catch 22 errors"? In CTP6 (but not in future), setup performed step 1, but not step 2. In future setup will do both steps. In CTP6, sp_filestream_configure (to be removed before RTM) will do both steps. But before sp_filestream_configure does anything, it CHECKS to see if either configuration job is already done. Thus the "filestream feature is already configured" message.

Got it? So get used to configuring filestream in both places using SQL Server Configuration Manager AND sp_configure. That's the way of the future. The fact that both exist in one CTP can be frustrating but is understandable. Reminds me of a similar shift when configuring HTTP endpoints (which also requires both OS and SQL configuration) during the SQL Server 2005 CTPs.

Hope this post saves someone a few "grrrr... moments" when using this new useful feature.