filestream

In the previous articles in this series (), we wrote about the various feature of the SQL Server FILESTREAM. In SQL Server, we use replication to replicate the articles to the destination server. Consider a scenario in which we have the FILESTREAM database in our environment. We would also have the requirement to configure this database for SQL Server replication.

在本系列的前几篇文章中（ ），我们写了有关SQL Server FILESTREAM的各种功能。 在SQL Server中，我们使用复制将项目复制到目标服务器。 考虑一个在我们的环境中拥有FILESTREAM数据库的方案。 我们还需要为SQL Server复制配置此数据库。

As you might have noticed reading through the previous articles, SQL Server FILESTREAM stores large objects into the file system. Therefore, this question came into my mind of whether it is possible to replicate FILESTREAM data as well. In this article, we are going to cover the steps to configure SQL Server replication for the FILESTREAM database.

您可能已经阅读了之前的文章，注意到SQL Server FILESTREAM将大对象存储到文件系统中。 因此，我想到是否也可以复制FILESTREAM数据这个问题。 在本文中，我们将介绍为FILESTREAM数据库配置SQL Server复制的步骤。

先决条件 (Pre-requisites)

We should have installed the SQL Server replication components on the server to configure it. The following error shows that replication is not installed on this instance of SQL Server.

我们应该已经在服务器上安装了SQL Server复制组件以对其进行配置。 以下错误表明在此SQL Server实例上未安装复制。

We can install replication components using the SQL Server installer. You need to run the installer of the same version of the database instance. In my environment, I am working on the SQL Server 2019 CTP 2.0. Therefore, I launched the installer of it.

我们可以使用SQL Server安装程序安装复制组件。 您需要运行相同版本的数据库实例的安装程序。 在我的环境中，我正在研究SQL Server 2019 CTP 2.0。 因此，我启动了它的安装程序。

In the feature selection page, select ‘SQL Server Replication’ as per the following image.

在功能选择页面中，根据下图选择“ SQL Server复制”。

Once we have installed the SQL Server replication component, we can start the configuration of SQL Server replication for the FILESTREAM database.

一旦安装了SQL Server复制组件，就可以开始配置FILESTREAM数据库SQL Server复制。

Before we start to configure SQL Server replication, let me give a brief introduction of the replication components.

在开始配置SQL Server复制之前，让我简要介绍一下复制组件。

• Publisher: it is the source database that we want to SQL Server replication
  发布者：这是我们要SQL Server复制的源数据库
• Subscriber: It is the destination database in which we want to replicate the data
  订阅服务器：这是我们要在其中复制数据的目标数据库
• Articles: in SQL Server replication, each object is called an ‘Article’
  文章：在SQL Server复制中，每个对象称为一个“文章”

We need to have the following pre-configurations.

我们需要进行以下预配置。

Now let us start the SQL Server replication configuration from the Source database.

现在，让我们从源数据库开始SQL Server复制配置。

• Connect to the Publisher instance. Right click on the ‘Replication’ and select ‘New Publication’

  连接到发布者实例。 右键单击“复制”，然后选择“新出版物”

  

  It starts the following publication wizard.

  它启动以下发布向导。

Click ‘Next’ and configure the distribution. In this article, we are going to use the publisher instance as the distributor as well. It shows this option by default. It will create the distribution database in the default data and log directories.

单击“下一步”并配置分发。 在本文中，我们还将使用发布者实例作为分发者。 默认情况下显示此选项。 它将在默认数据和日志目录中创建分发数据库。

In the following page, we need to configure the SQL Server Agent service to start automatically. If the SQL Server Agent service startup mode is manual, SQL Server replication will not work in sync automatically after a restart of SQL Service.

在下一页中，我们需要配置SQL Server代理服务以自动启动。 如果SQL Server代理服务启动模式为手动，则在重新启动SQL Service之后，SQL Server复制将不会自动同步。

SQL Server creates a snapshot of the publisher database. We can specify the proper path for this. We should have sufficient free space in this drive as per the publisher database size and the FILESTREAM container size as well.

SQL Server创建发布者数据库的快照。 我们可以为此指定适当的路径。 根据发布者数据库大小和FILESTREAM容器大小，我们在该驱动器中应该有足够的可用空间。

Now, in the following step, we need to select the publisher database.

现在，在接下来的步骤中，我们需要选择发布者数据库。

Now, we need to select the necessary SQL Server replication type. We can have the following SQL replication types.

现在，我们需要选择必要SQL Server复制类型。 我们可以具有以下SQL复制类型。

• Snapshot replication: In this SQL replication type, the publication database snapshot is applied to the subscriber database. No further changes are applied 快照复制：在这种SQL复制类型中，发布数据库快照将应用于订阅者数据库。 不再应用其他更改
• Transactional replication: this SQL replication type sends the transactions as well to the subscriber database for the articles configured in the SQL Server replication 事务复制：此SQL复制类型也将事务发送到SQL Server复制中配置的项目的订阅者数据库
• Peer to Peer replication: In this type of SQL replication, we can configure multiple peers to read and write the articles, and changes are propagated to all the nodes in the SQL replication 点对点复制：在这种类型SQL复制中，我们可以配置多个对等点来读写文章，并将更改传播到SQL复制中的所有节点
• Merge replication: In merge SQL replication, we can make changes to the data in the publisher and the subscriber as well and later all the data synchronizes between the publisher and the subscriber 合并复制：在合并SQL复制中，我们也可以更改发布者和订阅者中的数据，然后所有数据在发布者和订阅者之间同步

In this article, we are going to configure transactional SQL Server replication for the FILESTREAM database.

在本文中，我们将为FILESTREAM数据库配置事务SQL Server复制。

We need to select the objects from the publisher database. We should have the primary key on the object to configure replication on it. If we do not have any primary key on a particular object, we get an error message that the table cannot be published because it does not have a primary key defined on it.

我们需要从发布者数据库中选择对象。 我们应该在对象上具有主键以在其上配置复制。 如果在特定对象上没有任何主键，则会收到一条错误消息，指出该表没有定义主键，因此无法发布。

Right click on the FILESTREAM table and ‘Design’.

右键单击FILESTREAM表，然后单击“设计”。

Right click on the table and

右键单击表格，然后

In the table designer, right-click the required column and set the primary key on it. We set the primary key on the document_id column.

在表设计器中，右键单击所需列，然后在其上设置主键。 我们在document_id列上设置主键。

In the previous article, we did not specify the primary key on the FILESTREAM table. Therefore, you can create the primary key in the table designer using the steps above. Once we have created the primary key, you can check it using the below query.

在上一篇文章中，我们没有在FILESTREAM表上指定主键。 因此，您可以使用上述步骤在表设计器中创建主键。 一旦创建了主键，就可以使用以下查询对其进行检查。

Sp_help 'TBL_Support_Documents'

I have already created a primary key on the ‘TBL_SUPPORT_DOCUMENTS’ table therefore in the following screenshot; you can see that we can configure transactional SQL replication on it.

我已经在“ TBL_SUPPORT_DOCUMENTS”表上创建了主键，因此在下面的屏幕快照中； 您会看到我们可以在其上配置事务SQL复制。

In the next page, we can define the snapshot schedule for the publisher database. We want to do it immediately, therefore, leave the default option ‘Create the snapshot immediately and keep the snapshot available to initialize subscriptions’.

在下一页中，我们可以为发布者数据库定义快照计划。 我们希望立即执行此操作，因此，保留默认选项“立即创建快照并使快照可用于初始化订阅”。

In the agent security page, we can define the service account for the log reader and the snapshot agent. We should set it to SQL Server Agent account as per best practice.

在代理安全性页面中，我们可以为日志读取器和快照代理定义服务帐户。 我们应根据最佳实践将其设置为SQL Server代理帐户。

We have done the configuration stuff for the publication. In a further step, we can either generate a script for the SQL Server replication or create the publication.

我们已经完成了发布的配置工作。 在进一步的步骤中，我们可以为SQL Server复制生成脚本或创建发布。

Specify the publication name and finish.

指定发布名称并完成。

SQL Server starts the SQL replication configuration, and you can see a progress report of it.

SQL Server启动SQL复制配置，您可以看到它的进度报告。

If there is any error or warning, you can see it in the message tab. We get the below message that SQL Server could not configure the SQL Server Agent to start automatically. It is fine at for this demo however in production you should set SQL Server Agent service startup mode as Automatic to avoid this warning.

如果有任何错误或警告，您可以在消息选项卡中看到它。 我们收到以下消息，SQL Server无法将SQL Server代理配置为自动启动。 此演示非常合适，但是在生产中，应将SQL Server代理服务启动模式设置为“自动”，以避免出现此警告。

In the local publication, you can see publication as per the following image.

在本地出版物中，您可以按照以下图像查看出版物。

Once we have the publication, right click on it and go to properties. In the ‘Article’ page, go to ‘Article properties’

获得出版物后，右键单击它并转到属性。 在“文章”页面中，转到“文章属性”

By default, SQL Server replication converts FILESTREAM to MAX data types. In this configuration, we cannot store the FILESTREAM data at the file system on the subscriber database. We need to change the configuration as below.

默认情况下，SQL Server复制将FILESTREAM转换为MAX数据类型。 在这种配置中，我们无法将FILESTREAM数据存储在订户数据库上的文件系统上。 我们需要如下更改配置。

将FILESTREAM转换为最大数据类型：FALSE (Convert FILESTREAM to max data types: FALSE )

Now we are ready to create the subscription. Right click on the publication and create a new subscriber.

现在我们准备创建订阅。 右键单击发布并创建一个新的订阅者。

It automatically shows the publication list. We can go ahead to configure the distribution agent.

它会自动显示发布列表。 我们可以继续配置分发代理。

We can have two kinds of subscriptions.

我们可以有两种订阅。

• Push subscriptions: All the changes at the publisher database will be replicated to the subscriber database automatically 推送订阅：发布者数据库上的所有更改将自动复制到订阅者数据库
• Pull subscription: Each subscriber will request the changes from the publisher to replicate data on the database 拉订阅：每个订阅者将向发布者请求更改，以复制数据库中的数据

Let us go with the push subscription.

让我们一起进行推送订阅。

In the next step, we can select the subscriber database. As already highlighted, we should have this database prepared for the FILESTREAM feature of the SQL Server.

在下一步中，我们可以选择订户数据库。 如前所述，我们应该为SQL Server的FILESTREAM功能准备此数据库。

Configure the distribution agent security. We will go with the SQL Server Agent security.

配置分发代理安全性。 我们将使用SQL Server代理的安全性。

We can prepare the synchronisation schedule as per our requirement. We want to have immediate synchronization.

我们可以根据我们的要求准备同步时间表。 我们希望立即同步。

In the next step, we will configure subscriber to initialize with the publication snapshot we created earlier.

在下一步中，我们将配置订户以使用我们先前创建的发布快照进行初始化。

We can generate the subscription script or create the subscription in the next window.

我们可以生成订阅脚本或在下一个窗口中创建订阅。

Verify the details in the summary page and click on ‘Finish’ to configure the subscription.

验证摘要页面中的详细信息，然后单击“完成”以配置订阅。

In the following image, we can see that subscription is created successfully for out SQL Server FILESTREAM publisher database.

在下图中，我们可以看到为SQL Server FILESTREAM发布者数据库成功创建了预订。

You can see the subscription in the local subscriptions. We have created the publication and subscription on the same instance; therefore, you can see both publication and subscription here. If we have configured subscription database to be on a different instance, connect to it and check for the subscription.

您可以在本地订阅中看到该订阅。 我们已经在同一实例上创建了发布和订阅。 因此，您可以在此处看到发布和订阅。 如果我们已将订阅数据库配置为在其他实例上，请连接到该数据库并检查订阅。

Right-click database and launch the SQL Server replication monitor. We can see that SQL replication performance is excellent with zero latency. It shows we have replicated all the FILESTREAM table data from the publisher to subscriber.

右键单击数据库，然后启动SQL Server复制监视器。 我们可以看到，SQL复制性能出色，零延迟。 它表明我们已将所有FILESTREAM表数据从发布者复制到订阅者。

Let us verify the FILESTREAM table count on both the publisher and subscriber database.

让我们验证发布者和订阅者数据库上的FILESTREAM表计数。

--Publisher databaseSELECT Count(*) as PublisherTableCount  FROM [FileStreamDemoDB_test].[dbo].[Tbl_Support_Documents]--Subscriber databaseSELECT Count(*) as SubscriberCount  FROM [FileStreamDemoDB_test].[dbo].[Tbl_Support_Documents]

We have the same row count on both the publisher and subscriber database FILESTREAM table.

发布者和订阅者数据库FILESTREAM表上的行数相同。

We have verified the table count here, but we need to check the files in the FILESTREAM container. We should have the files in the FILESTREAM container of the subscriber database as well. In the following screenshot of the FILESTREAM container, we can see the files in the subscription database file system as well.

我们已经在这里验证了表计数，但是我们需要检查FILESTREAM容器中的文件。 我们也应该将文件放在订户数据库的FILESTREAM容器中。 在FILESTREAM容器的以下屏幕截图中，我们还可以看到订阅数据库文件系统中的文件。

Now we will insert one FILESTREAM object at the publisher database, and it should replicate to the subscriber database automatically. Run the below query on the Publisher database.

现在，我们将在发布者数据库中插入一个FILESTREAM对象，它应该自动复制到订阅者数据库。 在发布服务器数据库上运行以下查询。

Use FileStreamDemoDB_test --Publisher Database DECLARE @File varbinary(MAX);  SELECT  @File = CAST(  bulkcolumn as varbinary(max)  )  FROM  OPENROWSET(BULK 'C:\sqlshack\akshita.png', SINGLE_BLOB) as MyData;  INSERT INTO [Tbl_Support_Documents]  VALUES  (  NEWID(),  'akshita.png','Test image Repl',  @File)

Once the insert is successful, verify the FILESTREAM table count at both the publisher and subscriber end. Previously we had 11 rows, but we have 12 rows at both sides. It shows that the data is replicated successfully.

插入成功后，请在发布者和订阅者端都验证FILESTREAM表计数。 以前我们有11行，但两边都有12行。 它表明数据已成功复制。

Now let us try to insert video file in the FILESTREAM table of the publisher database using the following query.

现在，让我们尝试使用以下查询将视频文件插入发布者数据库的FILESTREAM表中。

Use FileStreamDemoDB_test --Publisher Database DECLARE @File varbinary(MAX);  SELECT  @File = CAST(  bulkcolumn as varbinary(max)  )  FROM  OPENROWSET(BULK 'C:\sqlshack\VID_20131211_191448.3gp', SINGLE_BLOB) as MyData;  INSERT INTO [Tbl_Support_Documents]  VALUES  (  NEWID(),  'VID_20131211_191448.3gp','Test Video Repl',  @File)

We got the error message that length of the LOB data (270219122) to be replicated exceeds the configured maximum 65536.

我们收到以下错误消息：要复制的LOB数据（270219122）的长度超过了配置的最大值65536。

Right click on the server instance and go to properties. In the advanced section, we can see the ‘Max text Replication size’ is defined as 65536 (64K). In the error message also, SQL Server reported this value. We need to modify this value to replicate the LOB data to the subscriber.

右键单击服务器实例，然后转到属性。 在高级部分，我们可以看到“最大文本复制大小”定义为65536（64K）。 同样在错误消息中，SQL Server报告了该值。 我们需要修改此值以将LOB数据复制到订户。

We can define ‘Max Text Replication Size’ up to 2147483647(2GB). Let us change it to the maximum value and click ‘Ok’. We do not need to restart SQL Server to activate this configuration.

我们可以定义“最大文本复制大小”，最大为2147483647（2GB）。 让我们将其更改为最大值，然后单击“确定”。 我们不需要重新启动SQL Server即可激活此配置。

Rerun the query, and it executes successfully. We can verify the FILESTREAM table count in the following image.

重新运行查询，它将成功执行。 我们可以在下图中验证FILESTREAM表的数量。

结论 (Conclusion)

In this article, we explored to replicate SQL Server FILESTREAM data to a subscriber database. It is an excellent way to publish part of the database so that it can be used for reporting purposes. We will continue to cover more articles on FILESTREAM in this series. Stay tuned!

在本文中，我们探讨了将SQL Server FILESTREAM数据复制到订户数据库。 这是发布数据库的一部分以便将其用于报告目的的绝佳方法。 在本系列中，我们将继续涵盖有关FILESTREAM的更多文章。 敬请关注！

目录 (Table of contents)

SQL Server FILESTREAM and Replication

SQL Server FILESTREAM和复制

翻译自:

filestream