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.
