Time taken for BACPAC restore in Azure

1
votes

I initiated the restoration of the BACPAC file and checked the status after 24 hours (the DB size is around 110GB). It was completed as expected but I am not sure how long it actually took to complete the restore as the SSSMS Import Data-tier Application Wizard hadn't displayed the time statistics.

1) I am trying to work out how long the restore had taken, just for our future reference. Is there a way to work out this?

2) Does the Azure Pricing Tier (P1, S1 etc) make any difference in the restoration time? I would like the Database to be on the lowest pricing tier, but not too sure if it will affect the restore time.

3) Is there any kind of statistics available in terms of how long the restore would take for each different pricing tier (let's say 100GB Database)?

PS: I have checked the Database created date, but it is one of the first steps in the process (i.e. the database was created just after a few minutes).

2
azure-sql-database

2 Answers

1
votes
  1. You can examine start\stop time from the portal, for example, or from powershell (New-AzureRmSqlDatabaseImport).
  2. Yes, more performance - faster restores
  3. No.

You can scale up before starting the restore, that will definitely help. But this operation got no SLA on it, so support wont help you speed it up or something.

1
votes

Let's say you used a PowerShell like below to import the bacpac from a storage account:

$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

You can track the progress using Get-AzureRmSqlDatabaseImportExportStatus.

$import | Get-AzureRmSqlDatabaseImportExportStatus

For better performance always use premium tiers when migrating or importing databases to Azure SQL Database, after the operation finishes you can scale down the tier to the intended service level.

I don't have knowledge of any statistics about approximate throughput to expect depending of the service level of the Azure SQL Database, but all of them have limits in terms of IOPS. Premium tiers show the best I/O performance.