Recently, one of my colleague has encountered an issue an error while adding a database to availability group as below:
“The database is encrypted by database master key, you need to provide valid password when adding to the availability group.”
The error message is self explanatory one as this database does not meet a requirement to add AG as this database is protected with a master key. We may need to provide the master key while configuring the database into AG.Please note that this is not due to TDE, but due to master key.
One observation is that this is recently added as a validation in SQL Server 2016 version. Earlier version, you would be able to add seamlessly.
There are two solutions to add these databases into AG:
1. Use T-SQL to add the database into AG
ALTER AVAILABILITY GROUP AGName ADD DATABASE DBName
2. Provide the password and use GUI with few extra steps
In GUI, this is not so clear for the user that the password can be provided directly and configure AG.
Double click in the Password column
Type the password in the column
Click Refresh (To enable check box for the database)
Check the checkbox for the database.
Click Refresh again (To enable the Next button)
Click Next to progress