While a re-encryption scan for a database encryption operation is in progress, maintenance operations to the database are disabled. You can use the single user mode setting for the database to perform the maintenance operation. For more information, see How to: Set a Database to Single-user Mode (SQL Server Management Studio).
You can find the state of the database encryption using the sys.dm_database_encryption_keys dynamic management view. For more information, see the "Catalog Views and Dynamic Management Views" section earlier in this topic).
In TDE, all files and filegroups in the database are encrypted. If any filegroups in a database are marked READ ONLY, the database encryption operation will fail.
If a database is being used in database mirroring or log shipping, both databases will be encrypted. The log transactions will be encrypted when sent between them.
.gif) Important: |
|---|
|
Any new full-text indexes will be encrypted when a database is set for encryption. Previously-created full-text indexes will be imported during upgrade and they will be in TDE after the data is loaded into SQL Server. Enabling a full-text index on a column can cause that column's data to be written in plain text onto the disk during a full-text indexing scan. We recommend that you do not create a full-text index on sensitive encrypted data.
|
Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.
Restrictions
The following operations are not allowed during initial database encryption, key change, or database decryption:
-
Dropping a file from a filegroup in the database
-
Dropping the database
-
Taking the database offline
-
Detaching a database
-
Transitioning a database or filegroup into a READ ONLY state
The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
-
Dropping a file from a filegroup in the database.
-
Dropping the database.
-
Taking the database offline.
-
Detaching a database.
-
Transitioning a database or filegroup into a READ ONLY state.
-
Using an ALTER DATABASE command.
-
Starting a database or database file backup.
-
Starting a database or database file restore.
-
Creating a snapshot.
The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
-
The database is read-only or has any read-only file groups.
-
An ALTER DATABASE command is executing.
-
Any data backup is running.
-
The database is in an offline or restore condition.
-
A snapshot is in progress.
-
Database maintenance tasks.
Transparent Data Encryption and Transaction Logs
Enabling a database to use TDE has the effect of "zeroing out" the remaining part of the virtual transaction log to force the next virtual transaction log. This guarantees that no clear text is left in the transaction logs after the database is set for encryption. You can find the status of the log file encryption by viewing the encryption_state column in the sys.dm_database_encryption_keys view, as in this example:
USE AdventureWorks;
GO
/* The value 3 represents an encrypted state
on the database and transaction logs. */
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO
For more information about the SQL Server log file architecture, see Transaction Log Physical Architecture.
All data written to the transaction log before a change in the database encryption key will be encrypted by using the previous database encryption key.
After a database encryption key has been modified twice, a log backup must be performed before the database encryption key can be modified again.
Transparent Data Encryption and the tempdb System Database
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server. For more information about the tempdb system database, see tempdb Database.
Transparent Data Encryption and Replication
Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files. During transactional or merge replication, encryption can be enabled to protect the communication channel. For more information, see How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).
Transparent Data Encryption and FILESTREAM DATA
FILESTREAM data is not encrypted even when TDE is enabled.