Sql server backup service master key6/20/2023 ![]() To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,500 database tech articles on his blog at. Pinal has authored 13 SQL Server database books and 45 Pluralsight courses. He holds a Masters of Science degree and numerous database certifications. Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 17 years of hands-on experience. I think this issue can also happen even with transaction log shipping based log restore also. OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password goes here'ĪLTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYĪs soon as we ran the command, the issue was resolved. This would allow SQL Server system SPIDs (like recovery spid) to automatically open it as required. We needed to add encryption by service master key to the database master key. ![]() 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.Īs we can see in the error log there are messages that imply TDE encrypted databases cannot be decrypted by system SPID. 17:46:42.15 spid24s Starting up database ‘TESTAG’. 17:46:42.09 spid55 ALTER DB param option: SET 17:46:42.09 spid55 ALTER DB TESTAG with AGNAME:ALWAYSONAG 17:46:41.65 spid55 The database ‘TESTAG’ is marked RESTORING and is in a state that does not allow recovery to be run. 17:46:41.60 spid55 Starting up database ‘TESTAG’. We found below error message in ERRORLOG and can see that database goes to not synchronizing. But as soon as the database is added to availability group it fails. They already followed the steps explained in books online to do backup and restore. I have asked more about database and found that the database was TDE enabled. There is a single Availability Group (ALWAYSONAG) which contains database TESTAG which is shown as not synchronizing. All three instances are SQL Server 2014 build. Three node AlwaysOn availability group with multi-subnet cluster with two nodes (SQL-A and SQL-B) within one subnet and the third node (SQL-C) in second subnet. Along with performance consulting, sometimes I also get requests from a few clients to look at the AlwaysOn availability group related problems.
0 Comments
Leave a Reply. |