SQL Server中的文件流

This article will discuss SQL Server FILESTREAM including installation, configuration, enabling and general considerations.

本文将讨论SQL Server FILESTREAM，包括安装，配置，启用和一般注意事项。

We may need to store a different kind of data in our SQL Server database apart from the regular table-based data. This data may be in multiple forms such as a document, images, audio or video files. You might have seen people using BLOB data to store these kinds of data, but you can save only up to 2 GB using this. This kind of data also slow down the performance of your database system since it tends to be large and takes significant system resources to bring it back from the disk.

除了基于常规表的数据外，我们可能需要在SQL Server数据库中存储另一种数据。 该数据可以采用多种形式，例如文档，图像，音频或视频文件。 您可能已经看到人们使用BLOB数据来存储此类数据，但是使用此数据最多只能保存2 GB。 这类数据通常会很大，并且会占用大量系统资源才能将其从磁盘中恢复，因此也会降低数据库系统的性能。

FILESTREAM, in SQL Server, allows storing these large documents, images or files onto the file system itself. In FILESTREAM, we do not have a limit of storage up to 2 GB, unlike the BLOB data type. We can store large size documents as per the underlying file system limitation. SQL Server or other applications can access these files using the NTFS streaming API. Therefore, we get the performance benefit of this streaming API as well while accessing these documents.

SQL Server中的FILESTREAM允许将这些大文档，图像或文件存储到文件系统本身。 在文件流中，与BLOB数据类型不同，我们没有最大2 GB的存储限制。 我们可以根据基本文件系统限制存储大尺寸文档。 SQL Server或其他应用程序可以使用NTFS流API来访问这些文件。 因此，在访问这些文档时，我们还将获得此流API的性能优势。

Note: FILESTREAM is not a SQL Server data type to store data

注意： FILESTREAM不是用于存储数据SQL Server数据类型

Traditionally if we store the data in the BLOB data type, it is stored in the Primary file group only. In SQL Server FILESTREAM, We need to define a new filegroup ‘FILESTREAM’. We need to define a table having varbinary(max) column with the FILESTREAM attribute. It allows SQL Server to store the data in the file system for these data type. When we access the documents that are stored in the file system using the FILESTREAM, we do not notice any changes in accessing it. It looks similar to the data stored in a traditional database.

传统上，如果我们将数据存储为BLOB数据类型，则仅将其存储在主文件组中。 在SQL Server FILESTREAM中，我们需要定义一个新的文件组'FILESTREAM'。 我们需要使用FILESTREAM属性定义一个具有varbinary（max）列的表。 它允许SQL Server将这些数据类型的数据存储在文件系统中。 当我们使用FILESTREAM访问存储在文件系统中的文档时，我们在访问文件时没有发现任何更改。 它看起来类似于存储在传统数据库中的数据。

Let us understand the difference in storing the images in the database or the file system using SQL Server FILESTREAM.

让我们了解使用SQL Server FILESTREAM将图像存储在数据库或文件系统中的区别。

Below you can traditional database storing the employee photo in the database itself.

您可以在下面的传统数据库中将员工照片存储在数据库本身中。

Now let us look at the changes in this example using the SQL Server FILESTREAM feature.

现在，让我们使用SQL Server FILESTREAM功能查看此示例中的更改。

In the above illustration, you can see the documents are stored in the file system, and the database has a particular filegroup ‘FILESTREAM’. You can perform actions on the documents using the SQL Server database itself.

在上图中，您可以看到文档存储在文件系统中，并且数据库具有特定的文件组“ FILESTREAM”。 您可以使用SQL Server数据库本身对文档执行操作。

One more advantage of the FILESTREAM is that it does not use the buffer pool memory for caching these objects. If we cache these large objects in the SQL Server memory, it will cause issues for normal database processing. Therefore, FILESTREAM provides caching at the system cache providing performance benefits without affecting core SQL Server performance.

FILESTREAM的另一个优点是它不使用缓冲池内存来缓存这些对象。 如果我们将这些大对象缓存在SQL Server内存中，则将导致正常的数据库处理问题。 因此，FILESTREAM在系统缓存中提供缓存，从而在不影响核心SQL Server性能的情况下提供了性能优势。

在SQL Server中启用FILESTREAM功能 (Enabling the FILESTREAM feature in SQL Server)

We can enable the FILESTREAM feature differently in SQL Server.

我们可以在SQL Server中以其他方式启用FILESTREAM功能。

1. During Installation: You can configure FILESTREAM during the SQL Server installation. However, I do not recommend doing it during the installation because we can later enable it as per our requirements
   在安装期间：您可以在SQL Server安装期间配置FILESTREAM。 但是，我不建议在安装过程中这样做，因为以后我们可以根据需要启用它

2. In the SQL Server properties, you can see a tab ‘FILESTREAM’.

   在SQL Server属性中，可以看到“ FILESTREAM”选项卡。

   

   Click on ‘FILESTREAM’, and you get below screen. Here you can see that this feature is not enabled by default

   点击“ FILESTREAM”，您将得到以下屏幕。 在这里您可以看到默认情况下未启用此功能

   

• Put a tick in the checkbox ‘Enable FILESTREAM for Transact-SQL access’
  在复选框“为Transact-SQL访问启用文件流”复选框中打勾
• We can also enable the read\write access from the windows for file I/O access. Put a tick on the ‘Enable FILESTREAM for file I/O access’ as well
  我们还可以从窗口启用对文件I / O访问的读写访问权限。 还要在“为文件I / O访问启用FILESTREAM”上打勾
• 
  
  
  
  
  

  Click Apply to activate the FILESTREAM feature in SQL Server. You will get a prompt to restart the SQL Server service. Once we have enabled FILESTREAM access and restarted SQL Server, we also need to specify the access level using SSMS. We need to make changes in sp_configure to apply this setting.

  单击“ 应用”以激活SQL Server中的FILESTREAM功能。 您将看到提示重新启动SQL Server服务。 启用FILESTREAM访问并重新启动SQL Server之后，我们还需要使用SSMS指定访问级别。 我们需要在sp_configure中进行更改以应用此设置。

Run the below command to show the advanced option in sp_configure.

运行以下命令以显示sp_configure中的高级选项。

USE master
  Go
  EXEC sp_configure 'show advanced options'
  Go

Run the command sp_configure to check all available options. We can see all the available options now. Since we are interested in FILESTREAM only, I highlighted this particular option.

运行命令sp_configure以检查所有可用选项。 我们现在可以看到所有可用的选项。 由于我们仅对FILESTREAM感兴趣，因此我强调了此特定选项。

Below is the option in the sp_configure

以下是sp_configure中的选项

We need to specify the value from 0 to 2 while enabling SQL Server FILESTREAM using the query.

在使用查询启用SQL Server FILESTREAM时，我们需要指定0到2之间的值。

You can run the command to specify the access level. In below command, you can see that we have specified SQL Server FILESTREAM access level as 2.

您可以运行命令来指定访问级别。 在下面的命令中，您可以看到我们已将SQL Server FILESTREAM访问级别指定为2。

EXEC sp_configure filestream_access_level, 2
  GO
  RECONFIGURE WITH OVERRIDE
  GO

If you do not enable FILESTREAM using the SQL Server Configuration Manager, You can get the error message

如果未使用SQL Server配置管理器启用FILESTREAM，则可能会收到错误消息

‘FILESTREAM feature could not be initialized. The operating system Administrator must enable FILESTREAM on the instance using Configuration Manager.’

'FILESTREAM功能无法初始化。 操作系统管理员必须使用Configuration Manager在实例上启用FILESTREAM。

We can also provide this access level using the SSMS. Right click on the server instance and go to properties.

我们还可以使用SSMS提供此访问级别。 右键单击服务器实例，然后转到属性。

Now click on Advanced, and you can see a separate group for SQL Server FILESTREAM.

现在，单击Advanced ，您将看到一个单独SQL Server FILESTREAM组。

In this group, we can define the SQL Server FILESTREAM access level from the drop-down option as shown below.

在该组中，我们可以从下拉选项中定义SQL Server FILESTREAM访问级别，如下所示。

In this GUI mode, we have three options listed. The following table shows the mapping between GUI and t-SQL options for SQL Server FILESTREAM access level.

在此GUI模式下，我们列出了三个选项。 下表显示了SQL Server FILESTREAM访问级别的GUI和t-SQL选项之间的映射。

使用SQL Server FILESTREAM功能时要考虑的重要点 (Important points to consider while using the SQL Server FILESTREAM feature)

• We can use the SELECT, INSERT, UPDATE, and DELETE statements similar to a standard database query in FILESTREAM
  我们可以使用SELECT，INSERT，UPDATE和DELETE语句，类似于FILESTREAM中的标准数据库查询
• We should use FILESTREAM if the object size is larger than 1 MB on average
  如果对象大小平均大于1 MB，则应使用FILESTREAM
• Each row should have a unique row ID to use this functionality, and it should not contain NULL values
  每行应具有唯一的行ID才能使用此功能，并且不应包含NULL值
• We can create the FILESTREAM filegroup on the compressed volume as well
  我们也可以在压缩卷上创建FILESTREAM文件组
• In the Failover clustering, we need to use a shared disk for the FILESTREAM filegroup
  在故障转移群集中，我们需要将共享磁盘用于FILESTREAM文件组
• We can add multiple data containers in the FILESTREAM filegroup
  我们可以在FILESTREAM文件组中添加多个数据容器
• We cannot encrypt FILESTREAM data
  我们无法加密FILESTREAM数据
• You cannot use SQL logins with the FILESTREAM container
  您不能将SQL登录名与FILESTREAM容器一起使用

结论： ( Conclusion: )

In this article, we took the overview the FILESTREAM feature in SQL Server and explored ways to enable it at the SQL Server instance level. In the next article, we will create a database with SQL Server FILESTREAM data and perform multiple operations on it.

在本文中，我们概述了SQL Server中的FILESTREAM功能，并探讨了在SQL Server实例级别启用该功能的方法。 在下一篇文章中，我们将创建一个包含SQL Server FILESTREAM数据的数据库，并对它执行多项操作。

目录 (Table of contents)

翻译自: https://www.sqlshack.com/filestream-in-sql-server/