Database import very slow

Question

Database import very slow

s_osborne2

SSCertifiable

Points: 6726
More actions
October 23, 2018 at 3:09 am

#380325

Hi,
I have a ~300GB database to restore to Azure SQL Database. I have created the BACPAC and have started the import job but it seems to be running incredibly slowly with long periods of inactivity and low peak utilisation.
I started this import job at ~ 7.30am this morning. I did try to do this overnight last night, but the job failed after ~5 hours with this error:
Any advice you can offer is appreciated.
Thanks,
Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

Two things, migrate the BACPAC file to Azure storage first, then import it to the database. Also, make sure you're using an adequate service tier for the imports. It does look ok on the one graph you show, but it still could be an issue. Look to the disk i/o measures. Those are the ones that are likely to be the most used in an import situation.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 2

Thanks Grant. I actually raised a ticket with MS about this. They've responded as follows:

More Information:
This problem occurs when many customers make an import or export request at the same time in the same region.

The Azure SQL Database Import/Export Service provides a limited number of Compute virtual machines (VMs) per region to process the import and export operations. The Compute VM is hosted per region to make sure that the import or export avoids cross-region bandwidth delays and charges. If too many requests are made at the same time in the same region, significant delays occur in processing the operations. The time that is required to complete requests can vary from a few seconds to many hours. Kindly refer to the below link for more details:
https://support.microsoft.com/en-us/help/2965554/azure-sql-database-import-export-service-takes-a-long-time-to-import-o

The bacpac i'm importing is already in blob storage as you've suggested. The link that MS have provided has suggested using a utility called SQLPackage.exe which i'm busy investigating now. The issue i've got now is how can i get SQLPackage.exe to read the bacpac from my blob storage account. The alternative I have is to download the bacpac to a VM i'm using to host SSIS anyway and run SQLPackage from there.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Ah, if you're already using Azure storage, then it clearly is all a Microsoft issue. Wonder if this counts as a noisy neighbor situation?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 4

Right, so following the workaround in the article sent to me via the support ticket this led me to this resource: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-import. Following the instructions on there I have been able to restore my bacpac with no gaps in activity on the database. The only catch being i need to be able to reference the bacpac from a local resource (so having it an Blob storage was a no go). I downloaded the bacpac to the VM, but in theory i could have used Azure Files because that's addressable using and SMB3.0 style share.

On a side note, I'm now seeing slow response times to a Azure database scale request. I'm trying to go from P4 to P1, 2 hours of waiting and it's still scaling....

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

BLOB EATER SSChampion Points: 14609 More actions · Answer 5

s_osborne2 - Friday, October 26, 2018 7:08 AM
Right, so following the workaround in the article sent to me via the support ticket this led me to this resource: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-import. Following the instructions on there I have been able to restore my bacpac with no gaps in activity on the database. The only catch being i need to be able to reference the bacpac from a local resource (so having it an Blob storage was a no go). I downloaded the bacpac to the VM, but in theory i could have used Azure Files because that's addressable using and SMB3.0 style share.
On a side note, I'm now seeing slow response times to a Azure database scale request. I'm trying to go from P4 to P1, 2 hours of waiting and it's still scaling....

I have a scale down operation that has been running for 5.5 hours now...

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 6

Mine has now finished, i accidentally closed my browser. After i'd realized what I'd done i logged back into the portal to find it has infact completed. This, after i raised a ticket with MS too... :hehe:

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

BLOB EATER SSChampion Points: 14609 More actions · Answer 7

I am using sys.dm_operation_status - stuck at 27% for 5.5 hours. Very strange 🙂