A number of months ago, I wrote an article using the SQL Server filestream feature programmatically. I've gotten a number of inquiries since from folks who *need* to login to SQL Server using SQL logins (often the same SQL login for everyone), but want to access the stream using streaming I/O (e.g. SqlFileStream data type). […]
Bob Beauchemin
SQL Server Blog
Using filestream, streaming I/O and SQL logins with impersonation
Posted on: July 27, 2012 11:31 am
SQL Server 2012 FileTable and T-SQL on AlwaysOn secondary replicas
Posted on: January 17, 2012 12:01 pm
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 […]
SQL Server 2012 FileTables – AlwaysOn support and PathName()
Posted on: January 17, 2012 10:03 am
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, […]
SQL Server 2012 FileTables in T-SQL part 3: hierarchyid methods
Posted on: January 16, 2012 1:06 pm
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 […]
SQL Server 2012 FileTables in T-SQL part 2: new rows
Posted on: January 16, 2012 8:00 am
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: […]
SQL Server 2012 FileTables in T-SQL part 1: functions and methods
Posted on: January 15, 2012 10:17 pm
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 […]
Of filetables, full-text and semantic search, and docidmaps
Posted on: September 6, 2011 12:45 pm
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 […]
Filestreams, Remote Blob Storage, and SQL Server 2008 R2
Posted on: February 4, 2010 12:25 pm
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 […]
Filestreams, transactions and SQL Server 2008 R2
Posted on: February 4, 2010 11:47 am
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 […]
Article about programming filestreams on MSDN
Posted on: May 1, 2009 11:20 am
My latest article on out. It's in the May issue of MSDN magazine, and can be found here. It this article I explore the internals of programming with the filestream feature of SQL Server 2008 and some best practices around when and how to use it. Hope you like it.
Saving a roundtrip when inserting rows with filstream columns
Posted on: May 30, 2008 6:29 pm
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 […]
Configuring Filestream in CTP6 (its different)
Posted on: February 28, 2008 5:58 pm
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. […]
