How To Add a TDE Enabled Database To An Always On Availability Group

Please visit the below link for scripts:
================================

---Take backup from primary Replica.
BACKUP SERVICE MASTER KEY TO FILE = '\\Node1\tde\SMK'

---Restore in Secondary replicas,
Restore SERVICE MASTER KEY from FILE = '\\Node1\tde\SMK'

----

--Step1:Take the full database backup of Dba to be on safe side.
NAME = N'dba-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

---Create database master key
USE master;
Go
CREATE MASTER KEY
GO

--Back up master key.
USE Master;
GO
BACKUP MASTER KEY TO FILE = '\\Node1\tde\exportedmasterkey'

--Restore in all secondary replicas.
---------------
USE master
GO

RESTORE MASTER KEY
FROM FILE = '\\Node1\tde\exportedmasterkey'

--Step3:Create certificate in Primary replica
USE master;
GO
CREATE CERTIFICATE TDE_Certificate
WITH SUBJECT='Certificate for TDE';
GO

--Step4:Create database encryption key
USE dba
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

--Step6:Turn on encryption on database
ALTER DATABASE Dba
SET ENCRYPTION ON

--Step5:Back up the certificate and the private key associated with the certificate
USE master;
GO
BACKUP CERTIFICATE [TDE_Certificate]

--To Restore the certificate in All secondary replicas.
USE master;
GO
Create CERTIFICATE [TDE_Certificate]

--Step8:Check encryption enabled

---Monitor TDE Progress:

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, 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 (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,

---Create availaiiity groups On Primary Replica[which is node1 now]
USE master;
GO
CREATE AVAILABILITY GROUP [TDE]
WITH (DB_FAILOVER = ON)

--Add the database to AG from primary
USE master
GO
ALTER AVAILABILITY GROUP TDE ADD DATABASE [Dba]

--Use on secondary replicas Node2
Use Master
Go
ALTER DATABASE Dba SET HADR AVAILABILITY GROUP = TDE;

============================
--Roll back up steps For TDE

--Step1:
Use dba
Go
Alter Database dba Set Encryption off

--Step2:
Use dba
go
Drop database encryption key

--Step3:
Use Master
Go
Drop certificate TDE_Certificate

--Step4:--Optional
Use master
Go
Drop master key

[basheerahmed]

Fantastic content! This video really helped me understand things better. I appreciate the clear explanations and practical tips. Keep sharing such amazing insights – I’ll be eagerly waiting for the next one

[neeleshpathri]

Can you please tell me what is the process for Existing Db as you added New DB and applied TDE What about for Existing DB

[k.nagapavankumar]

Thanks for detailed live scenario explanation sir

[Danish_Khan..]

Thank you for sharing such helpful videos

[k.nagapavankumar]

Hello sir,

When you get a chance pls do a demo on SQL upgrade from lower version to higher version which includes Alwayson databases sor