Nie można przywrócić bazy danych obsługującej TDE, gdy używane jest MAXTRANSFERSIZE i CHECKSUM

Aktualizacja : @AmitBanerjee - starszy menedżer programu dla grupy produktów Microsoft SQL Server potwierdził, że MS zajmie się problemem, ponieważ jest on wadą.

Czy ktoś napotkał problem z przywracaniem kopii zapasowych wykonanych na SQL Server 2016 z włączonym TDE i korzystających z MAXTRANSFERSIZE> 65536 (w moim przypadku wybrałem 65537, aby móc kompresować bazę danych TDE ) i CHECKSUM?

Poniżej znajduje się repro:

--- create database 
create database test_restore
go
-- create table
create table test_kin (fname char(10))
go
-- Enable TDE 

use master
GO
CREATE CERTIFICATE test_restore WITH SUBJECT = 'test_restore_cert'
GO
SELECT name, pvt_key_encryption_type_desc, * FROM sys.certificates WHERE name = 'test_restore'
GO
use test_restore
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE test_restore
GO 
alter database test_restore set encryption ON

Wykonaj kopię zapasową tylko z kopią… zrób to dwa razy ..

backup database test_restore 
to disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak' -- change as per your location !!
with init, stats =10  -- overwrite ..using INIT !!
, maxtransfersize = 65537
, compression
,CHECKSUM

Teraz zrób verifyonly...

restore verifyonly from disk = 'D:\temporary-short-term\test_restore_KIN_test_restore_1.bak'

Komunikat o błędzie :

Msg 3241, poziom 16, stan 40, wiersz 11 Rodzina mediów na urządzeniu „D: \ tymczasowo-krótkoterminowa \ test_restore_KIN_test_restore_1.bak” jest niepoprawnie utworzona. SQL Server nie może przetworzyć tej rodziny mediów. Msg 3013, poziom 16, stan 1, wiersz 11 WERYFIKUJ BAZA DANYCH kończy się nieprawidłowo.

Wyniki (1 = ON, 0 = OFF) z różnymi kombinacjami:

+-------------------------+-------------+----------+--------+
| MAXTRANSFERSIZE (65537) | COMPRESSION | CHECKSUM | RESULT |
+-------------------------+-------------+----------+--------+
|                       1 |           1 |        1 | FAIL   |
|                       1 |           1 |        0 | PASS   |
|                       1 |           0 |        1 | FAIL   |
|                       0 |           0 |        0 | PASS   |
|                       0 |           1 |        1 | PASS   |
|                       0 |           1 |        0 | PASS   |
+-------------------------+-------------+----------+--------+

Problem występuje w dniu:

Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64) 11 lipca 2016 22:05:22 Prawa autorskie (c) Microsoft Corporation Enterprise Edition (64-bit) w systemie Windows Server 2012 R2 Standard 6.3 (kompilacja 9600 :)

Udało mi się odtworzyć twój problem.

Dodanie FORMATdo BACKUPpolecenia rozwiązało to dla mnie.

Chociaż nie mogę znaleźć konkretnej dokumentacji, moim zdaniem jest to związane z faktem, że INITzachowuje istniejący nagłówek multimediów w zestawie kopii zapasowych podczas FORMATtworzenia nowego nagłówka multimediów.

Nadal badam ten problem i jeśli znajdę dodatkowe informacje, zaktualizuję tę odpowiedź.

Wygląda na to, że ten problem mógł zostać rozwiązany za pomocą KB 4032200:

• POPRAWKA: Nie można skompresować pliku kopii zapasowej, gdy opcja INIT i COMPRESSION jest używana w bazie danych obsługującej TDE w SQL Server 2016

Z tego wpisu:

Objawy

Załóżmy, że włączasz transparentne szyfrowanie danych (TDE) dla bazy danych w Microsoft SQL Server 2016. Próbujesz wykonać kopię zapasową bazy danych przy użyciu instrukcji BACKUP DATABASET-SQL, która ma zarówno opcję, jak COMPRESSIONi INITopcję. W tym scenariuszu możesz zauważyć, że istniejący plik kopii zapasowej jest zastępowany przez nowy plik kopii zapasowej, a nowy plik kopii zapasowej nie jest kompresowany.

Rozkład

Ten problem został rozwiązany w następujących aktualizacjach zbiorczych dla programu SQL Server:

• Zbiorcza aktualizacja 7 dla programu SQL Server 2016 RTM
• Zbiorcza aktualizacja 4 dla programu SQL Server 2016 z dodatkiem SP1

Wygląda na to, że może to być ten sam problem, który został później zaktualizowany w blogu, do którego odwoływałeś się w pytaniu:

Aktualizacja 6 kwietnia 2017 r

Niedawno odkryliśmy pewne problemy związane z użyciem TDE i kompresji kopii zapasowych w SQL Server 2016. Podczas ich rozwiązywania, oto kilka wskazówek, które pomogą Ci uniknąć napotkania tych znanych problemów:

• Obecnie nie jest zalecane stosowanie kopii zapasowych w paski z TDE i kompresją kopii zapasowych

• Jeśli Twoja baza danych ma wirtualne pliki dziennika (VLF) większe niż 4 GB, nie używaj kompresji kopii zapasowej z TDE do tworzenia kopii zapasowych dziennika. Jeśli nie wiesz, czym jest VLF, zacznij tutaj .

• Na razie unikaj korzystania z INIT podczas pracy z TDE i kompresją kopii zapasowych. Zamiast tego na razie możesz używać Z FORMATEM.

Inżynieria SQL pracuje nad rozwiązaniami tych problemów w SQL Server 2016. Ponownie zaktualizujemy ten post na blogu, gdy będziemy mieli dalsze informacje do udostępnienia.

Pomimo tej notatki post na blogu nie został zaktualizowany o żadne dalsze informacje od tego czasu.

Jednak KB 4019893 mogą również dotyczyć tego:

• Poprawka: Przywracanie kończy się niepowodzeniem podczas wykonywania kopii zapasowej przy użyciu kompresji i sumy kontrolnej w bazie danych z włączoną funkcją TDE w programie SQL Server 2016

Chociaż komunikat o błędzie zgłoszony w tym artykule bazy wiedzy jest inny niż ten, który zgłaszasz, czynniki, które się do niego zwracają, są bardzo podobne. Dodatek SP1 CU3 dla programu SQL Server 2016 najpierw zawierał poprawkę, co widać na liście poprawek . Istnieją jednak doniesienia , że nie rozwiązało to problemu we wszystkich sytuacjach.

Dodatek SP1 CU4 dla programu SQL Server 2016 zawiera również (prawdopodobnie zaktualizowaną) poprawkę , a KB 4019893 został zaktualizowany w celu wyświetlenia dodatku SP1 CU4 jako wersji, w której naprawiono problem.

Niestety z własnego doświadczenia mogę potwierdzić, że nawet poprawka w dodatku SP1 CU4 nie rozwiązuje w pełni tego problemu. Obecnie mam jedną bazę danych z włączoną funkcją TDE, która wciąż tworzy konsekwentnie uszkodzone kopie zapasowe, nawet na SP1 CU4 podczas korzystania COMPRESSION(przez MAXTRANSFERSIZE> 64 KB) i CHECKSUM. Mam również kilkadziesiąt innych baz danych z włączonym TDE, które konsekwentnie nie tworzą uszkodzonych kopii zapasowych w tych ustawieniach, w tym jednej, która jest odmianą tej, która ma taką kopię, z prawie identycznym schematem, ale mniejszym zestawem danych. To wydaje się wskazywać, że Microsoft rzeczywiście odprawia scenariusze, które mogą to powodować, ale nie rozwiązał jeszcze wszystkich z nich.

Nie próbowałem jeszcze FORMATobejść tego problemu, jak wspomniano w innej odpowiedzi i poście na blogu SQLCAT , ale przedstawię tutaj aktualizację, jeśli będę w stanie spróbować i to rozwiąże problem. Jedna baza danych, którą to odtwarzam, jest niestety dość duża (~ 1 TB) i znajduje się w klastrze programistycznym / QA, który nie ma dużo dostępnej przestrzeni dyskowej (przynajmniej w tej skali), więc testowanie jej odmian ma okazało się logistycznie trudne i czasochłonne.