In SQL 2014 there is a new feature in database backups - an encrypted backup.
To encrypt backups you need to create a database master key for the MASTER database and either a certificate (as in example below) or an asymmetric key to use for the backup.
Here is a script to backup the IndexDemo database with an encryption.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1qaz!QAZ';
GO
IF EXISTS (select * from sys.certificates where name = 'TestBackupCert') DROP CERTIFICATE TestBackupCert;
CREATE CERTIFICATE TestBackupCert WITH SUBJECT = 'Backup Encryption Certificate';
GO
BACKUP DATABASE [IndexDemo] TO DISK = 'IndexDemoDB.bak'
WITH FORMAT, STATS=5
, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestBackupCert);
-- AES_128, AES_192, AES_256, TRIPLE_DES_3KEY
GO
To encrypt backups you need to create a database master key for the MASTER database and either a certificate (as in example below) or an asymmetric key to use for the backup.
Here is a script to backup the IndexDemo database with an encryption.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1qaz!QAZ';
GO
IF EXISTS (select * from sys.certificates where name = 'TestBackupCert') DROP CERTIFICATE TestBackupCert;
CREATE CERTIFICATE TestBackupCert WITH SUBJECT = 'Backup Encryption Certificate';
GO
BACKUP DATABASE [IndexDemo] TO DISK = 'IndexDemoDB.bak'
WITH FORMAT, STATS=5
, ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestBackupCert);
-- AES_128, AES_192, AES_256, TRIPLE_DES_3KEY
GO
I was wondering how much overhead the encryption algorithms would add and decided to test it on my IndexDemo database which was hosted on a Azure VM (A3 - 4 Cores, 7 GB RAM) with SQL 2014 Enterprise Edition (RTM) and the database files were stored on a disk attached to the VM.
The DB size was 1.75 GB.
For each of the encryption algorithms I ran the backup statement 3 times and averaged the results (the smaller the better).
As you can see backup encryption adds 40-70% to the backup time when AES algorithms are used.
And it's clear that Triple DES is not only an outdated algorithm but also the slowest one.
Comments
Post a Comment