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

Please visit the below link for scripts:
www.mediafire.com/file/0xtp2g...
================================
Select name, is_encrypted from sys.databases
SELECT * FROM sys.symmetric_keys;
---Take backup from primary Replica.
BACKUP SERVICE MASTER KEY TO FILE = '\\Node1\tde\SMK'
ENCRYPTION BY PASSWORD = 'HYD@123'
---Restore in Secondary replicas,
Restore SERVICE MASTER KEY from FILE = '\\Node1\tde\SMK'
DECRYPTION BY PASSWORD = 'HYD@123'
----
Select name, is_encrypted from sys.databases
--Step1:Take the full database backup of Dba to be on safe side.
BACKUP DATABASE [dba] TO DISK = '\\Node1\tde\dba.bak' WITH NOFORMAT, NOINIT,
NAME = N'dba-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
---Create database master key
USE master;
Go
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'HYd@123';
GO
--Back up master key.
USE Master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'HYd@123';
BACKUP MASTER KEY TO FILE = '\\Node1\tde\exportedmasterkey'
ENCRYPTION BY PASSWORD = 'HYd@123';
--Restore in all secondary replicas.
---------------
USE master
GO
RESTORE MASTER KEY
FROM FILE = '\\Node1\tde\exportedmasterkey'
DECRYPTION BY PASSWORD = 'HYd@123'
ENCRYPTION BY PASSWORD = 'HYd@123'
--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 FILE = '\\Node1\tde\TDE_Certificate_For_dbadatabase.cer'
WITH PRIVATE KEY (file='\\Node1\tde\TDE_dba_private_CertKey.pvk',
ENCRYPTION BY PASSWORD='HYd@123');
--To Restore the certificate in All secondary replicas.
---OPEN MASTER KEY DECRYPTION BY PASSWORD = 'HYd@123'; Please use this option if u get any error and try.
USE master;
GO
Create CERTIFICATE [TDE_Certificate]
From FILE = '\\Node1\tde\TDE_Certificate_For_dbadatabase.cer'
WITH PRIVATE KEY (file='\\Node1\tde\TDE_dba_private_CertKey.pvk',
DECRYPTION BY PASSWORD='HYd@123');
--Step8:Check encryption enabled
Select name, is_encrypted from sys.databases
Select * from sys.certificates
---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,
percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
---Create availaiiity groups On Primary Replica[which is node1 now]
USE master;
GO
CREATE AVAILABILITY GROUP [TDE]
WITH (DB_FAILOVER = ON)
FOR REPLICA ON 'node1' WITH (ENDPOINT_URL = 'TCP://Node1.abc.com:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC),
'node2' WITH (ENDPOINT_URL = 'TCP://Node2.abc.com:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC);
--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

[@k.nagapavankumar6997]

Thanks for detailed live scenario explanation sir

[@MSSQLDBATechSupport]

Always welcome and explore more content

[@Danish_Khan..]

Thank you for sharing such helpful videos

[@MSSQLDBATechSupport]

My pleasure 😊

[@ssv91952]

Nice explanation Bro

[@MSSQLDBATechSupport]

Thanks and welcome

[@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

[@MSSQLDBATechSupport]

Good Question... will Test this scenario

[@k.nagapavankumar6997]

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

[@MSSQLDBATechSupport]

Will do once we set up envirnoment

[@ssv91952]

Please Share the Scripts

[@MSSQLDBATechSupport]

Hello Srinivasulu
Please find the below link for the scripts
www.mediafire.com/file/0xtp2gtrtbbdwvt/ADD_TDE_USER_database_into_always_on.txt/file