SQL 2014 - Backup Encryption

SQL Server backup encryption is introduced in SQL Server 2014. But why need of backup encryption when you have TDE feature since SQL2008?

TDE is an encyption technique supported only on Enterpise Edition but where as SQL 2014 backup encryption is supported in Standard, Business Intelligence and Enterprise editions. Also with TDE, it implies some CPU overhead and the backup file compression ratio is then very low. So if an organizations using TDE just for sake of encrypting backup files or using any other 3rd party tool, then this backup encryption can be an alternative solution.
The supported SQL Server 2014 backup encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES.


/*****Create Database Master Key on Master DB*****/
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxxx'
--Create an encryption certificate
Use Master
GO
CREATE CERTIFICATE SQLbackupEncrptCert
WITH SUBJECT = 'SQL Backup Encryption Certificate';

/*****Backup Service Master Key:*****/
USE master
GO
BACKUP SERVICE MASTER KEY
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLTest_SMK.key'
ENCRYPTION BY PASSWORD = 'xxxxxxxxx';
GO

/*****Backup Database Master Key*****/
BACKUP MASTER KEY
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLTest_DMK.key'
ENCRYPTION BY PASSWORD = 'xxxxxxxxx';
GO

/*****Backup Certificate*****/
Use Master
GO
BACKUP CERTIFICATE SQLbackupEncrptCert
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLbackupEncrptCert.cert'
WITH PRIVATE KEY
(
FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLbackupEncrptCert_PK.key',
ENCRYPTION BY PASSWORD = 'xxxxxxxxx'
);
GO

/*****Backup database with encryption*****/
BACKUP DATABASE TestDb
TO DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\TestDB_SQL2014_Encrypt.bak'
WITH COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = SQLbackupEncrptCert
)

Limitations:
Appending to an existing backup set option is not supported for encrypted backups.
SQL Server Express and SQL Server Web do not support backup encryption but an encrypted backup can be restored.

SQL 2014/2016 Features

Features	SQL2014	SQL2016	Feature Description
Always Encrypted **		#	SQL Server 2016 new features are upgraded with Always Encrypted feature, which enables to encrypt database. This lets client application owners to manage which application should get the access the confidential data. This is done by letting the client application have the encryption key which is never passed to server.
Dynamic Data Masking **		#	This element has been added to customize the security of data according to the viewers. Dynamic Data Masking allows users to mask the confidential columns in a table for users who are not authorized for viewing.
Row Level Security **		#	Row level security, it provides a mechanism to control row level read and write access based on the user's context data like identity, role/group membership, session/connection specific information. Restricting rows will be done by filter predicates defined in inline table value function.
Stretch Database **		#	The Stretch Database feature provides a method to stretch the storage of On-Premise database to Azure SQL Database. This feature will make it easy to archive information to a cheaper storage media without having to change any actual application code and thereby maximizing performance.
JSON Support **		#	JSON stands for Java Script Object Notation. Parsing & storing of JSON as relational data & exporting relational data to JSON, as it is becoming a popular format to store NoSQL/Unstructured data.
PolyBase **		#	PolyBase is another significant addition which allows querying distributed data sets. This will allow you to use T-SQL statements to query Hadoop or SQL Azure blob storage.
R Comes to SQL Server **		#	With Microsoft's purchase of Revolution Analytics, now able to incorporate R to support advance analytics against big data right inside of SQL Server. By incorporating R processing into SQL Server, data scientists will be able to take the existing R code and run it right inside the SQL Server database engine.
Temporal Table **		#	Temporal table is defined as a table holding old versions of rows within a base table. If the database is having temporal tables, SQL Server can automatically manage shifting old row versions to the temporal table whenever it is updated. Users who are planning to manage row versioning might find this modification quite helpful.
Managing Locks in Online Index Rebuilds *	#	#	With SQL 2014 and later version, have an option to specify how long an index rebuild should wait for the locks required. This option also allows us to selectively terminate blocking processes caused by online index rebuilds.
Buffer Pool Extension *	#	#	In SQL Server 2014, use a fast SSD drive as a buffer pool extension (BPE). This extension is used only when main server memory starts to run out, and provides faster-than-spinning-disk-I/O access to data that's already been read from disk. This provides improved random I/O throughput, reduced latency, and a new caching architecture that enables us to take advantage of low-cost SSD drives to expand available cache, instead of just adding RAM.
In Memory OLTP *	#	#	This engine provides a lock and latch free environment for OLTP workloads. It enables to use both disk based tables and Memory-Optimized Tables together in the same queries and stored procedures. SQL 2016 - Has a major face-lift, increasing the maximum size of a table to an incredible 2 TB, up from 256 GB.
Partition Enhancements *	#	#	Individual partitions may now be rebuilt individually. If the most recent partitions become fragmented due to activity it's no longer necessary to rebuild indexes on the entire table.
Resource Governor Improvements *	#	#	Resource Governor now supports management of IO as well as CPU and Memory that were previously supported.
Columnstore Indexes *	#	#	Coulmnstore index funcaitonality supports highly aggregated data warehouse queries.SQL Server 2014 enhances to add support for creating Clustered Columnstore Index in addition to the non-clustered columnstore index which is for read-only queries. While in In SQL 2016, Columnstore indexes are no longer read-only.
Always On Improvements *	#	#	AlwaysOn Secondaries: SQL 2014 doubled the number of AlwaysOn readable secondary replica databases to eight, and SQL Server 2016 adds a third synchronous replica. More Reliable Secondaries: Previously (SQL 2012) several factors could result in secondaries being dropped offline which has been addressed in 2014/2016 and 2016 Secondary AlwaysOn replicas now automatically load balance READ workloads using round-robin. Scale to the Cloud: SQL 2014/2016 allows for Hybrid systems as you can now use Azure VMs as AlwaysOn AG replicas. AlwaysOn Availability Groups feature is now available in SQL 2016 Standard Edition with the limitation to one database and one non readable secondary replica as "Basic Availability Group (BAG) but allows multiple BAGs.
Improved Azure Integration *	#	#	SQL Server 2014 as a hybrid cloud platform, the implication being that SQL Server databases can now be more easily integrated with Windows Azure. SQL Server 2014 also lets store the data and log files associated with an on-premises database in Azure's storage. In addition, SQL Server Management Studio provides a deployment wizard that lets easy migration of existing on-premises databases to an Azure virtual machine.
BackupEnhancements (Both Standard and Enterprise)	#	#	Backup to Azure:With this new feature, backups can be stored offsite (in the cloud-Azure block blob storage) providing safe storage of backup data, as opposed to SQL Server 2014's limitation to backup online to page blob storage and no system databases and limited to 1 TB. In SQL 2016, the total backup size limit is increased to more than 12 TB and includes system databases. Smart Backup to Azure. With Smart Backups SQL Server determines whether a full or incremental backup is needed and backs up accordingly to Azure. Encryption: SQL Server will have the ability to encrypt the data while the backup is in progress. Can encrypt the database backup by specifying Encryption Algorithm and an Encryptor (Certificate or Asymmetric Key) while creating database backups.

* Enterprise Edition Only
**SQL 2016 Enterprise - Still in CTP