Transparent Data Encryption in SQL Server

This feature is only available in the Enterprise edition of SQL Server 2014 and later, which secures the database from being sniffed by intruders as the primary files and the backups are useless until you have the encryption details.

Create the Master Key and Certificate to be used for the databases

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sql@123';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE DBATest;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE DBATest
SET ENCRYPTION ON;
GO

/* The value 3 represents an encrypted state on the database and transaction logs. */

USE DBATest;
GO
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO

Backup certificate and key on source for restore purpose on a secondary server.

USE master;
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'N:\adhoc\_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'N:\adhoc\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Sql@123')

Create master key and certificate at the destination before restore. Until you have the key and certificate create as given below and you will not be able to restore the database from the backup taken on the primary server.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test@123'

CREATE CERTIFICATE MyServerCert
FROM FILE = 'D:\SQLEX2016\Backup\_TDE_Test_Certificate.cer'   
WITH PRIVATE KEY (FILE = 'D:\SQLEX2016\Backup\certificate_TDE_Test_Key.pvk',
DECRYPTION BY PASSWORD = 'Sql@123')

Restrictions

The following operations are not allowed during initial database encryption, key change, or database decryption:
Dropping a file from a filegroup in the database
Dropping the database
Taking the database offline
Detaching a database
Transitioning a database or filegroup into a READ ONLY state
The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
Dropping a file from a filegroup in the database.
Dropping the database.
Taking the database offline.
Detaching a database.
Transitioning a database or filegroup into a READ ONLY state.
Using an ALTER DATABASE command.
Starting a database or database file backup.
Starting a database or database file restore.
Creating a snapshot.
The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
The database is read-only or has any read-only file groups.
An ALTER DATABASE command is executing.
Any data backup is running.
The database is in an offline or restore condition.
A snapshot is in progress.
Database maintenance tasks.

Advantages

The performance impact of TDE is minor. Estimated to be around 3-5%
Performs real-time I/O encryption and decryption of the data and log files
Encrypts the Entire Database in rest
No architectural changes needed
No application code changes are required and the user experience is the same
Easy to implement
DBAs can still see the data

Disadvantages

No protection for data in memory
Not granular – Cannot just encrypt specific tables/columns
Not good for high CPU bottleneck servers
Not protected through communication/networks