This article is the continuation of the SQL FILESTREAM series.
本文是SQL FILESTREAM系列的继续。
SQL FILESTREAM is an exciting feature to store the objects into the FILESTREAM container but we might need to use the FILESTREAM database along with other SQL Server features. In this article, we are going to explore the SQL FILESTREAM feature compatibility with other SQL Server features.
SQL FILESTREAM是一项令人兴奋的功能，可以将对象存储到FILESTREAM容器中，但是我们可能需要将FILESTREAM数据库与其他SQL Server功能一起使用。 在本文中，我们将探讨SQL FILESTREAM功能与其他SQL Server功能的兼容性。
数据库快照和SQL FILESTREAM (Database Snapshots and SQL FILESTREAM)
Database Snapshots provide a read-only and static copy of the SQL Server database. They create a blank copy of the source database. If we try to access the data from a Snapshot database, it points to the source database. Once we perform any changes to the source database, the original page is copied to the snapshot database. In the following image, you can see the source and snapshot database. The snapshot database contains the original pages before the modification. In the following example, page 3 is copied to the snapshot database before modification in the source database.
数据库快照提供SQL Server数据库的只读和静态副本。 他们创建源数据库的空白副本。 如果我们尝试从Snapshot数据库访问数据，则它指向源数据库。 对源数据库执行任何更改后，原始页面将被复制到快照数据库。 在下图中，您可以看到源数据库和快照数据库。 快照数据库包含修改前的原始页面。 在以下示例中，在源数据库中进行修改之前，第3页已复制到快照数据库。
In this article, we will focus on the Database Snapshot for a SQL FILESTREAM database. We already have a FILESTREAM database configured in our environment. We can get the information about data and log file using sys.master_files.
在本文中，我们将重点介绍SQL FILESTREAM数据库的数据库快照。 我们已经在我们的环境中配置了FILESTREAM数据库。 我们可以使用sys.master_files获得有关数据和日志文件的信息。
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'FileStreamDemoDB_test')
In the FILESTREAM database, we have the primary data file along with the FILESTREAM container. The FILESTREAM container stores the objects in the file system.
在FILESTREAM数据库中，我们具有主数据文件以及FILESTREAM容器。 FILESTREAM容器将对象存储在文件系统中。
In the following query, we created the Snapshot database on the Primary data file for the FILESTREAM database.
在以下查询中，我们在FILESTREAM数据库的Primary数据文件上创建了Snapshot数据库。
CREATE DATABASE FileStreamDemoDB_Snapshot ON PRIMARY
(NAME = FileStreamDemoDB, FILENAME = 'C:\sqlshack\SQLDB\FileStreamDemoDB_test.snap')
AS SNAPSHOT OF FileStreamDemoDB_test;
GO
We can access the Database Snapshots in a separate folder under Databases. Look at the camera icon in front of a Database Snapshot. It shows that the Database Snapshot is an image of the source database.
我们可以在“数据库”下的单独文件夹中访问“数据库快照”。 查看数据库快照前面的相机图标。 它显示数据库快照是源数据库的映像。
We can access data from the user table stored in the primary data file.
我们可以从存储在主数据文件中的用户表访问数据。
SELECT TOP (1000) [CustomerID]
,[CustomerName]
,[Country]
FROM [FileStreamDemoDB_Snapshot].[dbo].[customers]
Now, let us try to access the FILESTREAM table.
现在，让我们尝试访问FILESTREAM表。
SELECT TOP (1000) [FileId]
,[FileName]
,[File]
FROM [FileStreamDemoDB_Snapshot].[dbo].[DemoFileStreamTable_1]
We received the following error message. It indicates that large object data for table resides on an offline filegroup. It considers the FILESTREAM filegroup as on offline filegroup because we did not include it while creating a database snapshot.
我们收到以下错误消息。 它指示表的大对象数据驻留在脱机文件组上。 它将FILESTREAM文件组视为脱机文件组，因为我们在创建数据库快照时未将其包括在内。
SQL FILESTREAM stores the FILESTREAM objects into the FILESTREAM filegroup. It stores the rest of data in primary filegroup. We did not specify FILESTREAM filegroup while creating the Database Snapshot. We can still query the FILESTREAM table without specifying the FILESTREAM column.
SQL FILESTREAM将FILESTREAM对象存储到FILESTREAM文件组中。 它将其余数据存储在主文件组中。 创建数据库快照时，我们未指定FILESTREAM文件组。 我们仍然可以在不指定FILESTREAM列的情况下查询FILESTREAM表。
We have the following FILESTREAM column in this example.
在此示例中，我们具有以下FILESTREAM列。
Rerun the select statement without specifying a FILESTREAM column.
在不指定FILESTREAM列的情况下重新运行select语句。
SELECT TOP (1000) [FileId,[FileName]
FROM [FileStreamDemoDB_Snapshot].[dbo].[DemoFileStreamTable_1]
We get the result as follows.
我们得到的结果如下。
We can create multiple Database Snapshots for a database. We need to provide a unique name for the Database Snapshot regardless it is pointing to a similar database. Let us create another Database Snapshot with FILESTREAM filegroup as well. Execute the following query to create a Database Snapshot on FILESTREAM filegroup as well.
我们可以为一个数据库创建多个数据库快照。 无论数据库快照指向相似的数据库，我们都需要为其提供唯一的名称。 让我们也使用FILESTREAM文件组创建另一个数据库快照。 执行以下查询以在FILESTREAM文件组上创建数据库快照。
CREATE DATABASE FileStreamDemoDB_Snapshot_FS ON PRIMARY
(NAME = FileStreamDemoDB, FILENAME = 'C:\sqlshack\SQLDB\FileStreamDemoDB_test.snap')
, FILEGROUP [DemoFileStream] CONTAINS FILESTREAM(
NAME = DemoFiles,
FILENAME = 'C:\sqlshack\Demo\DemoFiles_fs')
AS SNAPSHOT OF FileStreamDemoDB_test;
GO
We receive the following error message.
我们收到以下错误信息。
We can create a database snapshot of a SQL FILESTREAM enabled database however; it must not include the FILESTREAM filegroup. The database Snapshot does not support the FILESTREAM property. If you are using the database for the relational tables as well as FILESTREAM feature also, Database Snapshot allows querying the tables pointing to source database easily. In this case, we should be cautious not to include FILESTREAM columns in select statements.
但是，我们可以为启用了SQL FILESTREAM的数据库创建数据库快照。 它不能包含FILESTREAM文件组。 数据库快照不支持FILESTREAM属性。 如果您还将数据库用于关系表以及FILESTREAM功能，则数据库快照可轻松查询指向源数据库的表。 在这种情况下，我们应该谨慎，不要在select语句中包括FILESTREAM列。
Drop the Database Snapshot similar to a standard database using the Drop Database statement.
使用“ 删除数据库”语句删除与标准数据库相似的数据库快照。
USE [master]
GO
DROP DATABASE [FileStreamDemoDB_Snapshot]
GO
透明数据加密和SQL FILESTREAM (Transparent Data Encryption and SQL FILESTREAM)
Transparent Data Encryption ( TDE) encrypts and decrypts data and log files in real-time. TDE protects data and log file by encrypting them with certificates. We cannot access data without decrypting with a valid certificate.
透明数据加密（TDE）实时加密和解密数据和日志文件。 TDE通过使用证书加密数据和日志文件来保护它们。 如果不使用有效证书解密，我们将无法访问数据。
You can see the overall TDE process in this diagram (Reference article TDE)
您可以在此图中看到整个TDE流程（参考文章TDE ）
Suppose we are using the FILESTREAM database for the standard database activities along with the FILESTREAM functionality. We require SQL Server data to be protected. It will also require encrypting data stored in FILESTREAM container as well.
假设我们将FILESTREAM数据库与FILESTREAM功能一起用于标准数据库活动。 我们要求保护SQL Server数据。 它还将需要加密存储在FILESTREAM容器中的数据。
We can enable the TDE for the FILESTREAM database. However, it does not protect FILESTREAM data. We can take reference of MSDN for the combination of TDE and SQL FILESTREAM.
我们可以为FILESTREAM数据库启用TDE。 但是，它不保护FILESTREAM数据。 对于TDE和SQL FILESTREAM的组合，我们可以参考MSDN 。
- “FILESTREAM data is not encrypted even when TDE is enabled.”“即使启用了TDE，FILESTREAM数据也不会被加密。”
SQL FILESTREAM和数据库镜像 (SQL FILESTREAM and Database Mirroring)
SQL Server database mirroring is a high availability feature of the SQL Server database. It prepares a primary and secondary copy a database between two different SQL Server instances. In case of any issues with the primary instance goes down, the secondary database becomes available for the users.
SQL Server数据库镜像是SQL Server数据库的高可用性功能。 它准备两个不同SQL Server实例之间的数据库的主副本和辅助副本。 如果主实例出现任何问题，辅助数据库将对用户可用。
We might require configuring database mirroring on a FILESTREAM enabled database. SQL Server does not allow configuring database mirroring on a FILESTREAM database. It is enabled on a database level. Therefore, we cannot configure mirroring on this database. If we have already configured the database mirroring, we cannot modify this database to use the FILESTREAM feature. SQL Server does not allow adding a FILESTREAM filegroup to the existing mirrored database as well.
我们可能需要在已启用FILESTREAM的数据库上配置数据库镜像。 SQL Server不允许在FILESTREAM数据库上配置数据库镜像。 在数据库级别启用它。 因此，我们无法在此数据库上配置镜像。 如果已经配置了数据库镜像，则无法修改该数据库以使用FILESTREAM功能。 SQL Server也不允许将FILESTREAM文件组添加到现有的镜像数据库。
You can still go to database properties and go to Mirroring.
您仍然可以转到数据库属性，然后转到Mirroring 。
You can still go to configure database mirroring between Principal and Mirror instances but in a later stage when we try to start mirroring you will get an error message.
您仍然可以在Principal实例和Mirror实例之间配置数据库镜像，但是在稍后的阶段，当我们尝试启动镜像时，您会收到一条错误消息。
- A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage (Microsoft SQL Server, Error 5574)无法同时为数据库镜像和FILESTREAM或数据库镜像和MEMORY_OPTIMIZED_DATA存储启用数据库（Microsoft SQL Server，错误5574）
You can check the corresponding error message in sys.messages table as well. In the following query, we filtered the error 5574 in English language ( language_id 1033).
您也可以在sys.messages表中检查相应的错误消息。 在以下查询中，我们以英语（language_id 1033）过滤了错误5574。
select message_id, text from sys.messages
where message_id=5574 and language_id=1033
SQL FILESTREAM requires high usage of the log data compared to a standard database. We might insert or update the FILESTREAM object, and it requires changes to be captured in the log and sent over the mirror database. In case of update statement, FILESTREAM creates another copy of the FILESTREAM object, which again creates overhead for the SQL Server. Due to this reason, SQL Server does not allow configuring database mirroring on FILESTREAM database.
与标准数据库相比，SQL FILESTREAM需要大量使用日志数据。 我们可能会插入或更新FILESTREAM对象，并且它要求将更改捕获到日志中并通过镜像数据库发送。 如果使用update语句，则FILESTREAM创建FILESTREAM对象的另一个副本，这又为SQL Server创建开销。 由于这个原因，SQL Server不允许在FILESTREAM数据库上配置数据库镜像。
SQL FILESTREAM和日志传送 (SQL FILESTREAM and log shipping)
SQL Server log shipping works on taking regular log backups of the database and restoring on the secondary database in another instance. It provides high availability in case of any issues on the primary database. In my earlier articles (see TOC at the bottom), we explored the log backups for the FILESTREAM database. We can configure log shipping for SQL FILESTREAM database. Both the instances should be running SQL Server 2008 R2 or later version.
SQL Server日志传送适用于对数据库进行常规日志备份并在另一个实例中在辅助数据库上进行还原。 如果主数据库上有任何问题，它可以提供高可用性。 在我以前的文章（请参阅底部的TOC）中，我们探讨了FILESTREAM数据库的日志备份。 我们可以为SQL FILESTREAM数据库配置日志传送。 两个实例都应运行SQL Server 2008 R2或更高版本。
You can understand log shipping better by viewing the following image.
通过查看下图，您可以更好地了解日志传送。
To configure log shipping, right click on the database and click on Ship Transaction Logs.
要配置日志传送，请右键单击数据库，然后单击“传送事务日志” 。
It opens the log shipping configuration wizard. Configure the network path on the source and destination SQL Server instance, backup, copy and restore job frequency, database recovery state (Standby or NoRecovery mode).
它打开日志传送配置向导。 在源和目标SQL Server实例上配置网络路径，备份，复制和还原作业频率，数据库恢复状态（待机或NoRecovery模式）。
In the SQL FILESTREAM database, log backup includes FILESTREAM container objects as well. It might cause a larger log file size. We need to consider the following points before the log-shipping configuration.
在SQL FILESTREAM数据库中，日志备份也包括FILESTREAM容器对象。 这可能会导致更大的日志文件大小。 在配置日志传送之前，我们需要考虑以下几点。
- A network drive (Source instance) for transaction log backup should have sufficient free space 用于事务日志备份的网络驱动器（源实例）应具有足够的可用空间
- Log shipping copies the backups to the destination server directory. It should also have sufficient free space considering huge log backup size also 日志传送会将备份复制到目标服务器目录。 考虑到巨大的日志备份大小，它还应该具有足够的可用空间
- If the log backup size is huge, it might take time for the log shipping to copy the log backup from source to destination server. It might influence the network bandwidth. Therefore, you should consider this as well before implementation 如果日志备份大小很大，则日志传送可能需要一些时间才能将日志备份从源服务器复制到目标服务器。 它可能会影响网络带宽。 因此，在实施之前，您也应该考虑这一点
- If there are multiple changes in the FILESTREAM database, log backup might take longer to copy FILESTREAM container objects into log backups. You should also set the log backup frequency considering this point as well 如果FILESTREAM数据库中有多个更改，则日志备份可能需要更长的时间才能将FILESTREAM容器对象复制到日志备份中。 您还应该考虑这一点来设置日志备份频率
- Log backup’s size 日志备份的大小
- We need to monitor the time taken in copying log backups to a destination server 我们需要监视将日志备份复制到目标服务器所花费的时间
- Restoration time for the log backups in destination server 目标服务器中日志备份的恢复时间
- You should also set SQL Server Agent job failure alerts to avoid any excessive log growth 您还应该设置SQL Server代理作业失败警报，以避免任何过多的日志增长
982
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
