Transparent Data Encryption

PRIMARY REPLICA

STEP 1

USE MASTER 
BACKUP DATABASE DEMO TO DISK = 'E:\BACKUP\DEMO.Bak' 
GO 
BACKUP LOG DEMO TO DISK = 'E:\BACKUP\DEMO.trn' 

STEP 2

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTDBPASSWORD' 

--SELECT name KeyName,  symmetric_key_id KeyID,  key_length KeyLength, 

--algorithm_desc KeyAlgorithm,create_date FROM sys.symmetric_keys;

STEP 3

CREATE CERTIFICATE TDECert WITH SUBJECT = 'My TDE Certificate for all user database'

--SELECT name CertName,   certificate_id CertID,   pvt_key_encryption_type_desc EncryptType, 

--issuer_name Issuer,start_date,expiry_date FROM sys.certificates 

STEP 4

USE [DEMO]

GO  

CREATE DATABASE ENCRYPTION KEY  

WITH ALGORITHM = AES_128  

ENCRYPTION BY SERVER CERTIFICATE TDECert 

GO 

SELECT DB_NAME(database_id) DbName,   /*CASE CONVERT(int,[encryption_state]) 

    WHEN 0 THEN 'no encryption'     WHEN 1 THEN 'Unencrypted' 

WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' 

WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' 

WHEN 6 THEN 'Protection change in progress'     ELSE encryption_state 

END as [EncryptState]*/ encryption_state EncryptState,   key_algorithm KeyAlgorithm,   key_length KeyLength, 

encryptor_type EncryptType,percent_complete FROM sys.dm_database_encryption_keys; 

STEP 5

   USE DEMO

   ALTER DATABASE [DEMO] SET ENCRYPTION ON

STEP 6

USE MASTER  

GO  

BACKUP CERTIFICATE TDECert   

TO FILE = 'E:\BACKUP\TDECert_File.cer'  

WITH PRIVATE KEY (FILE = 'E:\BACKUP\TDECert_Key.pvk' ,  

ENCRYPTION BY PASSWORD = 'TESTDBPASSWORD' )  

GO

BACKUP DATABASE DEMO TO DISK = 'E:\BACKUP\DEMOTDE.Bak' 

GO 

BACKUP LOG DEMO TO DISK = 'E:\BACKUP\DEMOTDE.trn' 

GO 

==============Secondary replica,

USE MASTER  

GO  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTDBPASSWORD' 

GO  

CREATE CERTIFICATE TDECert  

FROM FILE = 'E:\BACKUP\TDECert_File.cer'   

WITH PRIVATE KEY (FILE = 'E:\BACKUP\TDECert_Key.pvk',   

DECRYPTION BY PASSWORD =  'TESTDBPASSWORD'

GO

ALTER DATABASE [DEMO] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [DEMO] FROM  DISK = N'E:\BACKUP\DEMOTDE.Bak' 

WITH  FILE = 1,  MOVE N'DEMO' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SECOND\MSSQL\DATA\DEMO.mdf', 

MOVE N'DEMO_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SECOND\MSSQL\DATA\DEMO_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE [DEMO] SET MULTI_USER

GO

=============ROLLBACK

USE DEMO

ALTER DATABASE [DEMO] SET ENCRYPTION ON

DROP DATABASE ENCRYPTION KEY

USE MASTER

DROP CERTIFICATE TDECertificate

DROP MASTER KEY