SQL Server 2008的透明数据加密(二)_MySQL

SQLServer2008

上面代码的结果是：

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58'.

它起作用了。我们的数据库是安全的了。恢复或附加TestDatabase 到另一个实例中去的唯一方法是在第二个实例中添加相同的证书。学习下面的代码：

-- backup the certificate
-- The private key will be encrypted by the password specified
BACKUP CERTIFICATE DEKCertificate TO FILE = 'E:/DEKCertificate.cert'
         WITH PRIVATE KEY
         (
                  FILE = 'E:/Instance1PrivateKey.key',
                  ENCRYPTION BY PASSWORD = 'Pa$w0rd5454'
         )
-- create the certificate in the second instance by using the backup
-- Private key will be loaded from the saved file.
USE [master]
GO
CREATE CERTIFICATE DEKCertificate
         FROM FILE = 'E:/DEKCertificate.cert'
         WITH PRIVATE KEY (
                  FILE = 'E:/Instance1PrivateKey.key'
                  , DECRYPTION BY PASSWORD = 'Pa$w0rd5454'
                  )
 
这个代码的第一部分将这个证书备份到了一个文件中。它还备份了这个证书的私钥。代码中指定的密码是用于加密私钥的。代码的第二部分需要运行在第二个SQL Server 2008实例上。它使用备份证书创建了一个证书。当这个代码运行后，你将可以恢复或附加TestDatabase数据库到新的实例中去。

在激活TDE之前需要考虑什么?

在你在数据库上激活TDE之前只有很少的事情需要注意，那就是：

TDE是否影响所执行的灾难复原计划?

设想一个简单的灾难复原计划，备份和恢复。你可能开发了这个计划而且它执行没有任何问题。你激活了TDE，仍然没有问题，时间表作业备份了你的数据库。假设这个服务器开始产生严重错误导致你需要重新安装操作系统和SQL Server。你可能不做它想就轻松地重新安装，因为你有数据库备份。当数据库恢复时问题出现了。你可能具有不是加密格式的数据库完全备份，你可能有一些在激活TDE之后进行的事务型备份，所以它们是加密的。你没有用于TDE的证书备份。这导致你处于一个不可预料的境地。因为你没有所用证书的备份，所以你将不能恢复事务型备份。

想想在激活TDE之前灾难复原计划的开发。如果你有计划，那么确保这个计划在激活TDE之后仍然可用。这不只用于备份和恢复策略，它还用于其它计划，例如日志传送和数据库镜像。

在你的数据库中有只读文件组吗?

如果数据库有只读文件组，那么TDE将会失败。一旦TDE激活了，那么encryption_state的数值将永远不可能是3(加密的)而是2(加密中)。SQL Server在运行TDE代码时不会抛出任何异常。激活TDE之后，如果你打开数据库的属性窗口，你将会发现属性Encryption Enabled的值被设为了true。使用下面的代码进行测试：

-- create a new database for testing TDE on readonly file groups
USE master
GO
CREATE DATABASE [TestDatabase2] ON PRIMARY
( NAME = N'TestDatabase2_Primary', FILENAME = N'E:/TestDatabase2_Primary.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG1_Default]
( NAME = N'TestDatabase2_FG1', FILENAME = N'E:/TestDatabase2_FG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG2_ReadOnly]
( NAME = N'TestDatabase2_FG2', FILENAME = N'E:/TestDatabase2_FG2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDatabase2_log', FILENAME = N'E:/TestDatabase2_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
-- Set the FG1_Default file group as the default one.
USE [TestDatabase2]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG1_Default')
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG1_Default] DEFAULT
GO

-- Add a table to the default file group
USE [TestDatabase2]
GO
CREATE TABLE TestTable1 (Id int PRIMARY KEY, [Text] varchar(100))
GO
INSERT INTO TestTable1 VALUES (1, 'hello')

-- Add a table to the FG2_ReadOnly file group
CREATE TABLE TestTable2 (Id int PRIMARY KEY, [Text] varchar(100))
ON [FG2_ReadOnly]
GO
INSERT INTO TestTable2 VALUES (1, 'hello')
GO

-- Set the file group FG2_ReadOnly file group as READONLY
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG2_ReadOnly')
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG2_ReadOnly] READONLY
GO

-- Create Database Encryption Key (DEK) in the user database
USE TestDatabase2
GO
IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID('TestDatabase2'))
BEGIN
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE DEKCertificate
END
GO

-- Enable TDE on the database
ALTER DATABASE TestDatabase2
SET ENCRYPTION ON
GO

首先这个代码创建了一个具有三个数据文件的数据库，这三个文件叫做TestDatabase2_Primary、TestDatabase2_FG1和TestDatabase2_FG2。文件组FG1_Default 设置为默认文件组，在其中创建了TestTable1。在FG2_ReadOnly文件组中创建了TestTable2。然后FG1_ReadOnly文件组被标识为READONLY。

最后，在TestDatabase2 中创建了DEK，Encryption属性设置为true。所有的语句都成功执行。如果你查询sys.dm_database_encryption_keys，你将看到TestDatabase2的encryption_state是2，这表示加密结束了但没有完成。

是否使用了FileStream数据类型?

使用了filestream类型的数据库可以使用TDE来进行加密，但是文件流数据不会被加密。

当激活TDE之后会影响什么?

在一个数据库上激活TDE会影响以下事情：

事务日志

一旦TDE激活了，SQL Server 通过将文本数据清理出去从而确保日志文件不包含文本数据。SQL Server 从具有加密格式的新VLF开始。

TEMPDB系统数据库

当你在任何数据库上激活了TDE之后这将会自动加密。这会导致使用tempdb数据库的非加密数据库性能下降。

日志传送和数据库镜像

如果你在一个传送日志到另一个数据库的数据库(意味着激活了日志传送的数据库)上激活了TDE，那么日志传送操作将会在辅助数据库上失败，除非在辅助服务器上证书可用。

压缩备份

下面是在一个激活了TDE的数据库上进行压缩备份的测试，看起来在激活TDE的数据库上压缩并不怎么高效：　

-- create a new database for testing compressed backup on TDE enabled database
USE master
GO
CREATE DATABASE [TestDatabase3] ON PRIMARY
( NAME = N'TestDatabase3', FILENAME = N'E:/TestDatabase.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
LOG ON
( NAME = N'TestDatabase3_log', FILENAME = N'E:/TestDatabase3_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

-- Create a table and insert some records
USE TestDatabase3
GO
CREATE TABLE TestTable (Id int primary key, [Value] char(8000))
GO
INSERT INTO TestTable VALUES (1, 'value1')
INSERT INTO TestTable VALUES (2, 'value1')
INSERT INTO TestTable VALUES (3, 'value1')
INSERT INTO TestTable VALUES (4, 'value1')
INSERT INTO TestTable VALUES (5, 'value1')
INSERT INTO TestTable VALUES (6, 'value1')

-- Backup the TestDatabase3 without compressing it
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:/TestDatabase3Full.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup'
GO

-- Backup the TestDatabase3 with  compression
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:/TestDatabase3Full_Compressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (Compressed)', COMPRESSION
GO

-- Now enable TDE on this
-- Use the code we used with first example

-- Backup the TestDatabase3 again (after TDE enabled) without compressing it
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:/TestDatabase3Full_Encrypted.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (encrypted)'
GO

-- Backup the TestDatabase3 with compression
BACKUP DATABASE [TestDatabase3]
TO DISK = N'E:/TestDatabase3Full_Encrypted_Compressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (Encrypted and compressed', COMPRESSION
GO

这个代码创建一个数据库并插入一些记录到数据表中。然后这个数据库被备份两次，一次没有压缩另一次有压缩。然后你需要在这个数据库上激活TDE并执行其它的与激活TDE之前备份所使用的相同代码。备份文件规模是：

在激活TDE之前完全备份1,365 KB

在激活TDE之前有压缩的完全备份124KB

激活TDE之后的完全备份1,365 KB

激活TDE之后有压缩的完全备份 1,278 KB

你可以看到它们的不同。结果证明激活了TDE的数据库的压缩备份文件不那么高效。