Last week I enabled TDE encryption on my AlwaysOn Availability Group databases of SQL Server 2012. During the encryption of one of the larger databases, bigDB (~200gb) a failover event occurred. Of the 4 databases in the AG, 3 recovered without issue but the last stayed in a recovery pending state. Checking the error logs now I see that there is no line that states "Recovery is writing a checkpoint in database big DB..." as there is for the other databases in the AG. I could not fail-back, with or without data loss. I first restarted the failed server in the AG and verified that it was working as expected. I could then move some AGs back to the original server I just restarted but still could not recover bigDB. The dashboard at this time showed that that bigDB was critical and 'Not Synchronizing'. The option to 'resume data movement' was grayed out and the t-sql statement to do the same resulted in an error. As a last resort I failed all the other AGs back to the original server and forced a restart of the SQL Server Service. At this time the error logs indicate that the SQL Server began to recover and completed after 2293 seconds. Normally this recovery takes less than a minute and I have never seen it take any longer than 2 min.

Could this have been a symptom of the incomplete TDE encryption? I can not find any documentation that indicates that a failover during encryption is not supported and I find it hard to believe that it is not. I have engaged Microsoft support on this issue and they are claiming that the Database would have 'eventually recovered' if I had just left it alone long enough but my error logs do not back that statement up. I am at a bit of a loss of where to keep looking to find the cause of the recovery failure but am VERY interested in determining why this happened as it was 5 hour outage for us. Has anyone experienced a recovery failure similar to this?

  • Were you watching the sys.dm_database_encryption_keys DMV during all this? That might shed some light, though I admit I don't have an answer for you. – FilamentUnities Apr 13 '15 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.