SQL Server WITH ENCRYPTION VS TDE

在SQL Server 2014（12.x）版本中，在BACKUP DATABASE的参数中增加了ENCRYPTION参数，即备份时，对写入文件的数据进行加密。那么该参数是如何使用的呢？WITH ENCRYPTION 加密对性能有怎样的影响？其和TDE有怎样的关系，两者对性能影响情况如何？本文将通过实例说明，给出该参数用法，并与TDE进行对比。

创建测试环境

创建数据库test，并在test库下创建表test，向表中插入一条记录：

USE master

CREATE DATABASE test ON PRIMARY

(name='test',filename='D:\DB\Test\test.mdf')

LOG ON

(name='test_log',filename='D:\DB\Test\test_log.ldf')

USE test

CREATE TABLE test ( id INT, name VARCHAR(10) )

INSERT INTO test

VALUES ( 1, 'Jack' )

要使用WITH ENCRYPTION 参数备份数据库，和TDE一样，首先需要创建数据库主秘钥、服务器证书，并备份证书：

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD= 'jC`T?]K,v`{voD<'

CREATE CERTIFICATE TestBackOption

WITH SUBJECT = 'My DEK Certificate for test database backup option'

BACKUP CERTIFICATE TestBackOption

TO FILE ='D:\TestBackOption.cer'

WITH PRIVATE KEY(FILE='D:\TestBackOption_Key.pvk',

ENCRYPTION BY PASSWORD='[J,XJK8|AwE*rLk')

使用ENCRYPTION备份选项，在备份时，对备份文件加密：

BACKUP DATABASE [test]

TO DISK = N'D:\TestWithBackOptionEncryption.bak'

WITH

COMPRESSION,

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = TestBackOption

STATS = 10

注意，和TDE不用一样，需要创建数据库秘钥，然后在将数据库文件数据进行加密。使用ENCRYPTION加密备份文件，是在备份时，将数据加密后写入备份文件。

接下来，我们使用记事本打开数据库备份文件，查找“Jack”，我们发现，查不到明文“Jack”，验证确实达到了加密的效果。

WITH ENCRYPTION 有两个参数，分别是加密算法和加密方式，我们可以选用的加密算法包括： AES 128、AES 192、AES 256 和 Triple DES；加密方式可以是证书或非对称密钥。

日志备份、文件或文件组备份时也可以使用ENCRYPTION参数对数据进行加密。

使用TDE加密的数据库，仍然可以使用ENCRYPTION参数进行加密。

备份时加密的备份文件，还原的时候和TDE数据库一样，也需要先在实例上创建主秘钥，还原服务器证书、私钥，并不需要在还原脚本中增加参数。

从上文可以看出，相对TDE，WITH ENCRYPTION数据库备份方式，是在备份时将数据加密后写入备份文件的，这样我们可以合理安排备份策略，避开高峰期数据读写加密、解密对数据库性能的影响。那么我们是不是就可以弃用TDE了呢？使用数据库备份时对文件进行加密会有什么影响吗？

第一个问题很好回答，首先，因为数据库备份时对备份文件进行加密是SQL Server 2014（12.x）版本的新功能，SQL Server 2014以前的版本是不能使用的，所以还是不能直接替代的，除非您的组织愿意花费成本去将SQL Server升级到 2014或者更高的版本。其次因为数据库中的数据是没有进行加密的，线上数据更容易被黑客盗取。综上两个原因，TDE仍然是不可替代的。

下面我们将通过试验对比数据库备份时对数据库备份文件进行加密和TDE，我们使用《SQL Server 透明数据加密（TDE）的影响》一文中test数据库，为了保证数据库数据量一致，减少其他因素的影响，我们先使用WITH ENCRYPTION加密选项对数据库test进行备份：

DECLARE @bdatetime AS DATETIME=GETDATE()

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE [test]

TO DISK = N'D:\TestWithBackOptionEncryption.bak'

WITH

COMPRESSION,

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = TestBackOption

STATS = 10

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

SELECT @bdatetime,GETDATE(),DATEDIFF(millisecond,@bdatetime,GETDATE())

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 3 页。

BACKUP DATABASE 成功处理了 328595 页，花费 62.830 秒(40.858 MB/秒)。

SQL Server 执行时间:

CPU 时间 = 265 毫秒，占用时间 = 63160 毫秒。

SQL Server 执行时间:

CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

对test库启动TDE

加密算法和数据库备份选项中的加密算法一样，均使用AES_256

USE test

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM= AES_256

ENCRYPTION BY SERVER CERTIFICATE TestBackOption

ALTER DATABASE test SET ENCRYPTION ON

查看动态视图 sys.dm_database_encryption_keys 中的列percent_complete ，确认其重新变为0后，说明加密完成：

SELECT DB_NAME(database_id) DBName, percent_complete

FROM sys.dm_database_encryption_keys

接下来我们来备份TDE数据库：

USE test

DECLARE @bdatetime AS DATETIME=GETDATE()

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE [test]

TO DISK = N'D:\Test_TDE.bak'

WITH STATS = 10

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

SELECT @bdatetime,GETDATE(),DATEDIFF(millisecond,@bdatetime,GETDATE())

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328544 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 3 页。

BACKUP DATABASE 成功处理了 328547 页，花费 138.508 秒(18.531 MB/秒)。

SQL Server 执行时间:

CPU 时间 = 157 毫秒，占用时间 = 138843 毫秒。

SQL Server 执行时间:

CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

完成时间: 2020-02-26T14:04:50.9311361+08:00

可以看到TDE数据库备份消耗的时间是备份时加密备份文件消耗时间的1倍左右，而两者CPU消耗时间近乎一致，因为TDE数据库的备份是先加密后解密的过程。

结合《SQL Server 透明数据加密（TDE）的影响》，如果您已经在使用SQL Server 2014（12.x）或者更高版本的SQL Server 数据库，并且您已经使用其他安全措施，保证数据库数据的安全。可以考虑使用备份时对备份文件加密，即使用的备份的参数：

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = TestBackOption

)

注意：但TDE是不可替代的，因为备份时对备份文件加密，存在的安全隐患，即生产环境数据库文件是没有加密的，此数据库文件被窃，或者被未授权的用户访问，或者被黑客攻击，仍然会暴露组织的隐私数据。

————————————————

原文链接：https://blog.csdn.net/zhoujunah/article/details/105946752