SQL Server 2012 File-Table Quota
recently I was doing a POC for one of my customers which evaluate the feasibility of handling a file storage related task using SQL Server 2012 File-Table feature.
the SQL Server team did a great job with the new File Table feature.
it is a lightweight implementation which let you manipulate the file either directly (using file explorer or .NET System.IO) of via the TSQL.
for example you can delete a file using with TSQL:
TSQL Snippet
- delete from [dbo].[ImageFiles]
- where [name] = 'SomeName.txt'
or by System.IO:
Code Snippet
- string virtualPath =
- @"\\CompName\\mssqlserver\ImgTable\ImgStore";
- File.Delete(virtualPath);
I won't get to much into the File-Table setting in this post (you can find more on this subject here, here and here),
but I will mention that when we are using System.IO you do have direct access to the stream (un like the previous version of SQL Server stream), while the SQL Server control the actual storage via virtual path.
you may have notice that the path in the above snippet start with a computer name followed by mssqlserver
(there is no such physical folder on my computer).
when you define a File-Table you can associate the table with single or multiple storage. SQL Server will abstract those folder using a virtual path.
this way when a DBA can add storage without affecting your code.
the File-Table is looking almost as a real table:
and you can associate a storage though the database files management (read more about it in here and here)
anyway as I was mention, I was doing a POC with the File-Table and I have found an interesting behavior.
because SQL Server does not interfere with our System.IO, when we are using the following code:
Code Snippet
- byte[] buffer = Encoding.UTF8.GetBytes(
- "More byte than available");
- string virtualPath =
- @"\\CompName\mssqlserver\ImgTable\ImgStore";
- using (var fs = File.OpenWrite(virtualPath))
- {
- fs.Write(buffer, 0, buffer.Length);
- }
the create file is getting an handle to one of the File-Table physical storage, SQL Server doesn't know the size which this file will end with, therefore it will give us an handle to a storage that isn't full yet (even if it one byte below the storage limit).
the interesting thing is that we can cross the quota limit without any exception as long as there is an available physical storage.
Summary
the SQL Server File-Table is a great abstraction which can bridge the OLTP with the File System worlds.
you can easily index your streams without the overhead of indirect file access.
the backup will also backup the streams, and you can manage the physical storage seamlessly.
Credits:
during this POC I was working closely with
Guy Twena and Dubi Lebel.