透明加密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 sc