Transparent Data Encryption (TDE) for Always On Availabilty Group database

Question

Hi,

We have TDE enabled database in one of our production database of SQL server 2017. We need to configure always on set up to secondary server. As per various site I found I have to create master key in secondary replica then need to restore original certificate and private key. After that I have to restore full bakcup and transaction log backup from primary to secondary reply with no-recovery mode. Upto this all are fine. Then I have to run below command to add the database in availability group-

USE master
GO
ALTER AVAILABILITY GROUP [avail_group_name] ADD DATABASE [database_name]

But I get error when I run this command as there is no availability group and I can not alter any availabity group which does not exist. Also let me know what command should I use to add the database in secondary replica.

Answer 1

Hi @Kazi Ariful Haq ,

Welcome to Microsoft Q&A!

Could you please use the sys.dm_database_encryption_keys dynamic management view to find the state of database encryption. In TDE, all files and filegroups in a database are encrypted. If any filegroup in a database is marked READ ONLY, the database encryption operation fails, please get more information from this link.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Kazi Ariful Haq , we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!