8

Breaking News: SQL 2019 CU16 Changes Backup Formats, Can Break Log Shipping.

 2 years ago
source link: https://www.brentozar.com/archive/2022/05/breaking-news-sql-2019-cu16-changes-backup-formats-can-break-log-shipping/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Breaking News: SQL 2019 CU16 Changes Backup Formats, Can Break Log Shipping.

The newest Cumulative Update for SQL Server 2019 has this gem in the release notes:

sql_server_2019_cu16-600x328.jpg
It’s not a known issue, it’s an improvement

This is a massive problem if:

  • You have databases encrypted with TDE
  • You take backups WITH COMPRESSION (which has a history of bugs already), or you have the server’s default compression option turned on
  • You’re doing log shipping to other 2019 servers
  • You patch the primary to CU16 before you patch the secondaries

You might say, “Well, you should always patch the secondaries first” – but in some shops, here’s how they approach patching:

  • Patch the secondary (Server2)
  • Fail over to the patched secondary (Server2) but do not patch the former primary (Server1) just yet, because you’re worried about something going wrong in the patching
  • Wait a few hours or days before patching Server1

At that point, the unpatched Server1 is the secondary – but it can no longer restore backups because of this “feature” of CU16. (That’s exactly how I discovered this issue – a client’s log shipping was broken when they had problems with CU16 and tried to fail back to Server1 – but they couldn’t. CU16 is a one-way trip if you’re using log shipping.)

The “feature” is detailed in this KB article, which says:

feature-600x88.jpg

In my client’s case, to get back to the CU15 servers quickly, we had to take uncompressed full & log backups from the CU16 primary over to the CU15 server, and were then able to go live on CU15 again. (I wasn’t involved in troubleshooting the issues they had with CU16 that drove them to fall back to CU15.)


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK