Managing unstructured data such as images, documents, and videos has traditionally been a challenge in relational databases. SQL Server addressed these challenges with the introduction of the FILESTREAM feature in SQL Server 2008, which allows BLOB data to be stored in the NTFS file system while maintaining transactional consistency with structured data in the database.
In this article, We will learn about the SQL Server FILESTREAM in detail by understanding various aspects in detail and so on.
SQL Server - FILESTREAM
The FILESTREAM feature in SQL Server enables the storage of unstructured data directly in the file system while keeping a reference in the database. This integration allows for efficient data management and access, combining the strengths of both SQL Server and NTFS.
Syntax:
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
Enabling the FILESTREAM Feature in SQL Server
To use the FILESTREAM feature in SQL Server, it must be enabled during or after the installation of SQL Server. This process involves configuring SQL Server to allow the storage and retrieval of BLOB data within the NTFS file system, linked to the structured data in our database.
Main Concept:
Enabling the FILESTREAM feature can be done either during the SQL Server installation or post-installation through SQL Server Configuration Manager. Once enabled, you can configure the FILESTREAM access levels to control how the data is accessed and managed.
Syntax:
EXEC sp_configure filestream_access_level, 2;
GO
RECONFIGURE;
GO
Example of Enabling FILESTREAM:
Example 1:
This example demonstrates how to enable the FILESTREAM feature using both SQL Server Configuration Manager and T-SQL commands.
Syntax:
We first enable the FILESTREAM feature via SQL Server Configuration Manager, then use T-SQL to configure the access level.
Steps and Query/Code:
- Via SQL Server Configuration Manager:
- Open SQL Server Configuration Manager.
- Navigate to the SQL Server Services.
- Right-click on the SQL Server instance and select "Properties."
- Go to the FILESTREAM tab and enable FILESTREAM for Transact-SQL access, Allow remote clients to have streaming access to FILESTREAM data.
- Apply the settings and restart the SQL Server service.
- Via T-SQL Command:
-- Enable FILESTREAM access at the highest level (full access)
EXEC sp_configure filestream_access_level, 2;
GO
RECONFIGURE;
GO
Explanation:
- Step 1: Through SQL Server Configuration Manager, the FILESTREAM feature is enabled for the SQL Server instance, allowing it to handle FILESTREAM data. This step includes configuring access levels that determine how the FILESTREAM data can be accessed—whether through Transact-SQL, file I/O streaming, or by remote clients.
- Step 2: The T-SQL command
sp_configure sets the FILESTREAM access level to 2, which enables full access (both Transact-SQL and file I/O streaming). The RECONFIGURE command applies this setting to the server.
Managing Unstructured Data with SQL Server's FILESTREAM
Problem Overview and Solution:
Handling unstructured data in SQL Server was problematic before the introduction of the FILESTREAM feature. The primary challenges included balancing performance with transactional consistency and managing large amounts of data effectively. FILESTREAM provides a solution by allowing BLOB data to be stored in the NTFS file system while ensuring transactional consistency with structured data in the database.
- Approach 1: Storing Unstructured Data Using VARBINARY(MAX) with FILESTREAM
- Approach 2: Accessing FILESTREAM Data via T-SQL
- Approach 3: Accessing FILESTREAM Data Using Managed AP
Approach 1: Storing Unstructured Data Using VARBINARY(MAX) with FILESTREAM
The FILESTREAM feature in SQL Server allows you to store unstructured data such as images, videos, and documents in the NTFS file system while maintaining the relational integrity of structured data in the database. By enabling the FILESTREAM attribute on a VARBINARY(MAX) column, SQL Server stores the actual data in the file system and keeps a pointer to this data in the table.
Syntax:
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
Example:
DECLARE @img AS VARBINARY(MAX)
-- Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\temp\MicrosoftMouse.jpg',
SINGLE_BLOB ) AS x
-- Insert the data into the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), 'MS1001','Microsoft Mouse', @img
Explanation:
This example shows how to create a table with a FILESTREAM-enabled column and then insert an image into that column. The image is loaded from the file system into a VARBINARY(MAX) variable and then inserted into the table. The actual image data is stored in the NTFS file system, with a reference to the file stored in the SQL Server table.
Approach 2: Accessing FILESTREAM Data via T-SQL
Once the unstructured data is stored using FILESTREAM, it can be accessed and retrieved using standard T-SQL queries. Despite being stored in the file system, FILESTREAM data is accessible just like any other column in SQL Server, making it easy to retrieve and work with the data using familiar SQL commands.
Syntax:
SELECT * FROM Items
Example:
SELECT ItemID, ItemNumber, ItemDescription, ItemImage
FROM Items
Explanation:
This query retrieves all data from the Items table, including the FILESTREAM data stored in the Item Image column. The data stored in the NTFS file system is seamlessly returned in query results, illustrating the transparency of FILESTREAM data to T-SQL.
Approach 3: Accessing FILESTREAM Data Using Managed API
For advanced scenarios, you can use the Managed API to access FILESTREAM data. This method is particularly useful when you need to take advantage of the NTFS file system's streaming capabilities. The Managed API allows you to access FILESTREAM data directly from the file system without consuming SQL Server's buffer pool, thereby improving performance for large BLOB operations.
Syntax:
-- Retrieve the PathName of the FILESTREAM data
SELECT ItemImage.PathName() AS FilePath
FROM Items
WHERE ItemNumber = 'MS1001'
-- Get the transaction context
SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() AS TransactionContext
Example:
' Create a connection to the database
Dim ConStr As String = "Data Source=YourServer;Initial Catalog=NorthPole;Integrated Security=True"
Dim con As New SqlConnection(ConStr)
con.Open()
' Retrieve the FilePath() of the image file
Dim sqlCommand As New SqlCommand()
sqlCommand.Connection = con
sqlCommand.CommandText = "SELECT ItemImage.PathName() AS PathName FROM Items WHERE ItemNumber = 'MS1001'"
Dim filePath As String = sqlCommand.ExecuteScalar()
' Obtain a Transaction Context
Dim transaction As SqlTransaction = con.BeginTransaction("ItemTran")
sqlCommand.Transaction = transaction
sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
Dim txContext As Byte() = sqlCommand.ExecuteScalar()
' Open and read file using SqlFileStream Class
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read)
Dim buffer As Byte() = New Byte(sqlFileStream.Length) {}
sqlFileStream.Read(buffer, 0, buffer.Length)
' Bind the image data to an image control
Dim ms As MemoryStream = New MemoryStream(buffer)
Dim bmp As Bitmap = New Bitmap(ms)
ItemImage.Image = bmp
' Cleanup
sqlFileStream.Close()
sqlCommand.Transaction.Commit()
con.Close()
Explanation:
This VB.NET example demonstrates how to use the Managed API to read a FILESTREAM file directly from the NTFS file system. The PathName() function retrieves the logical path to the FILESTREAM data, and the SqlFileStream class is used to stream the data, which can then be displayed in an application.
Conclusion
The FILESTREAM feature in SQL Server provides an effective solution for managing unstructured data by integrating the storage of BLOBs within the NTFS file system while ensuring transactional consistency with structured data. Whether using T-SQL for basic operations or leveraging the Managed API for advanced scenarios, FILESTREAM enhances performance and simplifies the management of large, unstructured data. By following the approaches outlined in this article, you can efficiently store, access, and manage unstructured data in SQL Server.