none
Importing DB from bacpac - tracking completion %

    Întrebare

  • Currently I am importing a large DB from a bacpac file, I can't see anyway of tracking the completion % of this process and looking through the overview I can't see if it is actually processing the file. I can only see the submitting deployment message.

    Also on previous imports it has missed several tables worth of data - at this point I can't tell if this was a problem with the bacpac file (which I assumed is compressed since db size is around 100GB and bacpac file is 28GB) or the import from Azure Storage.

    Is there any additional tracking I am failing to find ?

    miercuri, 11 iulie 2018 16:28

Toate mesajele

  • Hello,

    Please run the following query to monitor this type of operations. Look for the percent_complete column.

    SELECT * FROM sys.dm_ operation_status    
       WHERE [state] = 1

       ORDER BY start_time DESC; 


    State =2 means completed.

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    miercuri, 11 iulie 2018 19:47
  • Ran the SQL - 0 results (can't upload images until account verified)

    There are no operations currently running however viewing from azure I receive the following:

    "Submitting deployment for ..." 

    which then changes to 

    "Deployment status unknown please upin"


    I am going to attempt another import.

    If you know any way to access any reason behind this failing then please let me know :)

    Thanks,

    Chris 

    joi, 12 iulie 2018 10:59
  • Unfortunately still no luck with importing this bacpac... however I did receive an error message on this run (on a new SQL server instance)

    The ImportExport operation with request Id xxx failed due to 'Canceling import job orphaned by the webrole.'

    Any help in fixing this would be greatly appreciated.

    Thanks,

    Chris

    joi, 12 iulie 2018 20:33
  • Hello,

    Let me try to find a workaround/solution for this issue.

    Thank you for your patience.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    joi, 12 iulie 2018 20:46
  • Hello,



    Just one question, are you importing that database from an on-premises instance?
     

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    joi, 12 iulie 2018 20:58
  • Sort of? I exported the bacpac from a on-premise db, but it is currently in Azure Blob Storage.

    I initially tried using the direct export from SSMS however that often timed out / OOM'ed on the export due to the somewhat large nature of the DB export.

    It was then recommended to export to bacpac upload to storage and process it from there.

    Thanks for assisting.

    Chris

    joi, 12 iulie 2018 21:34
  • Hello,

    To migrate your databases to Azure SQL Database, I recommend you to use Data Migration Assistant. This tool makes the task really easy.

    https://blogs.msdn.microsoft.com/datamigration/2018/07/10/release-data-migration-assistant-dma-v3-6/?utm_source=dlvr.it&utm_medium=twitter


    If you don’t want to use the tool to migrate your database I would still recommend you to use the tool to evaluate or test the compatibility of the original database with Azure SQL Database. Let me know if you don’t find incompatibilities.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    joi, 12 iulie 2018 23:21
  • No incompatibilities found when running an assessment.

    There are however a few instances of deprecated data types e.g. TEXT IMAGE or NTEXT.

    Software has no suggested fixes though. 

    I can work on making these changes, would this be the cause?

    I will also try migrating using the assistant directly.

    Thanks,

    Chris

    vineri, 13 iulie 2018 08:22
  • Hello Chris,

    I would recommend the following workflow.

    1. Export using SqlPackage.

    SqlPackage /Action:Export /SourceServerName:<ServerName>,1433 /SourceDatabaseName:<DatabaseName> /TargetFile:"F:\Temp\DatabaseName.bacpac

    This will generate a compressed zip file with the .bacpac extension, you can rename extension to .zip and take a look of the file contents.

    2. Upload the file to the blob storage with your preferred method.

    3. Import the package with New-AzureRmSqlDatabaseImport.

    $import = New-AzureRmSqlDatabaseImport -ResourceGroupName "RG01" -ServerName "Server01" -DatabaseName "Database01" -StorageKeyType "StorageAccessKey" -StorageKey "StorageKey01" -StorageUri "http://account01.blob.core.contoso.net/bacpacs/database01.bacpac" -AdministratorLogin "User" -AdministratorLoginPassword $SecureString -Edition Standard -ServiceObjectiveName S0 -DatabaseMaxSizeBytes 5000000

    4. Track the import operation with Get-AzureRmSqlDatabaseImportExportStatus.

    $import | Get-AzureRmSqlDatabaseImportExportStatus


    sâmbătă, 14 iulie 2018 19:40
  • Hello,

    If you want to easily move your database to Azure SQL Database then you need to use the Data Migration Assistant.


    You can copy the database locally and try to solve those issues with deprecated features on the newly created copy of the database. Then try to import the database with the corrections made.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    în urmă cu 19 ore şi 58 minute