New features of native backup encryption for SQL Server 2014

Source: Internet
Author: User
Tags new features management studio backup

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) so that the backup and transaction logs are also 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:

Certificate or asymmetric key exists

Select Backup Encryption algorithm

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

Createcertificate backupcertificate
    
withsubject = ' Backup encryption certificate ';
    
Go

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

BACKUP databasetestbk todisk = ' C:\Test_Encrypted.bak ' withcompression, encryption  (algorithm = aes_256, SERVER certificate = backupcertificate);

As shown in Figure 1, in Figure 1, 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.

Figure 1. Prompt to back up certificate private key

In Figure 1, 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, as shown in Figure 2.

Figure 2. GUI support for native 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, the test statement as shown in Figure 3.

Figure 3. Test statement

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.