How to prevent Page corruption scenarios while restoring SQL DB backup from On-prem to Azure SQL MI

[Guest Tanayankar_Chakraborty]

Issue

 

While trying to restore the backup of an On-prem DB (That was moved from 1 server to another & Contained memory optimized table) on a managed instance, there could be a possibility of DB corruption if proper precautions are not taken.

 

 

 

Error

 

 

 

 

The impacted DBs might appear in Restoring state when we login to SSMS as shown below:

 

 

 

 

If the application is connected to the DB previously, a connection refresh throws the error message shown below:

 

 

 

Msg 64, Level 20, State 0, Line 2

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

 

Completion time: 2023-02-13T18:08:14.1270625+04:00

 

 

 

Workaround/Fix

 

It appears that page corruption happened during the DB restore process that resulted in boot page config failures. As a result, SQL was restarted again and again till the restore requests were cancelled from the back end. While at this stage, one needs assistance from Microsoft support, this can be prevented by the users if they follow a simple process/precaution (Verifying before & after the DB restore) during such on-prem to cloud restore operations.

 

 

 

While doing restore of a backup to an Azure SQL Managed Instance, in-memory OLTP engine asserts during deployment due to an invalid SQL boot page. The backup does not have in-memory OLTP deployed but has invalid in-memory entries in the SQL boot page including non-null in-memory OLTP LSNs. Please restore from a valid backup. If the source database has the inconsistency, it has a corrupted Hekaton (Also Known as In-Memory OLTP) state.

 

To fix/avoid this,

 

1. - create a fresh database
   
2. - verify the Hekaton fields in the bootpage are zeroed out
   
3. - migrate data to new database
   
4. - verify the Hekaton fields in the bootpage are zeroed out
   
5. - take a full backup.
   
6. - restore to SQL MI from this backup"
   

 

 

 

Steps 2 & 4 above can be carried out by doing the preventive analysis below:

 

The DB owner performing the restore will need to run the undocumented command

 

“DBCC DBINFO(<database name>) WITH TABLERESULTS”

 

 

 

On the fresh database before and after migration to get the Hekaton metadata stored in the SQL bootpage. It is an optional check.

 

 

 

The fresh database will not have Hekaton deployed unless db owner creates an in-memory table in the database.

 

 

 

The output of the above DBCC command will have following Hekaton fields zeroed out:

 

dbi_hkRecoveryLSN

 

dbi_hkLogTruncationLSN

 

dbi_hkCompatibilityMode

 

dbi_hkRootFile

 

dbi_hkRootFileWatermark

 

dbi_hkTrimLSN

 

Continue reading...