Transparent Data Encryption (TDE) is used in SQL Server to protect data by encrypting physical files of the database.In TDE only files (ldf, mdf and bak) will be encrypted, but not the data. In this TDE, database will be encrypted using a Database Encryption Key. This database encryption key will be protected by a certificate and master key which are present in Master Database. To get the database information decrypted on the fly, a user connecting to the database should be accompanied by the Instance level Service Master key, this preserves the security in tact. As Service master key is at instance level, physical data files cannot be taken to other instances and get decrypted, that way data is protected.

-- ============================================================================
-- Transparent Data Encryption in SQL Server
--
-- Transparent Data Encryption(TDE) is used in SQL Server to protect data by
-- encrypting physical files of the database.In TDE only files (ldf, mdf and
-- bak) will be encrypted, but not the data. In this TDE, database will be
-- encrypted using a Database Encryption Key. This database encryption key will
-- be protected by a certificate and master key which are present in Master
-- Database. To get the database information decrypted on the fly, a user
-- connecting to the database should be accompanied by the Instance level
-- Service Master key, this preserves the security in tact. As Service master
-- key is at instance level, physical data files cannot be taken to other
-- instances and get decrypted, that way data is protected.
--
-- Follow below steps to encrypt a database:
--
-- NOTE: Make sure your Database edition is one of Enterprise, Developer,
-- Evaluation editions. Express Edition doesn't support TDE.
--
-- Source:
-- http://www.intstrings.com/ramivemula/articles/implement-transparent-data-encryptiontde-in-sql-server/
-- ============================================================================

-- Step 1: Create a Master Key
-- The database master key is a symmetric key used to protect the private keys
-- of certificates and asymmetric keys that are present in the database.
use [master];
go

create master key encryption by password = 'P@ssw0rd';
go

-- Step 2: Create a Certificate
-- CREATE CERTIFICATE can load a certificate from a file or assembly. It can
-- also be used to create a self signed certificate.
create certificate AdventureWorksTDECert with subject = 'ADWorksTDE';
go

-- Step 3: Create a Database Encryption Key
-- A database encryption key is required before a database can be encrypted by
-- using Transparent Database Encryption (TDE).
use [AdventureWorks2012];
go

create database encryption key
with algorithm = aes_256 encryption
by server certificate [AdventureWorksTDECert];
go

-- Step 4
-- Encrypt database
alter database AdventureWorks2012 set encryption on;
go