Transparent Data Encryption

* To provide security for data we can use encryption option.
* To provide security for data and T.Log files of a database as well as backups, we need TDE which  was introduced in SQL Server 2008.
Steps
------
Create a master key
Create or obtain a certificate protected by master key.
Create a database key and protect it by the certificate.
Set the database you want to protect to use the encryption.
--step1:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
GO
--step2:
CREATE CERTIFICATE MyTDECert WITH SUBJECT = 'My TDE Certificate'
GO
--step3: To check existing certificates
SELECT * FROM sys.certificates where [name] = 'MyTDECert'
GO
--step4:
Use AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyTDECert
GO
--step5
ALTER DATABASE AdventureWorks SET ENCRYPTION ON
GO
--Verifying TDE
--step1
    * R.C on database--> Properties -->Options --> Check
        Encryption Enabled: True
--step2:
    Find the current path of the files
    sp_helpdb AdventureWorks

--step3: Detach database
    use master
    go
    sp_detach_db AdventureWorks
    go
--step4:
    Copy data and T.Log files of AdventureWorks into
        d:\AdventureWorks_Files
--step5: Connect to another instance of SQL Server
CREATE DATABASE [AdventureWorks] ON
( FILENAME = 'D:\AdventureWorks_Files\AdventureWorks.mdf'),
( FILENAME = 'D:\AdventureWorks_Files\AdventureWorks_log.ldf')
FOR ATTACH
GO
--The above step fails
--step6: Go to first server take backup of certificate
Use Master
GO
BACKUP CERTIFICATE MyTDECert TO FILE = 'D:\MyTDECert.cert'
 WITH PRIVATE KEY
 (
 FILE = 'D:\EncryptPrivateKey.key',
 ENCRYPTION BY PASSWORD = 'TryToUseOnlyStrongPassword'
 )
GO
--step7: Go to second server where need to attach db
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
GO
CREATE CERTIFICATE MyTDECert
 FROM FILE = 'D:\MyTDECert.cert'
 WITH PRIVATE KEY (
 FILE = 'D:\EncryptPrivateKey.key'
 , DECRYPTION BY PASSWORD = 'TryToUseOnlyStrongPassword'
 )
--step8: Run step5 in second server. Now db is attached successfully