Search This Blog

SQL Server 2014 CTP 2 : Native Backup Encryption

Here we will discuss the native backup encryption new feature introduced in SQL Server 2014 CTP2. The native backup encryption feature will create an encrypted backup file for SQL Server database. This is a feature that third party vendors have provided in the past, but we now have the feature for encrypted backups integrated with the Product.
How it works


A certificate or asymmetric key is used with encryption algorithm to achieve the goal. We have multiple options of algorithm with this feature. The supported algorithms are AES 128, AES 192, AES 256, and Triple DES.

Native backup encryption in action

Step 1: We can accomplish this by using certificate or asymmetric key so we will use certificate here.


Step 2: Create a certificate in master database.


Step 3:  We are all set to create an encrypted backup file by specifying the certificate that we create in step 2 and a backup algorithm. You can read more about the algorithm supported and choose the one which is best for you.


You will receive a warning as shown above  if you do not back up the certificate. It is very important that you back up the certificate to a safer location. So let’s Backup the certificate.

This is it — you have your encrypted backup with you now.
Native backup encryption using GUI
Step 1: We have two options to use as destination for backup of which you can choose to back up to a URL or to disk. If you choose to back up to a URL you can supply URL of your storage, Credentials and azure storage container. Below is the screenshot for the backup to URL option.I will be choosing disk option for now.


Step 2: We will move to Backup Options Page and can see that the Encryption option is greyed out here as I selected the DISK destination for backups. This could change in the final release of SQL Server 2014. But we were able to encrypt the backup created to disk using T-SQL in previous section of this article.
Went back and selected the URL options and now can see the options for encrypting backups. We can choose one of four encryption algorithm as of now.

Benefits of native backup encryption
  • This is going to benefit organizations which are using TDE just for the sake of encrypting data in their backup files, which actually consumes a lot of resources on production database. Also TDE is available only in Enterprise Edition but Native backup encryption is available for all editions, except SQL Server Express and SQL Server Web.
  • If you are currently using a 3rd party tool for encrypted backups, you can compare that tool to the functionality and performance of native encrypted backups and see if this fulfills your requirement.

Limitations
  • Appending to an existing backup set option is not supported for encrypted backups.
  • SQL Server Express and SQL Server Web do not support encryption during backup.

Conclusion
This is one of the most awaited feature which is integrated with product with no extra cost or additional installation.
I will compare the impact of different algorithms on backup performance and disk space utilization in an upcoming blog post.