透明加密tde_如何在SQL Server中监视和管理透明数据加密（TDE）

透明加密tde

Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).

透明数据加密（TDE）最初是在SQL Server 2008（企业版）中引入的，目的是保护静态SQL Server数据。 换句话说，物理数据和日志文件以及位于文件系统上的数据库备份都受到保护（加密）。

实施TDE时需要注意的几件事： (Few things to be aware of when implementing TDE:)

With TDE, The data transmitted over the network is not encrypted and the data at the object level remains unencrypted. In other words, if a user has select access to a table(s) within the TDE enabled database, he/she will be able to read data with simple select statements, as the name suggests, it’s transparent. TDE does not protect FILESTREAM data and any files related to Buffer Pool Extension (BPE) are not encrypted as well, you should use file system encryption tools like windows BitLocker or any other third party tools for this purpose. Another caveat is TDE doesn’t support Instant File Initialization for database files. Also, when TDE is enabled on a user database your tempdb database gets encrypted behind the scenes.

使用TDE，不会加密通过网络传输的数据，并且对象级别的数据仍未加密。 换句话说，如果用户可以选择启用TDE的数据库中的表，则他/她将能够使用简单的select语句读取数据，顾名思义，它是透明的。 TDE不会保护FILESTREAM数据，并且也不会对与缓冲池扩展（BPE）相关的任何文件进行加密，为此，您应该使用Windows BitLocker等文件系统加密工具或任何其他第三方工具。 另一个警告是TDE不支持数据库文件的即时文件初始化。 同样，在用户数据库上启用TDE后，您的tempdb数据库也会在后台进行加密。

Okay, let’s move on to our topic. In this article, we will see how to monitor and manage TDE progress, not essentially how to setup TDE on a user database. Before moving on to our main topic, here is a quick refresher on how Transparent Data Encryption works.

好的，让我们继续我们的主题。 在本文中，我们将看到如何监视和管理TDE进度，而不是本质上如何在用户数据库上设置TDE。 在继续讨论我们的主要主题之前，这里快速介绍一下透明数据加密的工作原理。

Enabling TDE on a given database is a very straightforward process. 20,000-foot view of the process is basically creating a DMK (Master DB) which is protected by Service Master Key, Cert (Master DB), DEK (User DB) and enable TDE(User DB) and you are done. But things get little tricky when you are dealing with VLDBs. What if you have a ginormous database (Let’s say a 30 TeraBytes monster) on which you have to enable TDE? Enabling TDE is not instantaneous, the SQL Server Encryption Scanner has to read all the underlying database pages and encrypt them, For a 30 TB database it might take multiple days for SQL Server to encrypt the entire database and we as DBAs should monitor the encryption progress making sure there are no side effects. In this context I am not talking about server resources, I am talking about the impact on the transaction log file when encryption scanner is in progress. So, why should we worry about LDF file when TDE scanner is in progress? Well, SQL Server doesn’t truncate the transaction log file of your database when TDE Encryption Scanner is doing its job. Things get more complicated if you are not allowed to run TDE during business hours and let’s say you have nightly ETL loads or some other scheduled job(s) which generates a considerable amount of log records. In this article, let’s see how to monitor TDE progress and how to manage transaction log when TDE scanner is in progress.

在给定的数据库上启用TDE是一个非常简单的过程。 该过程的20,000英尺视图基本上是在创建一个DMK（主数据库），该DMK受服务主密钥，证书（主数据库），DEK（用户数据库）和启用TDE（用户数据库）保护，您就完成了。 但是，当您处理VLDB时，事情变得有些棘手。 如果您有一个庞大的数据库（比如说一个30 TB的怪物）必须启用TDE，该怎么办？ 启用TDE并不是立即进行的，SQL Server加密扫描程序必须读取所有基础数​​据库页面并对其进行加密。对于30 TB的数据库，SQL Server可能需要花费几天的时间来加密整个数据库，因此，作为DBA，我们应该监视加密进度确保没有副作用。 在这种情况下，我不是在谈论服务器资源，而是在讨论加密扫描程序进行时对事务日志文件的影响。 那么，为什么在TDE扫描仪进行过程中为什么要担心LDF文件呢？ 好的，当TDE加密扫描程序完成其工作时，SQL Server不会截断数据库的事务日志文件。 如果不允许您在工作时间内运行TDE，并且假设您每晚进行ETL负载或某些其他计划的作业，这些作业会生成大量的日志记录，那么事情就会变得更加复杂。 在本文中，让我们看看如何在TDE扫描程序进行过程中监视TDE进度以及如何管理事务日志。

正在进行TDE时要监视的事情： (Things to monitor when TDE is in progress:)

• Disk IO and CPU usage.
  磁盘IO和CPU使用率。
• Keep an eye on blocking – Encryption_Scan resource can cause blocking.
  密切注意阻止– Encryption_Scan资源可能导致阻止。
• Last but not the least, keep an eye on your T-Log. As mentioned earlier, when TDE scanner is running the T-Log can’t be truncated. In other words, the log file might grow larger than normal, something to watch out if your server is storage constrained.
  最后但并非最不重要的一点，请注意您的T-Log。 如前所述，当TDE扫描仪运行时，T-Log不能被截断。 换句话说，日志文件可能会比正常情况更大，如果您的服务器存储受限，则需要注意。

如何监视TDE进度： (How to monitor TDE Progress:)

SQL Server keeps track of the encryption progress and we can pull that information by querying sys.dm_database_encryption_keys. Particularly ‘Percent_Complete’ and ‘encryption_state’ are the two columns which are required to understand the progress of TDE. ‘Encryption_state’ column returns an integer value (0-6) which indicates the encryption status of the database and ‘percent_complete’ column tells us percent complete of the DB encryption state change.

SQL Server跟踪加密进度，我们可以通过查询sys.dm_database_encryption_keys来获取该信息。 特别是“ Percent_Complete”和“ encryption_state”是了解TDE进度所需的两列。 “ Encryption_state”列返回一个整数值（0-6），该值指示数据库的加密状态，“ percent_complete”列告诉我们数据库加密状态更改的完成百分比。

Encryption_state(int)	Description
0	No database encryption key present, no encryption
1	Unencrypted
2	Encryption in progress
3	Encrypted
4	Key change in progress
5	Decryption in progress
6	Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed)

Encryption_state（int）	描述
0	没有数据库加密密钥，没有加密
1个	未加密
2	正在进行加密
3	加密的
4	正在进行关键变更
5	正在进行解密
6	正在进行保护更改（正在更改对数据库加密密钥进行加密的证书或非对称密钥）

Below T-SQL statement can be used to monitor TDE progress/status.

下面的T-SQL语句可用于监视TDE进度/状态。

Script 1:

脚本1：

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0'  THEN  'No database encryption key present, no encryption'
         WHEN '1'  THEN  'Unencrypted'
         WHEN '2'  THEN  'Encryption in progress'
         WHEN '3'  THEN  'Encrypted'
         WHEN '4'  THEN  'Key change in progress'
         WHEN '5'  THEN  'Decryption in progress'
         WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No Status'
         END,
percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

The output of above query comes really handy to manage TDE, Now let’s move on to managing TDE.

上面查询的输出对于管理TDE非常方便，现在让我们继续管理TDE。

如何管理TDE扫描器： (How to manage TDE scanner:)

As discussed earlier, if you are dealing with a VLDB and you are not allowed to let TDE encryption scanner run in business hours or if you see any performance issues and would like to halt the process temporarily, it sounds like your only option is to PAUSE TDE scanner and resume later. But there is no such thing as ‘ALTER database db_name SET encryption PAUSE’ in SQL Server and also we can’t leverage resource governor to lower the priority of TDE scanner because it starts as a background process. So, what are our options here to control TDE? Using a Trace Flag.

如前所述，如果您正在处理VLDB，并且不允许T​​DE加密扫描器在工作时间内运行，或者您看到任何性能问题并希望暂时停止该过程，那么听起来唯一的选择就是暂停TDE扫描仪，稍后再恢复。 但是在SQL Server中没有“ ALTER database db_name SET encryption PAUSE”这样的东西，而且我们不能利用资源调控器降低TDE扫描程序的优先级，因为它是作为后台进程启动的。 那么，我们在这里可以选择什么来控制TDE？ 使用跟踪标志。

Yes, you heard it right. We can pause and resume TDE scanner process using trace flag 5004. When this trace flag gets enabled, SQL Server will continue to keep encryption_state of 2 (Encryption in progress) when we query sys.dm_database_encryption_keys with a percent complete of 0. To resume the TDE scanner process, all we need to do is disable the trace flag and run ‘ALTER DATABASE db_name SET ENCRYPTION ON’. The database is not considered fully encrypted (TDE enabled) until the scanner process is 100% complete and the encryption_state column has changed to value 3 (Encrypted).

是的，您没听错。 我们可以使用跟踪标志5004暂停和恢复TDE扫描器过程。启用此跟踪标志后，当我们查询完成百分比为0的sys.dm_database_encryption_keys时，SQL Server将继续保持cryptom_state为2（正在进行加密）。在TDE扫描器过程中，我们需要做的就是禁用跟踪标志并运行“ ALTER DATABASE db_name SET ENCRYPTION ON” 。 直到扫描程序完成100％的操作，并且encryption_state列的值更改为3（已加密），数据库才被视为完全加密（启用TDE）。

注意： (Note:)

Don’t turn off encryption in panic by running ‘ALTER DATABASE DB_Name SET ENCRYPTION OFF’ if you want to halt the process temporarily. It doesn’t stop/halts TDE, instead, it starts decrypting whatever it encrypted so far which has just as much impact and eventually you have to start the encryption process from scratch. Bummer!

如果要暂时停止该过程，请不要通过运行“ ALTER DATABASE DB_Name SET ENCRYPTION OFF”来关闭加密功能。 它不会停止/停止TDE，相反，它会开始解密到目前为止已加密的任何内容，这会产生相同的影响，最终您必须从头开始加密过程。 mm！

示范： (Demonstration:)

I have a database named ‘TDE_Monit’ (around 10 GB in size) on which I will be enabling TDE, pause and resume TDE Scanner for our demo purposes. In the below screenshots steps 1 through 3 are to prepare my database for TDE and as soon as I run step 4, I got a message saying ‘command(s) completed successfully’, That doesn’t mean my database got encrypted successfully. SQL Server is just notifying me that the encryption scanner began to run successfully.

我有一个名为“ TDE_Monit”（大小约为10 GB）的数据库，出于演示目的，我将在该数据库上启用TDE，暂停和恢复TDE Scanner。 在下面的屏幕截图中，步骤1到3是为TDE准备数据库，而在运行步骤4时，我收到一条消息，说“命令成功完成”，这并不意味着数据库已成功加密。 SQL Server只是通知我加密扫描程序开始成功运行。

Now in another tab, I am running the query (Script 1) provided above and you can see the status as shown below (Encryption State = 2 and percent complete not Zero).

现在在另一个选项卡中，我正在运行上面提供的查询（脚本1），您可以看到如下所示的状态（加密状态= 2，完成百分比不为零）。

Assuming I did notice performance issue and would like to pause TDE, I enabled trace flag 5004 by running DBCC TRACEON(5004,-1) in a separate window and now when I run my monitoring query, I see below.

假设我确实注意到性能问题并想暂停TDE，我通过在单独的窗口中运行DBCC TRACEON（5004，-1）启用了跟踪标志5004，现在当我运行监视查询时，将看到以下内容。

As mentioned earlier, when this trace flag gets enabled the encryption state remains status 2 but the percent_complete goes to zero percent (since nothing is in progress). At this point, you should be able to truncate the transaction log if needed.

如前所述，启用此跟踪标志后，加密状态将保持状态2，但percent_complete变为零百分比（因为没有任何进展）。 此时，如果需要，您应该能够截断事务日志。

To resume TDE scanner, all we have to do is to disable the trace flag and re-enable TDE on the database. Note: Just disabling the trace flag will not resume the encryption scan.

要恢复TDE扫描器，我们要做的就是禁用跟踪标志并重新启用数据库上的TDE。 注意：仅禁用跟踪标志将不会恢复加密扫描。

DBCC TRACEOFF(5004,-1)
 
ALTER DATABASE TDE_Monit
SET ENCRYPTION ON;

After running the above SQL statements, TDE process resumed from where it left off as shown in the below screenshot.

运行上述SQL语句后，TDE进程从中断处恢复，如下面的屏幕快照所示。

After letting it run for a while, TDE process completed successfully and below is the final status.

让它运行一段时间后，TDE流程成功完成，以下是最终状态。

FYI, below are the series of events from SQL Server error log (Read from bottom to top).

仅供参考，以下是SQL Server错误日志中的一系列事件（从下至上读取）。

结论： (Conclusion:)

Enabling TDE is not instantaneous and the progress should be monitored to avoid potential issues. Keep in mind the database is not fully encrypted until the encryption scanner process completes and the encryption_state has changed to 3 and remember there is no rollback for TDE. Basically, once begun, to disable encryption on a database you must allow the encryption scanner process to finish first.

启用TDE并不是立即完成的，应监控进度，以避免潜在的问题。 请记住，直到加密扫描器进程完成并且encryption_state更改为3为止，数据库才被完全加密，并且记住TDE没有回滚。 基本上，一旦开始，要在数据库上禁用加密，必须首先允许加密扫描程序完成。

参考文献： (References:)

• Transparent Data Encryption (TDE) 透明数据加密（TDE）
• Setting up TDE on SQL Server Failover Cluster 在SQL Server故障转移群集上设置TDE
• sys.dm_database_encryption_keys (Transact – SQL) sys.dm_database_encryption_keys（事务– SQL）

翻译自: https://www.sqlshack.com/how-to-monitor-and-manage-transparent-data-encryption-tde-in-sql-server/

透明加密tde