Configure a SQL Server Database using Transparent Data Encryption and Database Mirroring

--STEP 1 TO CREATE MASTER KEY ON SQL2016 INSTANCE
Use master 
GO
 
Create Master Key Encryption By Password = 'C09ml_K3Y_PW23J!ly@2vc'
GO

-- ONCE MASTER KEY IS CREATED, BACKUP THE MASTER KEY
-- CREATE APPROPRIATE FOLDER LOCATION ON THE SERVER
BACKUP MASTER KEY TO FILE = 'D:\DO_NOT_DELETE\masterkeybackup' 
   ENCRYPTION BY PASSWORD = 'C09ml_K3Y_PW23J!ly@2vc'

-- ONCE MASTER KEY IS CREATED, CREATE A CERTIFICATE
Create Certificate primary_cert
With Subject ='primary_cert'
GO

-- ONCE CERTIFICATE IS CREATED, CREATE MIRRORING END POINT
Create Endpoint Mirroring_Endpoint_SQL2016
State = Started
AS TCP (Listener_Port = 5022, Listener_IP = ALL) 
For Database_Mirroring
(
Authentication = Certificate primary_cert, 
Role = ALL
)
GO

--BACKUP THE MIRRORING CERTIFICATE ONCE MIRRORING ENDPOINT IS CREATED
Backup Certificate primary_cert
To File ='D:\DO_NOT_DELETE\primary_cert.cer' 
GO

--STEP 2 TO CREATE MASTER KEY ON SQL2016MIRROR INSTANCE
Use master 
GO
 
--Password need not be same as used in Step 1
Create Master Key Encryption By Password = 'C09ml_K3Y_PW23J!ly@2vc'
GO

-- ONCE MASTER KEY IS CREATED, BACKUP THE MASTER KEY
-- CREATE APPROPRIATE FOLDER LOCATION ON THE SERVER
BACKUP MASTER KEY TO FILE = 'D:\DO_NOT_DELETE\masterkeybackup' 
   ENCRYPTION BY PASSWORD = 'C09ml_K3Y_PW23J!ly@2vc'

-- ONCE MASTER KEY IS CREATED ON MIRROR INSTANCE, CREATE A CERTIFICATE
Create Certificate mirroring_cert
With Subject ='mirroring_cert'
GO

-- ONCE CERTIFICATE IS CREATED, CREATE MIRRORING END POINT
Create Endpoint Mirroring_Endpoint_SQL2016
State = Started
AS TCP (Listener_Port = 5023, Listener_IP = ALL) 
For Database_Mirroring
(
Authentication = Certificate mirroring_cert, 
Role = ALL
)
GO

--BACKUP THE MIRRORING CERTIFICATE ONCE MIRRORING ENDPOINT IS CREATED
Backup Certificate mirroring_cert
To File ='D:\DO_NOT_DELETE\mirroring_cert.cer' 
GO

--STEP 3
Use master 
GO

-- CREATE A SQL SERVER LOGIN ON SQL2016-principal server
Create Login mirror With Password ='!T#@Lgsnbs12'
GO

-- MAP A DATABASE USER TO THE NEW LOGIN
Create User mirror_user for Login mirror
GO

--ATTACH THE CERTIFICATE CREATED ON MIRROR INSTANCE SQL2016 MIRROR TO THE DATABASE USER
Create Certificate mirroring_cert
Authorization mirror_user
From File ='D:\DO_NOT_DELETE\mirroring_cert.cer' 
GO

-- GRANT PERMISSIONS TO THE MIRRORING ENDPOINT CREATED
Grant Connect on Endpoint::Mirroring_Endpoint_SQL2016 TO [mirror]
GO

--STEP 4
Use master 
GO

-- CREATE A SQL SERVER LOGIN ON SQL2016MIRROR-mirror server
Create Login primary With Password ='!T#@Lgsnbs12'
GO

-- MAP A DATABASE USER TO THE NEW LOGIN
Create User primary_user for Login primary
GO

--ATTACH THE CERTIFICATE CREATED ON PRINCIPAL INSTANCE SQL2016 TO THE DATABASE USER
Create Certificate primary_cert
Authorization primary_user
From File ='D:\DO_NOT_DELETE\primary_cert.cer' 
GO

-- GRANT PERMISSIONS TO THE DATABASE MIRRORING ENDPOINT CREATED
Grant Connect on Endpoint::Mirroring_Endpoint_SQL2016 TO [primary]
GO

--STEP 5
CREATE DATABASE TDE
GO

USE TDE
GO

--IN ORDER TO TEST TDE, CREATE CERTIFICATES
CREATE CERTIFICATE TDE_CERT WITH SUBJECT = 'TDE CERT'
GO

-- ONCE CERTIFICATE IS CREATED, BACKUP THE CERTIFICATE
BACKUP CERTIFICATE TDE_CERT 
TO FILE = 'D:\DO_NOT_DELETE\TDE_CERT_BAK.CER'
WITH PRIVATE KEY 
(
FILE = 'D:\DO_NOT_DELETE\TDE_CERT_PVT_KEY.PVK',
ENCRYPTION BY PASSWORD = 'T!i4asP2345W'
)
GO

-- CREATE A DATABASE ENCRYPTION KEY ON THE TEST DATABASE
USE TDE
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_CERT
GO

--ENABLE THE TEST DATABASE FOR TDE
ALTER DATABASE TDE
SET ENCRYPTION ON
GO 

-- ONCE TDE IS ENABLED ON THE DATABASE, PERFORM FULL BACKUP, FOLLOWED BY LOG FILE BACKUP
BACKUP DATABASE TDE TO DISK = 'D:\DO_NOT_DELETE\TDE.bak'
GO
BACKUP LOG TDE TO DISK='D:\DO_NOT_DELETE\TDE_LOG1.trn'
GO

--STEP 6
--CREATE THE CERTIFICATE ON THE MIRROR SERVER, USE THE CERTIFICATES COPIED FROM PRINCIPAL
CREATE CERTIFICATE TDE_CERT
FROM FILE = 'D:\DO_NOT_DELETE\TDE_CERT_BAK.CER' 
WITH PRIVATE KEY (FILE = 'D:\DO_NOT_DELETE\TDE_CERT_PVT_KEY.PVK', 
DECRYPTION BY PASSWORD = 'T!i4asP2345W');
GO

-- RESTORE DATABASE USING THE FILES COPIED FROM PRINCIPAL SERVER
restore database TDE
from disk='D:\DO_NOT_DELETE\TDE.bak'
with move 'TDE' to 'C:\mirror2\tde.mdf', -- Provide appropriate drive location
move 'TDE_log' to 'C:\mirror2\tde_log.ldf',-- Provide appropriate drive location
replace, norecovery,stats

-- ONCE DATABASE IS SUCCESSFULLY RESTORE, RESTORE THE LOG BACKUP
restore log TDE from DISK='D:\DO_NOT_DELETE\TDE_LOG1.trn' with norecovery

--STEP 7
--Run below step on the SQL2016MIRROR instance
alter database TDE set partner ='TCP://SQL2016:5022' --primary server endpoint
GO

--STEP 8
--Run below step on the SQL2016 instance- Principal instance
alter database TDE set partner ='TCP://SQL2016MIRROR:5023' --mirror server endpoint
GO