Automatic seeding of availability database in availability group failed with a transient error. The operation will be retried.
Hi Mates,
This post helps you to solve the Error message that has been mentioned in the Title of the post.
We have 3 servers in One Data Center and other 3 in Second Data center. Most of the servers which are running on 2012/2014 are in Multi Subnet. However from SQL 2016 we started making use of Distributed Availability Groups.
Coming back to the issue my colleague told me that few of the Databases were not appearing in Forwarded Server (means primary replica of secondary Availability Group). When I checked the Error logs on the primary Replica I could find the below message
Automatic seeding of availability database 'TDEtesting' in availability group 'TEST_DAG' failed with a transient error. The operation will be retried.
My initial impression was there might be something wrong related to Seeding settings but in both of the Availability Groups they are in Automatic mode. To my surprise the database is synchronized state in secondary replica on the primary Availability Group.
what went wrong on primary(Forwarder) and secondary replicas of Secondary Availability Groups?
This time I checked the logs on the Forwarder server and below are the messages
2020-12-15 14:27:04.830 spid52s Error: 911, Severity: 16, State: 1.
2020-12-15 14:27:04.830 spid52s Database 'TDEtesting' does not exist. Make sure that the name is entered correctly.
2020-12-15 14:27:05.090 spid52s Error: 33111, Severity: 16, State: 3.
2020-12-15 14:27:05.090 spid52s Cannot find server certificate with thumbprint '0x3428858AF24F0D37387E7F5613AC5069E319D1F1'.
2020-12-15 14:27:05.090 spid52s Error: 3013, Severity: 16, State: 1.
2020-12-15 14:27:05.090 spid52s RESTORE DATABASE is terminating abnormally.
2020-12-15 14:27:05.090 spid52s Automatic seeding of availability database 'TDEtesting' in availability group 'TEST_DAG' failed with a transient error. The operation will be retried.
By looking at the logs I clearly understood the issue. The reason is that the problematic databases were part of Transparent Data Encryption.
My colleague added the database only on to Secondary replicas of the primary availability group by copying the certificate and private keys. By the way we can't add a database that is part of TDE through GUI. It has to be done manually using join option.
Also keep in mind at the time of creating the Distributed Availability Groups we need to ensure that there are no user databases in the availability group on the forwarded server.
One more Tip till today I was under impression that we can enable TDE on a database only by using server certificate but we can do this using asymmetric key too.
I tried doing this but this requires EKM and it has been very well described in the below link
Msg 33120, Level 16, State 1, Line 5
In order to encrypt the database encryption key with an asymmetric key, please use an asymmetric key that resides on an extensible key management provider.
Comments