As a SQL Server consultant, I often come across various challenges related to performance tuning and availability group configurations. One common issue that clients face is when a TDE (Transparent Data Encryption) enabled database fails to synchronize within an AlwaysOn availability group.

Let’s take a look at a specific scenario to understand this problem better. Consider an environment with a three-node AlwaysOn availability group, consisting of two nodes (SQL-A and SQL-B) in one subnet and the third node (SQL-C) in a different subnet. All three instances are running SQL Server 2014 build 12.0.4100. The availability group, named ALWAYSAG, contains a database called TESTAG, which is currently not synchronizing.

Upon further investigation, it was discovered that the TESTAG database had TDE enabled. The client had followed the steps mentioned in the SQL Server Books Online to perform the backup and restore operations. However, as soon as the database was added to the availability group, it failed to synchronize. The error log revealed the following message:

2016-12-23 17:46:41.60 spid55 Starting up database 'TESTAG'.
2016-12-23 17:46:41.65 spid55 The database 'TESTAG' is marked RESTORING and is in a state that does not allow recovery to be run.
2016-12-23 17:46:42.09 spid55 ALTER DB TESTAG with AGNAME:ALWAYSONAG
2016-12-23 17:46:42.09 spid55 ALTER DB param option: SET
2016-12-23 17:46:42.15 spid24s Starting up database 'TESTAG'.
2016-12-23 17:46:42.16 spid24s Error: 15581, Severity: 16, State: 7.
2016-12-23 17:46:42.16 spid24s Please create a master key in the database or open the master key in the session before performing this operation.

The error log clearly indicates that TDE encrypted databases cannot be decrypted by system SPIDs. In order to resolve this issue, we need to add encryption by the service master key to the database master key. This will allow SQL Server system SPIDs, such as the recovery SPID, to automatically open the master key as required.

To implement the solution, execute the following command:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password goes here'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Once these commands are executed, the issue should be resolved, and the TESTAG database should start synchronizing within the AlwaysOn availability group.

It’s important to note that this issue can also occur with transaction log shipping-based log restores. Therefore, it’s crucial to ensure that the master key is properly configured in such scenarios as well.

By understanding the relationship between TDE encryption and the master key, you can effectively troubleshoot and resolve synchronization issues within your SQL Server AlwaysOn availability groups.