Click here to Skip to main content
15,502,045 members
Articles / Database Development / SQL Server
Technical Blog
Posted 22 Sep 2014

Stats

10.6K views
12 bookmarked

Processing SQL Server FILESTREAM Data: Part 2 - The Setup

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
22 Sep 2014CPOL4 min read
Utilizing SQL Server FILESTREAM capabilities from .NET

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.

Image 1

Image 1 - FILESTREAM Configuration

Instance Kharma

Next, we need to ensure that our database instance is enabled to utilize FILESTREAM capabilities. This can be done from SQL Server Management Studio. Right click on the database instance and choose Properties from the resulting menu. The Advanced configuration selection in that dialog has a dropdown list for FILESTREAM support right at the very top (see Image 2). It's uncertain to me whether this step is necessary or not because I didn't necessarily do this in the prescribed order but it seemed to me that it needed to be done. I chose the "Full access enabled" option in order to employ the remote streaming access that will be shown in a subsequent post.

Image 2

Image 2 - FILESTREAM Instance Configuration

Filegroup Therapy

Since FILESTREAM BLOB data is stored on the file system. It can't live inside the PRIMARY filegroup for a database. So we need to create a new filegroup and file to contain this data. This is done pretty simply with a few SQL statements, or so it would seem.

First the filegroup.

SQL
ALTER DATABASE FilestreamExample
    ADD FILEGROUP FilestreamExampleFilegroup
    CONTAINS FILESTREAM
GO

This is very simple and straightforward. It creates a logical filegroup that specifies that the files contained within will be where FILESTREAM BLOB data is stored.

Pernicious Permissions

Now that I had a filegroup, I needed to add files to it. This is where things went a little sideways.

The SQL code to add a file to a filegroup is not terribly complicated.

SQL
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

Upon execution of this piece of code, I was presented with the following noxious error:

Operating system error 0x80070005(Access is denied.) occurred while creating or opening file 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\FilestreamExampleFiles'. Diagnose and correct the operating system error, and retry the operation.

As I investigated this issue, I began to understand what was happening. SQL Server was attempting to create a folder on disk with the name I specified in the ALTER DATABASE command, which is where it would store the files that would comprise the BLOB data. But there was clearly a permissions issue creating the folder.

Well, I'm a developer not an IT technician but I know enough to solve this issue. But I was unable to do so in a satisfactory way. The SQL Server service was running under the NetworkService account, which seemed appropriate for the situation. That account had full control to the entire SQL Server folder tree and everything beneath it. But no matter what I did, the problem persisted. I finally changed the service account to LocalSystem and the problem disappeared but I'm uncomfortable with that answer. If I set the permissions for the NetworkService user, why was it unable to write to a local disk resource?

Up Next - Processing SQL Server FILESTREAM Data: Part 3 - Creating Tables

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO RAMSoft Solutions, Inc
United States United States
Bob McGowan is the founder and CEO of RAMSoft Solutions and has led the company since its inception in 1993. He has over 30 years of experience managing projects and developing applications software for microcomputers. He has managed multi-million dollar software development projects for many Fortune 100 companies, including Chase Manhattan Bank, Prudential Insurance, and True North Media Services and has acted as a senior technical advisor for dozens of other projects.

Bob has been developing software for microcomputers since 1980, and has developed applications for many companies using a wide variety of development tools, languages, architectures, and operating systems. In 2004 he co-founded an information technology services company specializing in the Yellow Pages Advertising industry. As Chief Technology Officer he architected and implemented a service allowing agencies to view electronic "tear pages" via web services or interactively via a web site. The process included an optical character recognition (OCR) workflow automation process in for scanning, OCR, and review of Yellow Pages telephone directories. After 12 months of operation the database contained over 1.6 million scanned pages and accompanying extracted text in a searchable format.

He continues to be passionate about software development and technology in general. You can follow his thoughts on software development at http://blog.ramsoftsolutions.com

Comments and Discussions

 
QuestionNetworkService vs LocalService vs LocalAccount Pin
Lucas Vogel23-Sep-14 21:18
professionalLucas Vogel23-Sep-14 21:18 
I believe you'll find your answer here: http://stackoverflow.com/questions/510170/the-difference-between-the-local-system-account-and-the-network-service-acco[^]
AnswerRe: NetworkService vs LocalService vs LocalAccount Pin
Bob McGowan24-Sep-14 3:11
Bob McGowan24-Sep-14 3:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.