SQL Server FileTable: Cannot prevent users with read-only access to the FileTable from modifying file contents using non-transactional access

So this blog post has a long title, apologies for that. This is an important FileTable issue that you must be aware of before implementing this feature. Now that SQL Server 2012 is officially released, people are looking to deploy it and take advantage of the many new and improved features. While HADRON is the … Continue reading SQL Server FileTable: Cannot prevent users with read-only access to the FileTable from modifying file contents using non-transactional access

SQL Saturday #111 (Eleventy-one) in Atlanta

If you haven't already done so, check your calendar for Saturday, April 14, 2012 and attend SQL Saturday #111 in Atlanta, GA. The event this year will be held again at Georgia State University's Alpharetta location. Follow the latest news on Twitter using hashtag #sqlsat111. Despite almost insurmountable odds, one of my submitted sessions was … Continue reading SQL Saturday #111 (Eleventy-one) in Atlanta

Customizing the Create FileTable Template in SQL Server 2012

In SQL Server 2012, the new FileTable feature provides some very interesting new capabilities (which I will blog about later). However, the SSMS GUI only provides a T-SQL script template to create a new FileTable. And while this template is very complete, it isn't easy to use. There are more than 20 placeholders that need … Continue reading Customizing the Create FileTable Template in SQL Server 2012

The Truth Behind FilestreamEffectiveLevel and FilestreamConfiguredLevel SERVERPROPERTY values

There is a lot of confusion (not in small part due to Books Online’s inaccurate descriptions) around the meaning of the SQL Server server properties FilestreamEffectiveLevel and FilestreamConfiguredLevel. There are 2 problems with the way Microsoft describes these properties: the list of values they can have is incomplete, and their actual description (as well as … Continue reading The Truth Behind FilestreamEffectiveLevel and FilestreamConfiguredLevel SERVERPROPERTY values

FILESTREAM and Rebuilding Clustered Indexes: Caution

There is some caution needed when working with FILESTREAM and clustered indexes. Specifically, if all of the following conditions are true, your FILESTREAM data ends up being moved to the default FILEGROUP even though this may not be your intent: Your database has more than one FILESTREAM filegroup. You have one or more tables that … Continue reading FILESTREAM and Rebuilding Clustered Indexes: Caution

Using SQL Server 2008 FILESTREAM in an N-Tier Solution

For my recent talk at SQL Saturday #14, I had to find a solution that would allow a client to read SQL Server 2008 FILESTREAM BLOBs using SqlFileStream. The Problem While the addition of the FILESTREAM attribute is a great solution to a common problem, most performance benefits are only realized when accessing the BLOBs … Continue reading Using SQL Server 2008 FILESTREAM in an N-Tier Solution