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



  • 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:\tmp\test_mod') to filegroup test_mod;
    backup database test to disk = 'c:\tmp\test_full.bak' with format, init, stats = 1;
    restore database test2 from disk = 'c:\tmp\test_full.bak' with move 'test' to 'c:\tmp\test2.mdf', move 'test_log' to 'c:\tmp\test2_log.ldf', move 'test_mod' to 'c:\tmp\test2_mod', norecovery, stats = 1;
    backup log test to disk = 'c:\tmp\test_log.bak' with format, init, norecovery, stats = 1;
    restore log test2 from disk = 'c:\tmp\test_log.bak' with recovery, stats = 1;
    backup log test2 to disk = 'c:\tmp\test2_log.bak' with format, init, stats = 1;
    restore log test from disk = 'c:\tmp\test2_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?



  • 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:\tmp\test1\test1_mod') to filegroup test_mod;
    backup database test1 to disk = 'c:\tmp\bak\test1_full.bak' with format, init, stats = 1;

    restore database test2 from disk = 'c:\tmp\bak\test1_full.bak'
    with move 'test1' to 'c:\tmp\test2\test2.mdf',
    move 'test1_log' to 'c:\tmp\test2\test2_log.ldf',
    move 'test_mod' to 'c:\tmp\test2\test2_mod', norecovery, stats = 1;

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

    --log shipping role switch
    restore log test2 from disk = 'c:\tmp\bak\test1_log.bak' with recovery, stats = 1;
    go
    --reverse log shipping, reinitializing the secondary from a full backup
    backup database test2 to disk = 'c:\tmp\bak\test2.bak' with format, init, stats = 1;
    restore database test1 from disk = 'c:\tmp\bak\test2.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:\tmp\bak\test2_log.bak' with format, init, stats = 1;
    restore log test1 from disk = 'c:\tmp\bak\test2_log.bak' with norecovery, stats = 1;




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2