Database migrations to Azure SQL Managed Instance - Restore with Full and Differential backups

This blog will guide organizations to help accelerate migrations from on-premises, IaaS and/or relevant SQL Server implementation that need to be migrated to an Azure SQL Managed Instance, with little downtime, and having full and differential backups as the only options from source (e.g. no log backup capabilities).

Disclaimer: If log backups are available, it is strongly recommended to use Azure Database Migration Service instead.

Below are the SQL Server Migration Scenarios and Requirements Covered in this blog: 

• There is little tolerance for downtime for the migration cutover (e.g. less than 1 hour).
• There is no log backup option available from the source.
• There is no possibility to use Azure Data Migration Service.
• Source can backup with these options:
  • Full Backup with 1 or to multiple files (recommended)
  • Differential backup to 1 file (mandatory)
  • Compression can be enabled (recommended)
  • Checksum enabled (mandatory)
• Source is SQL Server 2005 or later.

Applications and tools required:

• Access to source database as backup operator or administrator, to execute full backup and differential backup commands.
• Administrative access to the target Azure SQL Managed Instance from a SQL interface tool like SSMS (SQL Server Management Studio).
• Access to the Azure resource portal that allows users to access the target Azure SQL Managed Instance.
• Access to the Azure Blob Storage container to create SAS keys to
  • Write the backup files.
  • Read and List the backup files (these 2 options are the only ones that must be enabled for the web portal restore to work properly).
• Access to download and Install Advanced REST client
• Access to download and Install AzCopy
  • Windows 64-bit (zip)
  • Windows 32-bit (zip)
  • Linux (tar)
  • MacOS (zip)

Planning the migration: 

Recommendation is to plan the migration, to ensure that there is as little data as possible which is within the acceptable limits for the differential backup file creation. It is also recommended to proceed when the database is not been intensively used for writes.

High Level Migration Process:

1. Run a full backup on source into multiple files (e.g. 10), with compression (recommended) and checksum (mandatory).
2. Copy the full backup files from source to an Azure Blob Storage Container with AzCopy.
3. Initiate the Restore via web portal with access to your subscription.
4. Obtain the Authorization from the Request Preview after running the restore from portal.
5. Monitor the restore process on the Azure SQL DB Managed instance target with SSMS (or tool of preference).
6. Obtain the session_activity_id from the restore process on the Azure SQL Managed Instance with SSMS (or tool of preference).
7. Once determined that the full backup restore is about to finalize, stop write connectivity to the source database.
8. Run a differential backup on source into 1 file with compression (recommended) and checksum (mandatory).
9. Transfer the differential backup files from source to the same Azure Blob Storage container used to store the full backup.
10. Execute the cutover with the Advanced REST client.

Initiate the Migration:

• Run the full backup (sample command below):

Below are a few optional backup tuning considerations options to further tune backup speed:

1. Using MAXTRANSFERSIZE is needed to compress TDE enabled databases (SQL 2016 CU7 / SP1 CU4 / SP2)
2. We have seen a 4MB (4194304) MAXTRANSFERSIZE to give best performance in various engagements.
3. BUFFERCOUNT requires more precise tuning. A quick section on TF3213 (for Backup/Restore info) and TF3605 to output this information to the error log would be a good idea to optimize backup.
4. Watch out for 512byte aligned disk I/O on source and the impact this has on the restore speed. It may be needed in advance to set TF1800 on the source on-prem SQL Server in order to get around potential issues

• Copy the backup files to Azure Blob
  Assumption: The SAS key for writing files is already available (Sample Command below):

• Initiate the restore
  1. Access the link https://docs.microsoft.com/en-us/rest/api/sql/manageddatabases/createorupdate
  2. Click on 
  3. Sign in and choose the appropriate account.
  4. Fill in (ensure to keep the api-version as below): 
  5. Paste into the Body (SAS Key below should just have only list and read capabilities from container): Location: e.g. West US 2     

     { "properties": { "createMode": "RestoreExternalBackup", "storageContainerUri": "https://account.blob.core.windows.net/container", "storageContainerSasToken": "SASKey", "collation": "SQL_Latin1_General_CP1_CI_AS" }, "location": "RegionOfTheManagedInstance" }

  6. Click on 
  7. Expect 
  8. If Response is different, troubleshoot and retry.
  9. Copy and keep the Authorization portion of the Request Preview; e.g. Bearerverylonstringverylonstringverylonstringverylonstring 
  10. Monitor the status on the Managed Instance with a tool like SSMS. 

      SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

  11. Once completed (e.g. no output from above query), obtain the session_activity_id of the pending restore (e.g. EAB63459-9AF0-4433-1BFF-CCE A70B67BC1). 

      SELECT [session_activity_id] as operationId ,[resource_type] ,[resource_type_desc] ,[major_resource_id] ,[minor_resource_id] ,[operation] ,[state] ,[state_desc] ,[percent_complete] ,[error_code] ,[error_desc] ,[error_severity] ,[error_state] ,[start_time] ,[last_modify_time] FROM [master].[sys].[dm_operation_status] where state_desc = 'IN_PROGRESS';

       

Initiate the Cutover Process

• Stop writing processes into the source database
  You can opt to stop the applications connecting to the source database, to ensure that no new transactions are applied after the differential backup has been executed.
• Run the Differential backup
  Sample command:

  BACKUP DATABASE [SAMPLE] TO DISK='C:\BACKUP\SAMPLEBackupDIF.bak' WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

• Copy the differential backup file to Azure Blob
  Assumption is that the SAS key for writing files is already available Sample command:
  

  azcopy cp "C:\BACKUP\ SAMPLEBackupDIF.bak" "https://account.blob.core.windows.net/container?SASKey"

• Cutover with Advanced REST Client
  1. Install Advanced REST Client and run it
  2. Build out the POST Request URL. Have these handy
     • Azure Subscription ID
     • The Location (region) of the Managed Instance
     • The session_activity_id obtained of the pending restore (e.g. EAB63459-9AF0-4433-1BFF-CCE A70B67BC1)
     • Sample below:

       https://management.azure.com/subscriptions/YourSubscriptionID/providers/Microsoft.Sql/locations/MILocation(e.g.westus2) /managedDatabaseRestoreAzureAsyncOperation/session_activity_id/completeRestore?api-version=2017-03-01-preview

  3. Choose Method: POST
  4. Request URL: Paste the above created
  5. Add in HEADERS Section (use value from Initiate the Restore section)
     o Header: Authorization
     o Parameter Value: Bearer verylonstringverylonstringverylonstringverylonstring
  6. Add in Headers Section
     o Header: Content-Type
     o Parameter Value: application/json
  7. Add in the BODY section
     

     { lastBackupName: "SAMPLEBackupDIF.bak" }

  8. Click SEND and expect a 202 Accepted
  9. Database should finalize restore and open.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.