A new feature in SQL Server 2014 that many of you hadn't heard about until it was announced this week at the PASS Summit is native backup encryption in Standard, Business Intelligence and Enterprise Editions (sorry, Web and Express are not supported). This is something that 3rd party vendors have been offering for a long time, and it finally makes its way into the product, supporting four encryption algorithms: AES 128, AES 192, AES 256, and Triple DES (3DES).
If you are currently using Transparent Data Encryption solely for the purpose of having encrypted data in your backup files, this could be a way to migrate from that technique and have encrypted backups without the hit that TDE places on your live system. If you are currently using a 3rd party tool for encrypted backups, you should compare that to the functionality and performance of native encrypted backups.
P.S. You can download CTP2 right now.
I didn't want to get into comparing to 3rd party products – I'm sure they all do a fine job, and probably have additional features that I haven't even thought about. I just wanted to test what kind of hit the different algorithms would take on full backups, from and to both traditional spinny disks (RAID 1) and solid state drives, and with and without native compression.
So, I downloaded the AdventureWorks2012 data file, made two copies, named them awSSD.mdf and awHDD.mdf, and placed one on the RAID 1 drive (D:\) and one on the SSD drive (E:\). Then I attached both with FOR ATTACH_REBUILD_LOG, set them to FULL recovery, changed the default auto-growth settings, and set the default location for log files in between (as this is the only way I know of to specify the location of the rebuilt log file):
USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog', REG_SZ, N'D:\CTP2_Data';
GO
CREATE DATABASE awHDD ON (filename='D:\CTP2_Data\awHDD.mdf') FOR ATTACH_REBUILD_LOG;
ALTER DATABASE awHDD SET RECOVERY FULL;
ALTER DATABASE awHDD MODIFY FILE (NAME = N'AdventureWorks2012_Data', FILEGROWTH = 512000KB);
ALTER DATABASE awHDD MODIFY FILE (NAME = N'AdventureWorks2012_Log', FILEGROWTH = 512000KB);
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog', REG_SZ, N'E:\CTP2_Data';
GO
CREATE DATABASE awSSD ON (filename='E:\CTP2_Data\awSSD.mdf') FOR ATTACH_REBUILD_LOG;
ALTER DATABASE awSSD SET RECOVERY FULL;
ALTER DATABASE awSSD MODIFY FILE (NAME = N'AdventureWorks2012_Data', FILEGROWTH = 512000KB);
ALTER DATABASE awSSD MODIFY FILE (NAME = N'AdventureWorks2012_Log', FILEGROWTH = 512000KB);
Next, I enlarged them using this script from Jonathan Kehayias (so that both the database and the log would be large enough to be meaningful). This took about 4 minutes per database on both HDD and SSD.
At that point, EXEC sp_helpfile; yielded the following for each database:
name fileid filename size ----------------------- ------ -------- ---------- AdventureWorks2012_Data 1 <db>.mdf 1553408 KB AdventureWorks2012_Log 2 <db>.ldf 5605504 KB
Now, a couple of things about this feature before we can actually start performing encrypted backups. You need to have a certificate (or asymmetric key) to use encryption, and this in turn will require a master key. I chose a certificate, and created these as follows:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd';
GO
CREATE CERTIFICATE TestCert WITH SUBJECT = 'EncryptionTesting';
GO
You also get a warning if you attempt to create an encrypted backup using a certificate that has not, itself, been backed up:
In my case I could just back up the certificate and the master key, like this:
BACKUP CERTIFICATE TestCert
TO FILE = 'C:\temp\TestCert.cert'
WITH PRIVATE KEY
(
FILE = 'C:\temp\TestCert.key',
ENCRYPTION BY PASSWORD = 'p@ssw0rd'
);
BACKUP MASTER KEY TO FILE = 'C:\temp\MasterKey.key'
ENCRYPTION BY PASSWORD = 'p@ssw0rd';
Strictly speaking, backing up the master key is not necessary to perform an encrypted backup (or even to avoid the warning), but you should back this up anyway. And you will probably want to use a stronger password than p@ssw0rd, store it somewhere other than the C: drive on the same machine, etc. Finally, you should take heed that if you encrypt your backups and you don't take all the right precautions, they may be useless in the event of a disaster. This is not a feature you should just turn on without a fair amount of diligence and testing.
With all of that out of the way, I could get on with testing. This system only has a single platter-based disk and a single SSD, so I couldn't test SSD -> different SSD or HDD -> different HDD; only backing up from one to the other, or to the same drive. The basic syntax for backing up with encryption is:
BACKUP DATABASE ... WITH ENCRYPTION
(ALGORITHM = , SERVER CERTIFICATE = );
And the four possible values for <algorithm> are AES_128, AES_192, AES_256 and TRIPLE_DES_3KEY.
So, next, I generated the script to run the backups to compare the performance and size of various combinations – the four different encryption algorithms (and no encryption), with and without compression, where the data comes from (HDD or SSD) and where the data is backed up to (HDD or SSD). That's 40 different backups, and the script I used to generate it looks like this:
DECLARE @sql NVARCHAR(MAX) = N'';
;WITH s(s) AS (SELECT 1 UNION ALL SELECT 2),
m AS (SELECT m FROM (VALUES('AES_128'),('AES_192'),('AES_256'),('TRIPLE_DES_3KEY'),(NULL)) AS m(m)),
c AS (SELECT c FROM (VALUES('NO_COMPRESSION'),('COMPRESSION')) AS c(c)),
d AS (SELECT d,t FROM (VALUES('D','HDD'),('E','SSD')) AS d(d,t))
SELECT @sql += N'
BACKUP DATABASE aw' + CASE s WHEN 1 THEN 'HDD' ELSE 'SSD' END
+ ' TO DISK =
''' + d + ':\backup\' + n + '.bak''
WITH INIT, ' + c + ',' + COALESCE('
ENCRYPTION (ALGORITHM = ' + m + ', SERVER CERTIFICATE = TestCert),', '') + '
NAME = ''' + n + ''';' FROM
(
SELECT *, n = 'test' + CONVERT(VARCHAR(8000), RIGHT('0' + RTRIM(r),2)) + '-' +
COALESCE(m,'NO_ENCRYPTION') + '-' + CASE WHEN r < 11 THEN 'HDD' ELSE 'SSD' END
+ '-to-' + t + '-' + c FROM
(
SELECT *, r = ROW_NUMBER() OVER (PARTITION BY d.d ORDER BY s.s,m.m,c.c)
FROM s CROSS JOIN m CROSS JOIN c CROSS JOIN d
) AS x
) AS y ORDER BY r;
--EXEC sp_executesql @sql;
--GO 10
SELECT CONVERT(XML, @sql);
Looks really complicated, but really it's just generating 40 BACKUP DATABASE strings. I select as XML so that, when you click on the results in the grid, you can view the whole output - instead of what PRINT or selecting the output to grid/text will limit you to. The output in this case is below (click to show/hide):
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test01-NO_ENCRYPTION-HDD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, NAME = 'test01-NO_ENCRYPTION-HDD-to-HDD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test01-NO_ENCRYPTION-HDD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, NAME = 'test01-NO_ENCRYPTION-HDD-to-SSD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test02-NO_ENCRYPTION-HDD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, NAME = 'test02-NO_ENCRYPTION-HDD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test02-NO_ENCRYPTION-HDD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, NAME = 'test02-NO_ENCRYPTION-HDD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test03-AES_128-HDD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test03-AES_128-HDD-to-HDD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test03-AES_128-HDD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test03-AES_128-HDD-to-SSD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test04-AES_128-HDD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test04-AES_128-HDD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test04-AES_128-HDD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test04-AES_128-HDD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test05-AES_192-HDD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test05-AES_192-HDD-to-HDD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test05-AES_192-HDD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test05-AES_192-HDD-to-SSD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test06-AES_192-HDD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test06-AES_192-HDD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test06-AES_192-HDD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test06-AES_192-HDD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test07-AES_256-HDD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test07-AES_256-HDD-to-HDD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test07-AES_256-HDD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test07-AES_256-HDD-to-SSD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test08-AES_256-HDD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test08-AES_256-HDD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test08-AES_256-HDD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test08-AES_256-HDD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test09-TRIPLE_DES_3KEY-HDD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test09-TRIPLE_DES_3KEY-HDD-to-HDD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test09-TRIPLE_DES_3KEY-HDD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test09-TRIPLE_DES_3KEY-HDD-to-SSD-COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'E:\backup\test10-TRIPLE_DES_3KEY-HDD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test10-TRIPLE_DES_3KEY-HDD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awHDD TO DISK = 'D:\backup\test10-TRIPLE_DES_3KEY-HDD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test10-TRIPLE_DES_3KEY-HDD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test11-NO_ENCRYPTION-SSD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, NAME = 'test11-NO_ENCRYPTION-SSD-to-HDD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test11-NO_ENCRYPTION-SSD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, NAME = 'test11-NO_ENCRYPTION-SSD-to-SSD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test12-NO_ENCRYPTION-SSD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, NAME = 'test12-NO_ENCRYPTION-SSD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test12-NO_ENCRYPTION-SSD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, NAME = 'test12-NO_ENCRYPTION-SSD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test13-AES_128-SSD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test13-AES_128-SSD-to-HDD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test13-AES_128-SSD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test13-AES_128-SSD-to-SSD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test14-AES_128-SSD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test14-AES_128-SSD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test14-AES_128-SSD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_128, SERVER CERTIFICATE = TestCert),
NAME = 'test14-AES_128-SSD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test15-AES_192-SSD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test15-AES_192-SSD-to-HDD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test15-AES_192-SSD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test15-AES_192-SSD-to-SSD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test16-AES_192-SSD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test16-AES_192-SSD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test16-AES_192-SSD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_192, SERVER CERTIFICATE = TestCert),
NAME = 'test16-AES_192-SSD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test17-AES_256-SSD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test17-AES_256-SSD-to-HDD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test17-AES_256-SSD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test17-AES_256-SSD-to-SSD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test18-AES_256-SSD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test18-AES_256-SSD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test18-AES_256-SSD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = TestCert),
NAME = 'test18-AES_256-SSD-to-HDD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test19-TRIPLE_DES_3KEY-SSD-to-HDD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test19-TRIPLE_DES_3KEY-SSD-to-HDD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test19-TRIPLE_DES_3KEY-SSD-to-SSD-COMPRESSION.bak'
WITH INIT, COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test19-TRIPLE_DES_3KEY-SSD-to-SSD-COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'E:\backup\test20-TRIPLE_DES_3KEY-SSD-to-SSD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test20-TRIPLE_DES_3KEY-SSD-to-SSD-NO_COMPRESSION';
BACKUP DATABASE awSSD TO DISK = 'D:\backup\test20-TRIPLE_DES_3KEY-SSD-to-HDD-NO_COMPRESSION.bak'
WITH INIT, NO_COMPRESSION, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestCert),
NAME = 'test20-TRIPLE_DES_3KEY-SSD-to-HDD-NO_COMPRESSION';
I didn't need to do anything special to time these, because I could pull all of the relevant statistics from the msdb database after they were done (the only downside is that the duration is only measured to the granularity of seconds, unless I wanted to parse the output manually). So I uncommented the EXEC sp_executesql and GO lines (I wanted to run each backup 10 times to get averages, rule out anomalies, etc.), hit F5, and went to work on one of my sessions for the PASS Summit.
When I came back, I checked out the msdb tables to get the sizes / durations for each backup. This query is quite simple:
;WITH x AS
(
SELECT
name,
natural_size = backup_size/1024/1024.0,
compressed_size = compressed_backup_size/1024/1024.0,
duration = 1.0*DATEDIFF(SECOND, backup_start_date, backup_finish_date)
FROM msdb.dbo.backupset
WHERE name LIKE 'test%'
)
SELECT
name,
[natural_size] = MAX(natural_size),
[compressed_size] = MAX(compressed_size),
[min_duration] = MIN(duration),
[max_duration] = MAX(duration),
[avg_duration] = AVG(duration)
FROM x
GROUP BY name
ORDER BY name;
This would give me the data I needed to make some pretty charts.
Impact on Size
Depending on your experience with encryption in general, it may or may not surprise you that encrypting a database backup has very little impact on its overall size. How this works is beyond the scope of this post, for certain; a simple explanation would be that - at least with AES encryption - compression is not very effective on most of the output because it is basically random gibberish.
The end result is that this chart is not very exciting. The compressed and non-compressed sizes of native backups against the four different encryption methods:
Size, in MB, of backups with and without encryption
As you can see, there was almost zero impact on the size of the database - around 0.03% increased size for a non-compressed backup, and an additional 0.04% for a compressed backup.
Impact on Performance
While encryption had a negligible impact on size, it did affect the speed of the backup. But in some cases, not in the way you'd think. Here is the overall measure of average runtimes for each approach:
Average duration, in seconds, of various backups
I truly expected the encryption to always cause a performance hit, and you should test in your environment to see if your results are different from mine. I'm going to come back and update this with a new chart showing particular cases that were surprising to me, and remove some outlier values to make sure the results are truly representative.
A Caveat
One important note: you can't append encrypted backups. If you generate an encrypted backup file using WITH INIT, and then try to append another encrypted backup to the same file, you will receive this error:
The backup cannot be performed because 'ENCRYPTION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'ENCRYPTION' or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.
Msg 3013, Level 16, State 1, Line 11
BACKUP DATABASE is terminating abnormally.
You can, confusingly, append a non-encrypted backup when the initial file was encrypted. This is not the intention, and is a bug I've reported on Connect (#805220, but it is currently marked as private); hopefully they will address this before RTM.
In the meantime, you have to be careful here because nothing has been changed about the RESTORE HEADERONLY output to indicate whether any of the enclosed backups were encrypted. To discover this, you'll need to check the BackupSetGUID value in that output at Position = 1, and find the corresponding backup_set_uuid value in msdb.dbo.backupset. This table has new columns to support encryption, where you can get this information: key_algorithm, encryptor_thumbprint, and encryptor_type. This is problematic in cases where you don't have the backupset data - maybe it's been cleared out during maintenance tasks, or maybe you can't access it because you really are recovering from a disaster or only have the .bak file (or only have the .bak file for other reasons). In this case I'm hoping there is some other way to tell from the backup file that it has been encrypted (and how), but at the time of writing I don't know of a way. I filed a suggestion (#805292, also private) that the output of RESTORE HEADERONLY be augmented with encryption information the same way it was augmented with compression information when that feature was added in SQL Server 2008.
When they do resolve these issues (and I am confident they will), I'll remove all of this noise, but it is important to be aware of this in the meantime, if you are going to perform any testing with current CTPs.
Next Up...
What this type of backup means for restoring, I'll circle back around to in another post, when I test restore speeds and reveal any trouble areas there. I also want to revisit these tests to investigate encrypted log backups.
If encrypted backups are faster than unencrypted ones there is a performance bug in non-encrypted backups.
You may be right. I haven't investigated any further. Keep in mind that in the final chart I was just taking averages; they weren't faster in *all* cases (look closely at the second chart). Also important to note that this is only one test result for one database on one system.
Great post thank you. Do you have any observations on CPU utilization along with the backup measures?
Hi Ken, sorry I did not keep an eye on CPU utilization, I will be sure to monitor that on my next set of tests.
Nice article Aaron, very excited about this coming in 2014. Currently using a 3rd party tool for backup encryption but this here makes it a nice feature. Also facing some TDE as well.
Thanks for the article! I'm excited about SQL Server finally having both backup encryption and compression for native file types. One question on the diagram "Average Backup Duration". The "SSD to HDD" outperformed the "SSD to SSD" in every run. Do you know of any factor that would cause this result?
Hi GP, I think that the factors involved are the same that also make HDD to SSD consistently outperform SSD to SSD in almost every run – just a different I/O path when backing up from one to the other than when backing up to the same drive. Even on an SSD the disk can only do so many things at once.