locked
Migration Strategy: Restoring a SQL Server 2016 backup from an on-prem data warehouse to Azure Data Warehouse. RRS feed

  • Question

  • I have a 200GB Sql Server backup file given to me by a vendor - (really 50 backup files of 4GB each, not sure why they did it this way).  I want to restore the backup files to an existing Azure Data Warehouse (now synapse analytics as I understand it).  I did some searches here on MSDN and most were about using ADF (Data factory) or polybase or ssrs or something from an existing db.  Is this really how it is done?  Can't I just somehow move the backup files to somewhere like blob storage or datalake and restore the backup there or do I have to restore it locally on-prem?

    The version of SQL Server from the backup is :  Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) 

    My search terms I used for researching

    https://social.msdn.microsoft.com/Forums/en-US/home?category=&forum=AzureSQLDataWarehouse&filter=&sort=relevancedesc&brandIgnore=true&searchTerm=migrate+to+warehouse

    Tuesday, December 3, 2019 3:13 PM

Answers

  • You cannot restore a regular database backup to Azure Data Warehouse as they are entirely services with a different architecture underneath. You can however restore your sql server backups to a Azure SQL DB and then do a migration to Azure DW from there as you will want to rebuild the structure of your data to be able to take full advantage of the capabilities that Azure DW has.

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-import?tabs=azure-powershell

    Thursday, December 5, 2019 7:19 AM

All replies

  • Since the number of files is high, any error/timeout during the restore process from On-Prem might force you to start over again.

    A good option would be to copy the files to Azure. Do you have an Express Route connection to Azure?

    Here is some guidance on choosing a solution for data transfer.


    Wednesday, December 4, 2019 2:55 PM
  • I do have an express route.  My question is more on how one would go about this.  For example, can I go into the master db in Azure Data Warehouse and restore a db that is a backup from and 2016 SQL Server box?  If so is there a link to the syntax?  If this is possible should I copy the backup files to a data lake or blob storage?
    Wednesday, December 4, 2019 8:16 PM
  • You cannot restore a regular database backup to Azure Data Warehouse as they are entirely services with a different architecture underneath. You can however restore your sql server backups to a Azure SQL DB and then do a migration to Azure DW from there as you will want to rebuild the structure of your data to be able to take full advantage of the capabilities that Azure DW has.

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-import?tabs=azure-powershell

    Thursday, December 5, 2019 7:19 AM
  • Hi Daniel, this is very helpful, thanks.

    As a followup question, what would you see as the most efficient way ( as far as time it takes to transfer data to Azure DW - my final destination) to get the data to an Azure Data Warehouse?

    Data Factory after restoring the backup to a Sql Server DB on Prem with an Express Route?

    Using the referenced method of a bacpac file the somehow migrating from Azure SQL DB to Azure DW?

    Some other method?

    Thanks.

    Thursday, December 5, 2019 2:33 PM