Hi Friends,
This post is just to share my Experience and findings with respect to TDE in Azure.
In our Environment we just have Azure Managed instance and I am still awaiting for my chance to work on Azure Virtual machines ,Azure SQL and Of course AWS.
Okay coming back to topic in Azure MI we have 2 options related to Transparent Data Encryption
1) Service Managed Key
2) Customer Managed Key
I don't have full permissions on Azure portal to carry out the testing on this however below links help you to understand on those.
https://www.mssqltips.com/sqlservertip/6641/azure-sql-database-tde-customer-managed-keys/
once your MI gets created by default TDE will operate in SMK Model.
I created a database by name TEST and it is encrypted by SMK however we have our own requirements and later Windows Team changed that to Customer Managed Key.
If you worked on MI by this time you know that we can initiate only copy-only full backups.
Msg 41904, Level 16, State 2, Line 3
BACKUP DATABASE failed. SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
If you try to perform any other backups you will get the above message. As said above TEST database got encrypted by SMK initially and later it got changed to Custom Managed key.
In the meanwhile I created Storage account to carry out the manual backups.
When I started taking the backup I got the below error
Msg 41937, Level 16, State 1, Line 1
BACKUP WITH COPY_ONLY cannot be performed until after the next automatic BACKUP LOG operation
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Later I created one more database by name Ireland and triggered the copy only backups and no issues were identified with that. In Azure MI we have automated backups in place and for every 5 minutes we do have log backups but despite of waiting for 30 minutes the error still persisted.
I Later realized it could be because of encryption so I turned off the encryption using the below command
alter database TEST set encryption off
But when cross checked the state of the database it lasted in the below stage "protection change in progress"
Because of the above reason I could see the below messages being recorded inside the Error logs.
2020-12-16 16:32:57.950 Backup BACKUP failed to complete the command BACKUP DATABASE TEST. Check the backup application log for detailed messages.
2020-12-20 11:19:45.950 Backup BACKUP failed to complete the command BACKUP DATABASE TEST. Check the backup application log for detailed messages.
2020-12-20 14:17:25.880 Backup BACKUP failed to complete the command BACKUP DATABASE TEST. Check the backup application log for detailed messages.
How to overcome this? I really don't know the answer for this and I tried the below commands
alter database test set encryption resume
alter database test set encryption suspend
Later I ran the backup but still the error persisted and finally I ran Checkdb for that database thinking by any chance if there were any errors but no errors were reported and to my surprise the backup worked.
2020-12-20 14:12:50.360 spid84 DBCC CHECKDB for database id 5 found 0 errors and repaired 0 errors.
Even now I am not clear the reason behind this. However if you carefully observe the messages I ran checkdb at 14:12 and the last backup failure message was on 14:17 so I guess there is some co-relation.
I would like to add few more points of what I observed
1) we can't track the history of the automated backups using Msdb backupset
2) By Default compression is not enabled for Managed instance however if you turn on it will helps only for Manual copy only backups . Again I am not sure if Automated backups will have them in place.
Comments