在 SQL Server 数据库中更改 FILESTREAM 数据文件的位置

原创肯肯在学习 2022-10-19

1034

本文介绍了更改 FILESTREAM 数据文件位置的不同方法。FILESTREAM 功能是在 SQL Server 2008 版本中引入的。我们可以使用 FILESTERAM 将 BLOB 存储在 SQL 数据库中。

什么时候应该将 FILESTREAM 文件组保存在单独的驱动器中？

在以下情况下，FILESTREAM 文件组的位置应保持不同：

当我们开发一个与图像和文件交互非常频繁的应用程序时，我们可以将 SQL 数据库的 FILESTREAM 数据文件保存在单独的驱动器中，以减少 IO 瓶颈并提高性能。
有时我们在默认位置配置 FILESTREAM 文件组。最终，已配置 FILESTREAM 的驱动器开始被填满，并且变得难以管理 FILESTREAM 数据。因此，建议将 FILESTREAM 文件组保存在单独的驱动器上。

我最近参与了一个将 FILESTREAM 数据移动到服务器的单独驱动器的项目。正如我上面提到的，SQL 数据库没有正确配置；因此，存储 FILESTREAM 数据的驱动器空间不足，从而破坏了应用程序的功能。我们可以使用以下任何一种方法将 FILESTREAM 数据文件复制到不同的位置。

方法一：分离和附加数据库方法

这种方法非常简单，但是如果我们使用这种方法，数据库会一直处于离线状态，直到 FILESTREAM 数据文件的位置发生变化。

方法 2：通过重新创建 FILESTREAM 表的聚集索引来更改位置。

我们将在此方法中删除并在 FILESTREAM 表上创建聚集索引。如果我们使用这种方法，数据库将处于联机状态，但 FILESTREAM 表将保持脱机状态，直到创建聚集索引。

我们评估了这两种方法，并且由于我们有停机时间，我们使用了分离和附加数据库方法来移动 FILESTREAM 数据文件。

在本文中，我将解释这两种方法，以便您确定移动 SQL Server 的 FILESTREAM 文件组的最佳方法。

环境设置

为了演示，我在我的工作站上创建了一个名为 EltechDB 的数据库。我创建了一个名为FG_EltechDB_Employee_Documents 的 FILESTREAM 文件组，并在文件组中添加了一个名为DF_EltechDB_Employee_Documents的数据文件。我们将 FILESTREAM 数据保存在D:\EltechDB_Employee\Employee_Documents目录中。

创建 FILESTREAM 文件组的脚本如下：

  USE [master] 
  go 
  ALTER DATABASE [EltechDB] ADD filegroup [FG_EltechDB_Employee_Documents] 
  CONTAINS filestream 
  go 
  
  USE [master] 
  go 
  ALTER DATABASE [EltechDB] ADD FILE ( NAME = N'DF_EltechDB_Employee_Documents', 
  filename = N'D:\EltechDB_Employee\Employee_Documents' ) TO filegroup [EmployeeDoucment] 
  go

我创建了一个名为tblEmployees的 FILESTREAM 表来存储表中的数据。我在D:\EmployeeDocuments目录中复制了一些文档。

运行以下查询以创建表并在**tblEmployees**中插入数据。

查询创建表

  Use [EltechDB]
  Go
  CREATE TABLE [tblEmployee] (
     [FileId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
     [ID] int identity(1,1) Primary key clustered,
     [EmployeeID] varchar(10),
     [Documents] VARBINARY(MAX) FILESTREAM);
  GO

查询插入数据

 
  DECLARE @Document_1 VARBINARY(max); 
  DECLARE @Document_2 VARBINARY(max); 
  DECLARE @Document_3 VARBINARY(max); 
  
  SELECT @Document_1 = Cast(bulkcolumn AS VARBINARY(max)) 
  FROM   OPENROWSET(BULK 'D:\EmployeeDocuments\Document_1.pdf', single_blob) AS 
         [Document 1] 
  
  SELECT @Document_2 = Cast(bulkcolumn AS VARBINARY(max)) 
  FROM   OPENROWSET(BULK 'D:\EmployeeDocuments\Document_2.pdf', single_blob) AS 
         [Document 2] 
  
  SELECT @Document_3 = Cast(bulkcolumn AS VARBINARY(max)) 
  FROM   OPENROWSET(BULK 'D:\EmployeeDocuments\Document_3.pdf', single_blob) AS 
         [Document 3]; 
  
  INSERT INTO [tblemployee] 
              ([fileid], 
               [employeeid], 
               [documents]) 
  VALUES      ( Newid(), 
                'EMP0001', 
                @Document_1), 
              ( Newid(), 
                'EMP0002', 
                @Document_2), 
              ( Newid(), 
                'EMP0003', 
                @Document_3)

我们想将 FILESTREAM 数据文件的位置从D:\EltechDB_Employee\EmployeeDocuments更改为D:\EltechDB\Employee_Documents。

现在，让我们了解如何更改 FILESTREAM 数据文件的路径。

分离和附加数据库

在这种方法中，我们必须先分离数据库，将 FILESTREAM 数据复制到新位置，然后重新附加数据库。运行以下语句以分离数据库。

 
  USE [master] 
  go 
  ALTER DATABASE [EltechDB] SET single_user WITH ROLLBACK immediate 
  go 
  EXEC Sp_detach_db [EltechDB] 
  go

现在，将 FILESTREAM 数据复制到**D:\EltechDB\Employee_Documents**目录。复制文件后，附加数据库。

注意： 不推荐使用sp_attach_db命令，因此我们使用CREATE DATABASE FOR ATTACH语句来附加数据库。我们可以在CREATE DATABASE ADD FILEGROUP语句中指定新位置。

运行以下查询附加数据库

 USE [master]
  GO
  CREATE DATABASE [EltechDB] ON
  ( FILENAME = N'D:\MS_SQL\Data\EltechDB_Data.mdf' ),
  ( FILENAME = N'D:\MS_SQL\Log\EltechDB_Log.ldf' ),
  FILEGROUP [FG_EltechDB_Employee_Documents] CONTAINS FILESTREAM DEFAULT
  ( NAME = N'DF_EltechDB_Employee_Documents', FILENAME = N'D:\EltechDB\Employee_Documents' )
  FOR ATTACH
  GO

附加数据库后，运行以下语句以验证 FILESTREAM 数据文件位置是否已更改。

询问

  USE eltechdb 
  go 
  SELECT df.NAME          AS [Logical Name], 
         df.size / 128    AS [File Size], 
         fg.NAME          AS [FileGroup Name], 
         df.physical_name AS [Physical Path] 
  FROM   sys.database_files AS df 
         INNER JOIN sys.filegroups AS fg 
                 ON df.data_space_id = fg.data_space_id;

输出

如您所见，位置已更改。现在，让我们跳到另一种方法。

删除并重新创建 FILESTREAM 表的聚集索引

在这种方法中，首先，我们必须创建一个名为FG_EltechDB_Employee_Documents_New的新 FILESTREAM 文件组。在FG_EltechDB_Employee_Document_New文件组中添加一个名为DF_EltechDB_Employee_Document_New的数据文件。FG_EltechDB_Employee_Document_New必须是 FILESTREAM 数据的默认文件组**。**运行以下查询

  USE [master]
  GO
  ALTER DATABASE [EltechDB] ADD FILEGROUP [FG_EltechDB_Employee_Documents_New] CONTAINS FILESTREAM 
  GO
  USE [master]
  GO
  ALTER DATABASE [EltechDB] ADD FILE ( NAME = N'DF_EltechDB_Employee_Documents_New', FILENAME = N'C:\EltechDB\Employee_Documents' ) TO FILEGROUP [FG_EltechDB_Employee_Documents_New]
  GO
  ALTER DATABASE [EltechDB] MODIFY FILEGROUP [FG_EltechDB_Employee_Documents_New] DEFAULT
  GO

现在，通过运行以下查询 删除 tblemployee表上的聚集索引。

  ALTER TABLE [dbo].[tblemployee] 
  DROP CONSTRAINT [PK__tblEmplo__3214EC277F3CFB9E]

创建索引。新索引将在FG_EltechDB_Employee_Document_New文件组中创建，并且 FILESTREAM 数据将位于新文件组中。

  ALTER TABLE [dbo].[tblemployee] 
  ADD PRIMARY KEY CLUSTERED ( [id] ASC )

插入新记录时，数据将存储在新位置。

注意： 这种方法只能在表有聚集索引的情况下使用，并且聚集索引必须在ROWGUID列以外的列上创建。

概括

数据库容量规划是应用程序开发中最重要的阶段。通常，我们不考虑数据增长趋势，最终我们会遇到破坏应用程序功能的问题。

在本文中，我们学习了如何更改 SQL 数据库的 FILESTREAM 文件组的位置。我们学习了以下用于移动 FILESTREAM 文件组的方法。

分离和附加 SQL 数据库方法
通过重新创建 FILESTREAM 表的聚集索引来更改位置。

原文标题：Changing the location of FILESTREAM data files in SQL Database
原文作者：Nisarg Upadhyay
原文地址：https://www.sqlshack.com/changing-the-location-of-filestream-data-files-in-sql-database/

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者