As a data professional managing SQL Servers, you need to be thinking about Windows and SQL Server patching.
You want to stay up to date, but you also have to be careful because sometimes staying fully up to date comes with the risk that a new patch might break a feature you are using. This is possibly the case for SQL Server 2019 CU 16. If you’re using Log Shipping on a database that has TDE enabled and compression is enabled on those backups, then you need to take heed.
I’ll walk you through what can happen and what I’ve seen work to resolve the problem.
In this link we see the below beauty of a box that seems like they are telling you about an improvement.
In my opinion this is mislabeled. It really should say:
In a link on that first KB article, you’re taken to a page that shows this:
Seeing this error in your log shipping after you’ve patched to SQL Server 2019 CU 16 is your first clue that you’ve been bitten by this problem.
If you’re having this problem, what do you do?
You might be thinking you can just update the Log Shipping Secondary to CU 16 and that fixes everything. You would expect or hope that to be true, but you will be disappointed. That doesn’t work.
Here is what I have seen work:
- Change the backup compression option setting in the Log Shipping UI away from “Use the default server setting”, which is typically going to be set to “Compress Backups”. Instead make the selection in this UI be “Do not compress backup”.
- Take a new full backup of the database on the primary using the WITH NO_COMPRESSION option and restore it to the secondary using WITH REPLACE
- Now the Log Shipping jobs should be able to apply new, uncompressed log backups to the secondary.
Next Steps To Take
- If you haven’t updated to SQL Server 2019 CU16 but you plan to and you have TDE enabled databases that have compressed backups, consider turning off backup compression in your Log Shipping before you update.
- If you have questions or comments, leave me a comment on this post, or message me on LinkedIn or Twitter
- Check out my Services page and set up a Zoom call with me via my Calendly so we can discuss the problems you’re having in your environment and how I can help.