Are there any special considerations for a log-shipping role change when the database has memory optimzed files?

Posted on

Question :

We’re noticing a problem with our log-shipping setups when the database contains a memory-optimized file. Specifically, after changing the primary/secondary roles, restoring log backups taken after the role change result in errors:

Msg 41804, Level 16, State 0, Line 12
Internal error for database 'test' (lookup for HkTruncationLsn failed). The operation will be retried. No user action is required. If the problem persists, contact customer support.
Msg 3013, Level 16, State 1, Line 12
RESTORE LOG is terminating abnormally.

The following is a script that reproduces the error for us:

drop database test;
drop database test2;
create database test;
alter database test add filegroup test_mod contains memory_optimized_data;
alter database test add file (name = 'test_mod', filename = 'c:tmptest_mod') to filegroup test_mod;
backup database test to disk = 'c:tmptest_full.bak' with format, init, stats = 1;
restore database test2 from disk = 'c:tmptest_full.bak' with move 'test' to 'c:tmptest2.mdf', move 'test_log' to 'c:tmptest2_log.ldf', move 'test_mod' to 'c:tmptest2_mod', norecovery, stats = 1;
backup log test to disk = 'c:tmptest_log.bak' with format, init, norecovery, stats = 1;
restore log test2 from disk = 'c:tmptest_log.bak' with recovery, stats = 1;
backup log test2 to disk = 'c:tmptest2_log.bak' with format, init, stats = 1;
restore log test from disk = 'c:tmptest2_log.bak' with norecovery, stats = 1;

We’ve seen this on SQL Server for Linux 2017 and 2019 and also SQL Server for Windows 2019.

Are we missing some step? Is this a known problem?

Answer :

This looks like a bug. The log shipping failover works, but reversing the log shipping does not. So consider opening a support case.

But you can work around it by using a full backup to restart log shipping in the
reverse direction. eg:

use master 
drop database test1;
drop database test2;
go

create database test1;
alter database test1 add filegroup test_mod contains memory_optimized_data;
alter database test1 add file (name = 'test_mod', filename = 'c:tmptest1test1_mod') to filegroup test_mod;
backup database test1 to disk = 'c:tmpbaktest1_full.bak' with format, init, stats = 1;

restore database test2 from disk = 'c:tmpbaktest1_full.bak' 
   with move 'test1' to 'c:tmptest2test2.mdf', 
   move 'test1_log' to 'c:tmptest2test2_log.ldf', 
   move 'test_mod' to 'c:tmptest2test2_mod', norecovery, stats = 1;

--tail log backup
backup log test1 to disk = 'c:tmpbaktest1_log.bak' with format, init, norecovery, stats = 1;

--log shipping role switch
restore log test2 from disk = 'c:tmpbaktest1_log.bak' with recovery, stats = 1;
go
--reverse log shipping, reinitializing the secondary from a full backup
backup database test2 to disk = 'c:tmpbaktest2.bak' with format, init, stats = 1;
restore database test1 from disk = 'c:tmpbaktest2.bak' with norecovery, stats = 1;

go
--generate some log records
create table test2.dbo.foo(id int)
insert into test2.dbo.foo(id) select object_id from sys.objects 
go
backup log test2 to disk = 'c:tmpbaktest2_log.bak' with format, init, stats = 1;
restore log test1 from disk = 'c:tmpbaktest2_log.bak' with norecovery, stats = 1;

Leave a Reply

Your email address will not be published.