The code creates a database first with three data files named TestDatabase2_Primary, TestDatabase2_FG1 and TestDatabase2_FG2. The file group FG1_Default is set as the default file group and TestTable1 is created in it. The TestTable2 is created in the FG2_ReadOnly file group. Later, FG1_ReadOnly file group is marked as READONLY.
Finally, DEK is created in the TestDatabase2 and the Encryption property set as true. All statements are successfully executed. If you query sys.dm_database_encryption_keys, you will see that the encryption_state of the TestDatabase2 is 2 that says that encryption is being done but not completed. I am not sure the reason for not throwing an exception at the time of enabling TDE
Is FileStream data type used?
Databases that have filestream type used can be encrypted by using TDE but file stream data will not be encrypted.
What is the impact when TDE is enabled?
Enabling TDE on a database impacts few things;
Transaction log
Once TDE is enabled, SQL Server ensure that log file contains no clear text data by zeroing it out. SQL Server starts with new VLF with an encrypted form.
TEMPDB system database
This will be automatically encrypted once you enable TDE on any database. This may introduce slow performance on non-encrypted databases that use the tempdb database.
Log shipping and Database mirroring
If you enabled TDE on a database that ships logs to another database (means, log shipping enabled database), log shipping operation will fail at the secondary database, unless the certificate is available in the secondary server. I tested TDE on a log shipping enabled database, found that it fails once the TDE is enabled but starts works fine when the certificate used for TDE is available. BOL says that enabling TDE on a log shipping enabled database, encrypts both primary and secondary databases but when I checked, I found the encryption_state of the secondary database is 1 (unencrypted). I am not too sure about the reason, I will be exploring more on this anyway.
Compressed backup
I tested this by taking a compressed backup on a TDE enabled database, seems compression is not much effective on a TDE enabled database. This is how I tested;
— 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
The code creates a database and inserts some records to a table. Then the database is backed up twice, one without compression and another with compression. The you need to enable TDE on the database and execute the rest that is same code for backing up we used before enabling TDE. Backup files sizes are;
| Full backup before enabling TDE | 1,365 KB |
| Full backup with compression before enabling TDE | 124KB |
| Full backup after enabling TDE | 1,365 KB |
| Full backup with compression after enabling TDE | 1,278 KB |
You can see the difference. The result proves that compressing backup files for TDE enabled databases are not much effective.
This article contains what I tested on TDE. I may have missed something, you may have something that needs to be added to this. I highly appreciate if you can let me know, if you have anything new to be added to this. I would appreciate your comments on this, please send your comments to dinesh@sqlserveruniverse.com.
]]>