Processing SQL Server FILESTREAM Data: Part 2 - The Setup

In Part 1 of this topic, I discussed the reasoning behind the decision to use Microsoft's FILESTREAM technology for a recent client project. In this installment, I discuss the setup portion of this on the SQL Server side. I'll spare you much of the swing-and-a-miss frustration while attempting to understand how the parts work, but I'll try to pinpoint the traps that I located the hard way.

Stream of Consciousness

The first step is to ensure that SQL Server's FILESTREAM technology is enabled for the instance in which you're working. This isn't too difficult to configure, but there is a portion of it that might be confusing.

In SQL Server Configuration Manager, you will be presented with a list of SQL Server services that have been installed. Double click the SQL Server (MSSQLSERVER) service to see its configuration. The third tab in that dialog is the FILESTREAM configuration (see Image 1). The selections on this page require some explanation:

1. The "Enable FILESTREAM for Transact-SQL Access" seems pretty simple. This option is necessary for any FILESTREAM access. But what's subtle here is what it omits, which is the next portion.
2. The "Enable FILESTREAM for file I/O streaming access" is the portion that will allow you as a developer to read and write FILESTREAM data as if it were any other .NET Stream. I recommend enabling this since it allows some nifty capabilities that will be seen in the code for a subsequent post.
3. The "Windows share name" was another option that seemed obvious but was more subtle. This essentially creates a pseudo-share, like any other network share, that contains files that can be read and written. But it won't show up in Windows Explorer. It's only accessible via the SqlFileStream .NET Framework class.
4. The final option, "Allow remote clients to have streaming access to FILESTREAM data" is still a bit of a mystery to me. Why would you enable the access without allowing remote clients to stream to it? Is it likely that only local clients would use it? It doesn't seem so to me but perhaps I'm mistaken.

Instance Kharma

ALTER DATABASE FilestreamExample
    ADD FILEGROUP FilestreamExampleFilegroup
    CONTAINS FILESTREAM
GO

ALTER DATABASE FilestreamExample
    ADD FILE( NAME = N'FilestreamExampleFiles', 
      FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\FilestreamExampleFiles' ) 
    TO FILEGROUP FilestreamExampleFilegroup
GO