Backup and restoration of transparent data encryption (TDE) Library

Think of TDE (Transparent Data Encryption ).

Tde msdn description:

TDE performs real-time I/O encryption and decryption on data and log files. This encryption uses the database encryption key (DEK), which is stored in the database Boot Record for recovery. DEK is a symmetric key protected by a certificate stored in the master database of the server, or an asymmetric key protected by the EKM module. TDE protects "Sleep" data, that is, data and log files. It provides the ability to comply with many laws, regulations and standards established by various industries. Software developers can use AES and 3DES encryption algorithms to encrypt data without changing existing applications.
What attracted me was "No need to change existing applications", because I needed an encrypted library to serve a very stable system, and DBA was controllable in doing everything.

TDE encryption architecture:

Test process:Copy codeThe Code is as follows: -- create a Master Key)
USE master
GO
-- DROP MASTER KEY
Create master key encryption by password = n' 1qaz @ wsx ';
GO
-- Backup master key
Backup master key to file = n'c: \ master_key.cer'
Encryption by password = n '! Qaz2wsx'
GO
-- Create a master key-based certificate. Database Encryption Key)
-- Drop certificate SDB_Cert
Create certificate SDB_Cert
With subject = n' Certificate for secretdb'
Go
-- Use the private key encryption method to back up the certificate of the master key
Backup certificate SDB_Cert
To file = n'c: \ SDB_Cert.cer'
WITH PRIVATE KEY
(
FILE = n'c: \ SDB_Cert.pvk ',
Encryption by password = '! Qaz2wsx'
)
GO
-- Create a test database SecretDB
USE master
GO
Create database SecretDB
GO
USE SecretDB
GO
Create table SDB_TB
(Id int, val nvarchar (20 ));
Insert into SDB_TB
VALUES (1, N 'A'), (2, N 'B'), (3, N 'C ');
GO
USE SecretDB
Go
-- Create a database encryption key
CREATE DATABASE ENCRYPTION KEY
With algorithm = AES_128
Encryption by server certificate SDB_Cert;
GO
-- Enable database encryption
USE SecretDB
Go
Alter database SecretDB SET ENCRYPTION ON
Go
-- Backup SecretDB for subsequent recovery tests
USE master
Go
Backup database SecretDB to disk = n' D: \ SecretDB. Bak'
Go
After completing these steps, the SecretDB database is encrypted and the encrypted backup file is obtained. Next, you need to restore the backup file on another server.
I copied SecretDB. bak, SDB_Cert.cer, and SDB_Cert.pvk to another server. If you restore the file directly, an error is returned. You must create an encrypted certificate to restore the database backup. My goal has been achieved!
-- Restore the backup of SecretDB on a different machine
USE master
GO
Create database SecretDB
GO
Restore database SecretDB
From disk = n' D: \ SecretDB. Bak'
WITH REPLACE
GO
-- Message 33111, level 16, status 3, 1st rows
-- The server certificate with the fingerprint '0x010600000000000900000009c529ffd5c7fd72fd0aae9edf46c5f69946ffed0' cannot be found.
-- Message 3013, level 16, status 1, 1st rows
-- The restore database is being terminated abnormally.
Create a certificate and restore it.
USE master
GO
Create certificate SDB_Cert
From file = n'c: \ SDB_Cert.cer'
WITH PRIVATE KEY
(
FILE = n'c: \ SDB_Cert.pvk ',
Decryption by password = n '! Qaz2wsx'
)
GO
Restore database SecretDB
From disk = n' D: \ SecretDB. Bak'
WITH REPLACE
GO

Summary:
In fact, before TDE should carefully read the description of the BOL: ms-help: // MS. SQLCC. v10/MS. SQLSVR. v10.zh-CHS/s10de_4deptrbl/html/c75d0d4b-4008-4e71-9a9d-cee2a566bd3b.htm
It says:
If TDE is used to encrypt the database, backup compression cannot significantly compress the backup storage.
Replication does not automatically copy data from databases with TDE enabled in encrypted form. To protect distribution and subscription Server databases, you must enable TDE separately.
Some restrictions and precautions may affect the deployment and use of TDE.