Transparent data encryption (TDE) helps you to secure your data at rest, this means the data files and related backups are encrypted, securing your data in case your media is stolen.
This technology works by implementing real-time I/O encryption and decryption, so this implementation is transparent for your applications and users.
However, this type of implementation could lead to some performance degradation since more resources must be allocated in order to perform the encrypt/decrypt operations.
On this post we will compare how much longer take some of the most common DB operations, so in case you are planning to implement it on your database, you can have an idea on what to expect from different operations.
Setup of our example
For this example we will use the Wide World Importers sample database, and restore it 2 times, one decrypted, and the other one encrypted. Please note that the restored database is around 3.2 GB size, a relatively small one.
To encrypt one of the databases we use the following T-SQL (more info about how TDE works here):
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Y0uR_P@$$worD_123';
GO
CREATE CERTIFICATE MyServerCert
WITH SUBJECT = 'WWI Encryption';
GO
USE [WideWorldImporters_encrypted];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE [WideWorldImporters_encrypted]
SET ENCRYPTION ON;
GO
After the above T-SQL has been executed, now we have the 2 databases ready for our example:
So it is just a matter of testing and measuring different operations and compare them (in no particular order):
DBCC CHECKDB
We run a dbcc checkdb on each database with the following T-SQL:
SET STATISTICS TIME ON;
DBCC CHECKDB([WideWorldImporters]) WITH NO_INFOMSGS
DBCC CHECKDB([WideWorldImporters_encrypted]) WITH NO_INFOMSGS
SET STATISTICS TIME OFF;
we can see the results, it took around 6% more to complete:
BACKUP DATABASE
Before we start this one, read this post about issues found on database backups with TDE enabled, so it is strongly recommended to patch your instances before using it.
SET STATISTICS TIME ON;
BACKUP DATABASE [WideWorldImporters] TO
DISK = N'D:DBAWorkWideWorldImporters-Full.bak'
WITH NOFORMAT, NOINIT, NAME = N'WideWorldImporters-Full',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 100
BACKUP DATABASE [WideWorldImporters_encrypted] TO
DISK = N'D:DBAWorkWideWorldImporters_encrypted-Full.bak'
WITH NOFORMAT, NOINIT, NAME = N'WideWorldImporters_encrypted-Full',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 100
SET STATISTICS TIME OFF;
Surprisingly, this one took less time with encryption enabled, around 15% less time... but wait! this is because of the number of pages processed by each backup operation, because if you check the average speed, you can see the encrypted backup operation is in fact, slower (around 33%), so in the case of this operation, it can vary for your environment:
Restore Database
If you plan to restore an encrypted database to a different server, remember that you must back up your master key and certificate and restore it on the new server in order to do it, otherwise you will not able to recover encrypted data.
Since now we are on the same instance, master key and certificate are already there, so we just run the simple RESTORE command:
SET STATISTICS TIME ON;
USE [master]
RESTORE DATABASE [WideWorldImporters] FROM
DISK = N'D:DBAWorkWideWorldImporters-Full.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 100
RESTORE DATABASE [WideWorldImporters_encrypted] FROM
DISK = N'D:DBAWorkWideWorldImporters_encrypted-Full.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 100
SET STATISTICS TIME OFF;
As with the backup operation, the restore took less time for the encrypted database, but you can see that is because of the number of pages processed, because if you check the average restore speed, for the encrypted database is slower (167% slower), so also check this one for your own databases:
Rebuild indexes
We execute Rebuild index statements for random tables with the following T-SQL:
SET STATISTICS TIME ON;
USE [WideWorldImporters]
GO
Print '--- Normal DB ---'
ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions]
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX [PK_Application_People] ON [Application].[People]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Print '--- END Normal DB ---'
USE [WideWorldImporters_encrypted]
GO
Print '--- Encrypted DB ---'
ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions]
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX [PK_Application_People] ON [Application].[People]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines]
REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Print '--- END Encrypted DB ---'
SET STATISTICS TIME OFF;
You can see the relevant execution results:
The encrypted database took around 24% more time (on average for all the operations) to complete.
Update Statistics
Now we execute update statistics with full scan on various tables with the following T-SQL:
SET STATISTICS TIME ON;
USE [WideWorldImporters]
GO
Print '--- Normal DB ---'
UPDATE STATISTICS [Warehouse].[StockItemTransactions]
WITH FULLSCAN
UPDATE STATISTICS [Sales].[OrderLines]
WITH FULLSCAN
UPDATE STATISTICS [Warehouse].[StockItems]
WITH FULLSCAN
UPDATE STATISTICS [Application].[People]
WITH FULLSCAN
UPDATE STATISTICS [Purchasing].[PurchaseOrderLines]
WITH FULLSCAN
Print '--- END Normal DB ---'
USE [WideWorldImporters_encrypted]
GO
Print '--- Encrypted DB ---'
UPDATE STATISTICS [Warehouse].[StockItemTransactions]
WITH FULLSCAN
UPDATE STATISTICS [Sales].[OrderLines]
WITH FULLSCAN
UPDATE STATISTICS [Warehouse].[StockItems]
WITH FULLSCAN
UPDATE STATISTICS [Application].[People]
WITH FULLSCAN
UPDATE STATISTICS [Purchasing].[PurchaseOrderLines]
WITH FULLSCAN
Print '--- END Encrypted DB ---'
SET STATISTICS TIME OFF;
And these are the results:
Even when some of the tables took less time, on average the encrypted database took around 15% more time to complete the statistics update. This should be other to have into account to check for your own database.
For this post we will not test any DML or SELECT statement, but you are free to do your own tests and determine if TDE implementation suits for you.
As we stated earlier, we have demonstrated that additional workload is put on TDE enabled databases in order to support the encrypt/decrypt operations. Now is is your turn to perform your own validations to see how much is impacted your own databases with this, since this can vary on different storage types (for example on fast SSD storages, it could be barely noticed).