Discover what new features are in SQL Server 2014 (4)-native backup encryption _mssql

SQL Server 2014 CTP2 publishes a feature for backup, which is native backup encryption. In view of the previously bad impact on the network database leakage events, is essentially a database backup leaked to the third party, SQL Server's original data backup can make even if the backup itself is stolen, without the encryption certificate can not be used, this effectively resolves the above data leakage problem. The original solution

Before SQL Server 2014, if you want to implement encryption of the backup, it is implemented in one of the following two ways:

Encrypt the entire database with transparent data encryption (TDE), allowing backup and transaction logs to be encrypted using a Third-party backup encryption tool

First, let's talk about using transparent data encryption, if only to encrypt the backup, using TDE a bit too much, because using TDE can cause the database itself and the log is encrypted, the CPU overhead, in addition, the use of TDE encrypted database if the use of backup compression, compression ratio will be very low. If you use SQL Server 2014 's native backup encryption, you do not need to encrypt the entire database, and the backup is encrypted only before it is written to disk. Original sound backup encryption if combined with backup compression, then compression, and then encryption, resulting in a very high compression ratio.

Second, the use of Third-party Backup encryption tool requires additional costs, and in the implementation process will also have a relatively cumbersome operating costs, the use of Third-party backup when it is necessary to encrypt backup to Azure, it will be very cumbersome, and the use of raw backup encryption, you can directly backup to Azure backup to encrypt, This provides additional security for off-site backups.

Encrypting using native data

This feature is included in SQL Server 2014 Enterprise, Bi, and Standard editions. There are two prerequisites to using Backup encryption:

Existing certificate or asymmetric key selection backup encryption algorithm

The following uses the acoustic data encryption to encrypt the backup, first establishing the certificate:

CREATE Certificate Backupcertificate

with SUBJECT = ' Backup encryption certificate ';

Go

After the build is complete, use the certificate for backup encryption directly:

BACKUP DATABASE testbk to DISK = ' C:\Test_Encrypted.bak ' with COMPRESSION, encryption (algorithm = aes_256, SERVER certifica TE = backupcertificate);

 note that SQL Server gives the hint to back up the certificate and the certificate's private key, otherwise the data will not be available. Usually in best practice, when a certificate is created, it should be backed up, and data security will always be the first.

note that the encryption algorithm specified by the backup is the aes_256 algorithm. SQL Server 2014 provides 4 algorithms for backup encryption, namely the AES128, AES196, AES256, and Triple DES algorithms.

The Management Studio in SQL Server 2014 also provides GUI support for acoustic backup encryption.

The effect of primary encrypted backup on compression

Previously mentioned, the use of raw backup encryption can make backup compression is almost not affected by encryption, the following backup a test library around 2.5G, specify a compressed backup, the first example using only compressed backup, 第2-4 example using compressed backup + different compression algorithm

 using backup encryption has almost no effect on the size of the database backup.

regardless of the encryption algorithm used, encryption has almost no effect on the compression ratio.

Performance impact of native encrypted backups

Using native backup encryption is a great CPU-consuming operation, and the following is a further addition to the uncompressed backup based on Figure 3.

As can be seen from fig. 6, the AES algorithm does not have much effect on the backup time without the CPU being the bottleneck of the backup machine, except that the 3DES algorithm consumes longer time. However, the impact of encryption on the specific environment is due to the environment, the test results in different environments may vary widely, and if you are concerned about the performance impact of the original encrypted backup, first test in the specific environment.

Summary

SQL Server Soundtrack backup encryption provides a very good solution for data security. Using native backup encryption basically does not increase the size of the backup file, and breaks the dilemma of having little compression after using transparent data encryption. Using native backup encryption can provide additional security for data at very low cost, whether you are backing up data to a remote data center or backing up your data to the cloud.