Unlike TDE, there is some extra CPU overhead when you take an encrypted backup as the data has to be encrypted before being written to disk – whereas with TDE the data is already encrypted. Backup times however are unlikely to be affected significantly as the bottleneck is usually going to be the time it takes to physically write the data to disk. The CPU processing should take a fraction of that time.

We can run a quick test with our database to show how backup performs with and without encryption. If you’re running this test yourself having followed the examples in Backup Encryption in SQL Server, then make sure the previous backup files are removed before executing the below script.

BACKUP DATABASE TestBackupEncryption
TO DISK = 'C:\Test\TestBackupEncryption_Unencrypted.bak';

BACKUP DATABASE TestBackupEncryption
TO DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak'
WITH ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptionCert);

Here is the output for the unencrypted backup:

Processed 1205416 pages for database 'TestBackupEncryption', file 'TestBackupEncryption' on file 1.
Processed 1 pages for database 'TestBackupEncryption', file 'TestBackupEncryption_log' on file 1.
BACKUP DATABASE successfully processed 1205417 pages in 17.428 seconds (540.355 MB/sec).

And here is the output for the encrypted backup:

Processed 1205416 pages for database 'TestBackupEncryption', file 'TestBackupEncryption' on file 1.
Processed 1 pages for database 'TestBackupEncryption', file 'TestBackupEncryption_log' on file 1.
BACKUP DATABASE successfully processed 1205417 pages in 19.631 seconds (479.716 MB/sec).

You can see the backup with encryption did take a bit longer, about 2 seconds, a little over a 10% increase which seems not too bad.

With backup performance, it’s also worth considering how long it takes to restore a database from a backup. Let’s take a quick look at that. We’ll drop the database, restore the unencrypted backup, then drop it again, and restore from the encrypted backup. Then we can compare the performance. We do all that with the following code:

DROP DATABASE TestBackupEncryption;
GO

RESTORE DATABASE TestBackupEncryption
FROM DISK = 'C:\Test\TestBackupEncryption_Unencrypted.bak';

DROP DATABASE TestBackupEncryption;
GO

RESTORE DATABASE TestBackupEncryption
FROM DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak';

Here is the output for restoring from the unencrypted backup:

Processed 1205416 pages for database 'TestBackupEncryption', file 'TestBackupEncryption' on file 1.
Processed 1 pages for database 'TestBackupEncryption', file 'TestBackupEncryption_log' on file 1.
RESTORE DATABASE successfully processed 1205417 pages in 17.979 seconds (523.795 MB/sec).

And here is the output with the encrypted backup:

Processed 1205416 pages for database 'TestBackupEncryption', file 'TestBackupEncryption' on file 1.
Processed 1 pages for database 'TestBackupEncryption', file 'TestBackupEncryption_log' on file 1.
RESTORE DATABASE successfully processed 1205417 pages in 20.794 seconds (452.886 MB/sec).

You can see that like the backup itself, the restore from the encrypted backup took slightly longer – in this case about 3 seconds or 15%. It’s not too bad, but it’s good to be aware that you may see some impact.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.