Add TDE encrypted database to an Availability Group

You will see below error when you try to add database to availability group if database is TDE encrypted.  This is also an issue when you restore TDE enabled database and then you try to add it back to availability group.

This wizard cannot add a database containing a database encryption key to an availability group. Use the CREATE or ALTER AVAILABILITY GROUP Transact-SQL statement instead. For more information see SQL Server Books Online.

This database already belongs to this availability group.

 

Below are the steps to follow to add TDE encrypted database to availability group.

Run below scripts on Primary replica instance

USE Master
BACKUP DATABASE DatabaseName TO DISK = ‘E:\Database\DatabaseName_Full_20190614.bak
GO

BACKUP LOG DatabaseName TO DISK = ‘E:\Database\DatabaseName_Full_20190614_log.trn‘;
GO

ALTER AVAILABILITY GROUP AG_GroupName ADD DATABASE DatabaseName 
GO

 

Run below scripts on Secondary replica instance

Copy full database back and log file from primary replica instance to secondary replica server on local drive

Use Master
RESTORE DATABASE DatabaseName 
FROM DISK = ‘E:\Database\DatabaseName_Full_20190614.bak
WITH NORECOVERY
GO

RESTORE LOG DatabaseName 
FROM DISK = ‘E:\Database\DatabaseName_Full_20190614_log.trn
WITH NORECOVERY
GO

ALTER DATABASE DatabaseName  SET HADR AVAILABILITY GROUP = AG_GroupName
GO

 

Happy Deployment!

 

Posted in Microsoft Technology, SQLServer Tagged with:

Ads