How to Use SQL Server FILESTREAM to Store Unstructured Data

In this post, I decided to explain to you how to use SQL Server FILESTREAM to store unstructured data. Here you can also read what are the positive and negative sides of FILESTREAM. Here are some other SQL queries to help you use it.
In earlier versions of SQL Server, storage of unstructured data posed many challenges in terms of maintaining consistency between structured and unstructured data, managing backup/restore procedure, performance issues, scalability, etc.… In earlier versions of MSSQL before SQL Server 2008, there were various mechanisms for storing unstructured data. This information is usually stored in the form of files in a shared folder for which certain users are granted access rights. The UNC path of these files is usually stored in the database as a column in the table (varchar (n)) so that application logic can access the specific files. The problem was the security of the files, administering access to them and maintaining them.
The later concept of Binary Large Objects (BLOB) helped to store unstructured data to some degree. The main advantage of this concept is the integrated management and transactional consistency within the database. Security issues (the previous file solution) were resolved in this case. There were problems though, which is a 2Gb limit and log file overload.
To address these issues, SQL Server 2008 introduced for the first time an enhancement called FILESTREAM.
It is not a data type such as VARBINARY (MAX) but an attribute/property set via the VARBINARY column to indicate that the data should be stored directly on the file system. Such data remains an integral part of the system and maintains transactional consistency.
Some of the features of FILESTREAM are:
  • Binary data is stored as individual files outside the database,
  • These individual files can be accessed through the WIN32 API for file operations,
  • T-SQL statements are applicable,
  • The 2GB file size limit for VARBINARY (MAX) columns has been removed for objects stored in the file system through FILESTREAM,
  • FILESTREAM can also be used on compressed folders, disks, volumes.

In order to use FILESTREAM, it must be enabled. FILESTREAM is enabled on two levels. The first level is the SQL Server level. This level was enabled when installing SQL Server. If this is not done then enable can be done through SQL Server Configuration Manager by checking the checkboxes on the SQL Server FILESTREAM tab of Properties (Figure 1).

The second level of enabling FILESTREAM is at the SQL Server Instance level. This enable is done by executing the T-SQL script provided in script 1.
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Script 1. Enable FILESTREAM at Server Instance level
Table 1 lists the possible values for filestream_access_level.
Value Description
0 Disables FILESTREAM support for this instance.
1 Enables FILESTREAM for Transact-SQL access.
 2 Enables FILESTREAM for Transact-SQL and Win32 streaming access.
                                Table 1. Possible values for filestream_access_level
The next step is to create a database where it is possible to create tables in which FILESTREAM will be used. An example of a T-SQL script for creating a database where FILESTREAM can be used is given in script 2.
CREATE DATABASE Test_FILESTREAM
ON
PRIMARY(
NAME=Test_FILESTREAM_Prmary,
FILENAME=’d:sqlbaza2019mssqldataFILESTREAMTest_FILESTREAM.mdf’),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM(
NAME=Test_FILESTREAM_FM,
FILENAME=’d:sqlbaza2019mssqldataFILESTREAMTest_FILESTREAM_FM’)
LOG ON (
NAME=Test_FILESTREAM_Log,
FILENAME=’d:sqlbaza2019mssqldataFILESTREAMTest_FILESTREAM.log’)
 
Script 2. An example of creating a database where it will be possible to use FILESTREAM Executing script 2 on the file system will create a $ FSLOG folder and file filestream.hdr. The filestream.hdr file is a header file for the FILESTREAM container. The next step is to create a table with column VARBINARY (MAX) with the FILESTREAM attribute (script 3).
USE Test_FILESTREAM
CREATE TABLE [dbo].[Fajlovi]
(
ID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY,
Fajl VARBINARY(MAX) FILESTREAM DEFAULT NULL
)
 
Script 3. Creating a table with a VARBINARY (MAX) FILESTREAM column It remains to insert the record into the newly created table (script 4).
INSERT INTO Fajlovi
VALUES (newid(), cast(‘My test FIESTREAM-a’ as varbinary(max)))
 
Script 4. Inserting a record into a table with a VARBINARY (MAX) FILESTREAM column
Inserting a record with script 4 will also create a new folder on the file system.
Records from the table with the VARBINARY (MAX) FILESTREAM column can be obtained by executing script 5.
select *
from [Test_FILESTREAM].[dbo].[Fajlovi]
Script 5. Viewing records from a table with a VARBINARY (MAX) FILESTREAM column
The result of executing script 5 is given in Figure 2.
Each cell in the FILESTREAM column is a file path on the file system associated with it. To read the path, it is necessary to use the PathName property of the varbinary (max) column in the T-SQL statement. Script 6 provides an example of how to read the varbinary (max) column file path.
DECLARE @filePath varchar(max)
SELECT @filePath = Fajl.PathName()
FROM dbo.Fajlovi
WHERE ID = ‘F9A149D0-F5F0-4FC5-9F59-1D27E4D10C1D’
PRINT @filepath
 
Script 6. Example of reading a FILESTREAM file path on a file system
T-SQL can be used to work with FILESTREAM data, but it’s a more natural MS Visual Studio environment. The use of FILESTREAM functionality, in application logic, is made possible by implementing the System.Data.SqlTypes.SqlFileStream class. To maintain consistency of data, each SQL FILESTREAM operation must be part of a transaction. MARS (Multiple Active Result Sets) connections have special rules for batch transactions, which the T-SQL BEGIN TRANSACTION statement violates. To avoid this problem, the application client should use the appropriate transaction management API to mean the class System.Data.SqlClient.SqlTransaction.
To allow transactional access to a FILESTREAM data file system, it is necessary to use the T-SQL function GET_FILESTREAM_TRASACTION_CONTEXT () to provide a token that represents the current transaction within a particular session (C # code 1).
SqlConnection sqlConnection = new SqlConnection(
“Integrated Security=true;server=(local)”);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
SqlTransaction transaction = sqlConnection.BeginTransaction(“mainTranaction”);
sqlCommand.Transaction = transaction;
sqlCommand.CommandText =
“SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()”;
Object obj = sqlCommand.ExecuteScalar();
byte[] txContext = (byte[])obj;
 
C # code 1. Using the GET_FILESTREAM_TRASACTION_CONTEXT () function
This way the transaction is initiated but not yet committed or rollbacked. Multiple operations with FILESTREAM data can be performed in one transaction. An example of a data entry code using the Win32 API is given in C # code 2 in which a sqlFileStream object is initially created based on the System.Data.SqlTypes.SqlFileStream class.
SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite);
byte[] buffer = new byte[512];
int numBytes = 0;
//Write string, “FILESTREAM test data” in FILESTREAM.
string someData = “FILESTREAM test data”;
Encoding unicode = Encoding.GetEncoding(0);
sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),0,someData.Length);
 
C # code 2. Example code for entering data in FILESTREAM
An example code to read FILESTREAM data, using the Win32 API, is given in C # code 3. The same sqlFileStream object that was created at the beginning of the C # code 1 example is used.
sqlFileStream.Seek(0L, SeekOrigin.Begin);
numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);
string readData = unicode.GetString(buffer);
if (numBytes != 0)
Console.WriteLine(readData);
 
C # code 3. Example code to read FILESTREAM data
Upon completion of FILESTREAM enrollment transactions (C # code 1) and reading FILESTREAM data (C # code 2), the sqlFileStream object (created at the beginning of the C # code 1 example based on the System.Data.SqlTypes.SqlFileStream class) must be closed and commit transaction (C # code 4). The sqlCommand object was created at the beginning of example C # code 1.
sqlFileStream.Close();
sqlCommand.Transaction.Commit();
 
C # code 4. Close the sqlFileStream object and commit the transaction
A graph of comparative values of data loading performance, when VARBINARY (MAX) and FILESTREAM are used to store unstructured data and T-SQL or Win32 is given in Figure 3 to access the data.
It should also be noted that FILESTREAM is also used in other SQL Server technologies, such as FileTable and In-Memory OLTP.
In the end:
Like any other technology, FILESTREAM has its advantages and disadvantages.
Advantages:
  • Files created as part of FILESTREAM are managed by SQL Server itself in its own file groups from which backup and restore can be done together with other SQL Server data,
  • Reading and writing these files is part of the database transaction,
  • It is possible to store very large BLOB objects

Disadvantages:

  • FILESTREAM data can only be stored on local disk volumes,
  • Not supported in database snapshot,
  • Not supported on database mirroring,
  • Transparent Data Encryption is not supported,
  • Cannot be used with table-valued parameters
  • Top articles, research, podcasts, webinars and more delivered to you monthly.

  • Leave a Comment
    Next Post

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Big Data, Cloud & DevOps
    Cognitive Load Of Being On Call: 6 Tips To Address It

    If you’ve ever been on call, you’ve probably experienced the pain of being woken up at 4 a.m., unactionable alerts, alerts going to the wrong team, and other unfortunate events. But, there’s an aspect of being on call that is less talked about, but even more ubiquitous – the cognitive load. “Cognitive load” has perhaps

    5 MINUTES READ Continue Reading »
    Big Data, Cloud & DevOps
    How To Refine 360 Customer View With Next Generation Data Matching

    Knowing your customer in the digital age Want to know more about your customers? About their demographics, personal choices, and preferable buying journey? Who do you think is the best source for such insights? You’re right. The customer. But, in a fast-paced world, it is almost impossible to extract all relevant information about a customer

    4 MINUTES READ Continue Reading »
    Big Data, Cloud & DevOps
    3 Ways Businesses Can Use Cloud Computing To The Fullest

    Cloud computing is the anytime, anywhere delivery of IT services like compute, storage, networking, and application software over the internet to end-users. The underlying physical resources, as well as processes, are masked to the end-user, who accesses only the files and apps they want. Companies (usually) pay for only the cloud computing services they use,

    7 MINUTES READ Continue Reading »