SQL Server 2019 – TDE DB Recovery Errors, Log Shipping & CU16


Userlevel 7
Badge +20

Microsoft have recently released SQL Server 2019 CU16, and in this blog post I’m calling out a very specific bug that Microsoft have addressed, and the implications this has to business-as-usual operations.

 

Transparent Data Encryption (TDE) & Compressed Backups

Microsoft have been tracking a bug related to using TDE and compressing backups, impacting both database, and log backup types. Should you attempt to restore a compressed backup of a TDE database, you’ll likely receive a message similar to the below:

Msg 3241, Level 16, State 18, Line
The media family on device 'BackupFileName' is incorrectly formed. SQL Server cannot process this media family.

Oh dear. This isn’t good! Prior to CU16, Microsoft’s workaround was to simply not compress your backups. Before we proceed any further, I also want to call out that I’ve not yet heard of any examples of Microsoft retrospectively fixing these backups, so if you’re currently in this dangerous scenario, mitigate ASAP.

 

SQL Server 2019 CU16 Released with Breaking Changes

This brings us onto the resolution, Microsoft have created a new backup format for compressed, TDE enabled DB & log backups. However, only CU16 is aware of how to process this new backup format, leaving you unable to restore these backups onto earlier versions of SQL Server.

At this point, you might be thinking, why on earth would I need to worry about this? So, consider the following two scenarios:

1. Primary & Secondary SQL Servers

It’s not uncommon for larger organisations, or any organisation with a mission critical app, to have multiple SQL servers. Commonly with the design to provide rapid restoration of service in a back-end related outage. If you have such a configuration, you might take a phased upgrade approach. This is where a SQL Server, commonly a secondary, gets patched to the latest CU, the workload is migrated over to the secondary, becoming the new primary, and then waiting to discover any potential bugs/issues that might necessitate backing out to your original primary server, via DB restoration.

If that is your scenario, this won’t work, as your DB restore will instead end with an error such as the below:

Restore Database Error:

Msg 3013, Level 16, State 1, Line <LineNumber>
RESTORE DATABASE is terminating abnormally.
Msg 9004, Level 21, State 1, Line <LineNumber>
An error occurred while processing the log for database 'TDE_DB'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Restore Log Error:

Location:     mediaRead.cpp:1018
Expression: readSize <= m_Demand
SPID:         84
Process ID: ProcessID
Msg 3013, Level 16, State 1, Line <LineNumber>
RESTORE LOG is terminating abnormally.
Msg 3624, Level 20, State 1, Line <LineNumber>
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

2. Log Shipping

The most common scenario, shipping transaction logs between SQL Servers, commonly tied into the previous scenario. If this is your scenario, the transaction log shipping will be broken until the SQL Servers are all running CU16.

 

Workarounds

You may have decided that you’re not willing to jump feet first into CU16 and want to be more cautious, and I completely respect that! So, how can you keep everything going smoothly if you want to conduct a phased approach to upgrading?

The answer is simple, use the CU15 workaround, disable compression. It might be a pain, but your alternatives are unrecoverable backups, and nobody wants them, right?

 

More Information:

Before I close this blog post out, I want to stress an important point, you can’t just install CU16 and assume all is fixed. You need to then also create new backups, so SQL Server can utilise the new backup format, and provide you with recoverable backups.

Want to find out more? You can read more about this bug, KB5014298, here.

If you want to read more about CU16 in its entirety, check out KB5011644, here.


10 comments

Userlevel 7
Badge +12

Interesting post @MicoolPaul. I've never seen a SQL server which uses encryption but now I'm aware of potential problems.

I was wondering how we can restore such databases with Veeam and found this KB article: https://www.veeam.com/kb2006 So in addition to backing up the SQL server, one should also export the certificate.

Userlevel 7
Badge +20

Great point @regnor, I used to love asking “who’s responsibility is it to backup the key” and nobody would be quite sure how to backup the key or who should be protecting it. But it was better that it got called out during implantation and therefore documented, before the disaster!

Userlevel 7
Badge +17

Very interesting topic. We don't have many SQL server dbs at the moment, but we will get them this year. So, I am aware of some points I have to give attention to. 😎👍🏼

Userlevel 7
Badge +12

Great point @regnor, I used to love asking “who’s responsibility is it to backup the key” and nobody would be quite sure how to backup the key or who should be protecting it. But it was better that it got called out during implantation and therefore documented, before the disaster!

That's right. Starting to search for the key or ways to export it during a disaster will be horror. And it will get worse if there's no way to access the key and you end up with a useless backup.

Userlevel 7
Badge +9

Interesting post @MicoolPaul. I've never seen a SQL server which uses encryption but now I'm aware of potential problems.

I was wondering how we can restore such databases with Veeam and found this KB article: https://www.veeam.com/kb2006 So in addition to backing up the SQL server, one should also export the certificate.

Very rarely used! I have had the opportunity to use this technology “Transparent Data Encryption (TDE)” when I did setup Pleasant Password Manger: https://techdirectarchive.com/2019/08/13/pleasant-password-server/. There was a need to protect the password saved on the Db.

  • I am aware this guide is old and will be updated anytime soon. 
Userlevel 7
Badge +20

This is great information to know for SQL and nice to see MS fix things.  Will read in more detail for sure.

Userlevel 7
Badge +7

Thanks for your sharing! @MicoolPaul 

Userlevel 7
Badge +7

thanks @MicoolPaul 

I have some SQL in TDE mode, bad news for DBAs 🤣

Userlevel 7
Badge +8

Great article, bookmarked for later use :)

Userlevel 7
Badge +13

Thanks for sharing Michael! Never used an encrypted SQL Server. However, good to know!

Comment