The encrypted operation of data stored in the database instance of Microsoft SQL Server via TDE is supported by RDS of JD. TDE will automatically encrypt such data before writing them into a memory and automatically decrypt such data when reading them from the memory. The database file is encrypted in the page level. The page in the encrypted database will be encrypted before being wrote into a disk, but will be decrypted when being read into a memory. TDE will not increase the size of an encrypted database.
For detailed introduction of transparent data encryption (TDE), please refer to the Microsoft document TDE
RDS of JD Cloud supports the use of TDE in the following SQL Server version:
- SQL Server 2008R2 Enterprise Version
- SQL Server 2012 Enterprise Version
- SQL Server 2014 Enterprise Version
- SQL Server 2016 Enterprise Version
- SQL Server 2017 Enterprise Version
- It is not allowed to disable TDE when it is enabled at the instance level
- The user can enable (or disable) the database to be encrypted via SQL after enabling TDE
- When TDE is enabled, the CPU load will be significantly increased. It is suggested to enable TDE after test. The TDE performance comparison, TDE Performance Comparision, made by a third party is listed below for reference
- If at least encrypted database is possessed by the database instance, the un-encrypted database performance may be reduced. Hence, you are suggested to keep the encrypted database and the un-encrypted database on different database instances.
- The encrypted database backup cannot be restored to other instances via the "cross-instance recovery" function
- After being downloaded, the encrypted database backup can be restored to the local database only after being decrypted. Please obtain the decryption key pair by contacting the Customer Service. The self-help download function will be provided later.
- The cross-origin backup synchronization is not supported by the encrypted database
1. Enable TDE encryption
- Log in the RDS console and click the instance to enter the "Security Management" page.
- Select "TDE Encryption" and click Enable to enable the instance-level TDE.
- Execute the following SQL for the database with TDE to be enabled. Taking the database db1 for example
SELECT name FROM sys.certificates WHERE name LIKE 'TDE%'
-- TDECertificateName refers to the TDE certificate name searched in the last step.
CREATE DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE [TDECertificateName]
ALTER DATABASE db1
SET ENCRYPTION ON
-- TDE Verification Enabled
SELECT name FROM sys.databases WHERE is_encrypted = 1
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
2. Download TDE certificate and get Key
After TDE is enabled in the Database, the backup downloaded to the local cannot be restored directly, and it only can be restored in the local SQL Server Instance in combination with the TDE certificate and Key
- The certificate of TDE can be downloaded after TDE is enabled in the Console. The certificate can be downloaded from the Intranet or public network.
- Click "Click Replication" to get Key
- Execute the following SQL for the backup to be restored
create master key encryption by password = N'xxx'; --xxx is the Key obtained previously
CREATE CERTIFICATE Mycertificate
FROM FILE = N'D:\Database\mycertificate.cer'
with private key
file = N'D:\Database\mycertificate.pvk' ,
decryption by password = N'xxx'
); -- mycertificate.cer and mycertificate.pvk are TDE certificates previously downloaded, and the specific file names are slightly different
- Then, execute the restored SQL statement or restore via the SSMS client