Importing a database into SQL Elastic Pool is taking longer than expected

PS 396

Hi All,

I am currently working on a POC and attempting to import a 300mb DB into SQL Elastic Pools. While the process initially started fine, it has been over 14 hours and it's still not complete. Can anyone provide any insights into why it may be taking this long or suggest a way to identify the root cause of the issue?

Thank you in advance.

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-08-25T19:35:07.4266667+00:00

PS Thank you for reaching out.

Are you trying to import using bacpac file or sqlpackage?

Regards,

Oury
PS 396 Reputation points

2023-08-25T20:24:03.87+00:00

Thank you for responding.

I have used bacpac through "import database". Also, checked the "Use private link (preview)" option while setting up.
Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-08-25T23:55:27.54+00:00

PS

I tried to reproduce the same from my end but taking long time also. I am checking internally what could be the cause. I will reach out when I find out the root cause of the issue.

Thank you for your patience.

Regards,

Oury
PS 396 Reputation points

2023-08-26T03:04:32.83+00:00

Thank you @Oury Ba-MSFT !

1 answer

Rahul Randive 9,176 Reputation points Microsoft Employee

2023-08-26T02:59:26.45+00:00

Hi PS •

To make the process relatively faster, you can import the database first and then add it to the elastic pool if you want to manage the resources and performance of the database more effectively.

Additionally, while importing the database, you can select a relatively higher pricing tier to get more compute. Once the database is imported, you can downscale it.

To answer your other question, what causes delays in the process?

The Azure SQL Database Import/Export service provides a limited number of compute virtual machines (VMs) per region to process import and export operations. The compute VMs are 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 can occur in processing the operations. The time that's required to complete requests can vary from a few seconds to many hours.

Hope this helps!

Thank you
Please sign in to rate this answer.
PS 396 Reputation points

2023-08-26T03:20:33.5633333+00:00

Thank you @Rahul Randive for your input.

Yes, I tried with the default.

now, I am trying w/double the cores.

hope it goes well. will update the outcome here.

Thank you!

PS 396 Reputation points

2023-08-26T20:48:19+00:00

It's been over 17hrs, and the import is still underway with the increased configuration.

It would have been nice if we can actually check what's causing this delay.

Thank you!

PS 396 Reputation points

2023-08-28T15:33:41.8566667+00:00

this is what I found.

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import-export-hang?view=azuresql&source=recommendations

to add some context: We are planning to move a 500gb DB from SMI to SQL Elastic Pools for better performance and what's the ideal way to achieve this?

Thank you!

Rahul Randive 9,176 Reputation points Microsoft Employee

2023-08-28T17:24:25.53+00:00

Hi PS •

We recommend the use of the SQLPackage utility for scale and performance in most production environments.

Secondly as suggested earlier, you should import the database first and then add it to the elastic pool if you want to manage the resources and performance of the database more effectively.

FYI- If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage with SqlPackage.

Here is a detailed blog- "Using SQLPackage to import or export SQL Server and Azure SQL DB"

PS 396 Reputation points

2023-08-28T18:29:32.3666667+00:00

Thank you @Rahul Randive for the input.

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-08-28T19:20:32.9333333+00:00

PS

@Rahul Randive has provided some good advice.

The only other trick here is related to private link. I will need to check by disabling the private link to see if the performance of the import will be any better.

Also note that using the import / export service with private link enabled is currently in preview. There are also some very specific limitations like if there are resource locks on either the DB or the storage account.

What does it mean when a feature is in preview?

When a feature is in preview, it means that we're actively seeking feedback on how the feature is working for you. A feature in preview is functionally complete, but it's possible that we'll make a breaking change in response to customer feedback. We recommend that you don't depend on a feature that is in preview in your production environment.

Regards,

Oury

Rahul Randive 9,176 Reputation points Microsoft Employee

2023-08-28T19:36:49.7433333+00:00

Hi PS •

You are most welcome 😊, hope we are able to share an information which you were looking for.

All the very best for your import operation. 👍

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-08-29T18:47:06.8466667+00:00

PS

Importing the same without private endpoint also is slow from my end.

I would suggest creating a support ticket because this require further investigation. Please do let us know if you don't have a support ticket.

Regards,

Oury

PS 396 Reputation points

2023-08-30T00:42:45.34+00:00

@Oury Ba-MSFT , agreed. from the link I attached in one of the earlier comments...the better way to achieve this is to use our own compute rather than relying on Azure VM's from the region. So, either use sqlpackage.exe or ssms import would work fine. However, when I tried sqlpackage.exe I got the following error while everything looks to be correct. trying to troubleshoot what could have caused it.

*** Error importing database:Could not import package. Unable to connect to target server 'tcp:azr-***-***-***-sqdb.database.windows.net,1433'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server. Login failed for user 'azsqladmin'.

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-08-31T04:45:44.0433333+00:00

This looks correct to me.

import database to default target service tier (Gen5 General Purpose with 2 vCore):
sqlpackage.exe /Action:Import /tsn:tcp:<ServerName>.database.windows.net,1433 /tdn:<TargetDatabaseName> /tu:<UserName> /tp:<Password> /sf:<Path to bacpac file>

Could you please add IP firewall rule under networking on the server then try again.

Note that for large databases import its recommended setting the service tier during the import, this help import process to run faster.

Example: import database and set target service tier to P2

sqlpackage.exe /Action:Import /tsn:tcp:MyServer.database.windows.net,1433 /tdn:AdventureWorks /tu:AdminUser /tp:AdminPassword1 /sf:C:\temp\AW.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P2

Reference: https://techcommunity.microsoft.com/t5/azure-database-support-blog/using-sqlpackage-to-import-or-export-sql-server-and-azure-sql-db/ba-p/368861

Regards,

Oury

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-08-31T04:56:35.54+00:00

PS can you try running this on the bash

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver16

The SqlPackage Import action imports the schema and table data from a BACPAC file (.bacpac) into a new or empty database in SQL Server or Azure SQL Database. At the time of the import operation to an existing database the target database cannot contain any user-defined schema objects. Alternatively, a new database can be created by the import action when the authenticated user has create database permissions.

Permissions

Requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

To maintain control over disk use on an instance of SQL Server, permission to create databases is typically limited to a few login accounts.

The following example provides the permission to create a database to the database user Fay.

USE master; GO GRANT CREATE DATABASE TO [Fay];

Note

SqlPackage import performs best for databases under 200GB. For larger databases, you may want to optimize the operation using properties available in this article and tips in Troubleshooting with SqlPackage.

Please let me know if that helps.

Regards,

Oury

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-09-01T17:28:50.54+00:00

PS

Any updates on this. Checking if you were able to import the database.

Regards,

Oury

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-09-03T22:54:42.38+00:00

PS

I wanted to check if you are still facing the same issue.

Regards,

Oury

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2023-09-05T18:03:55.7366667+00:00

PS

Did the above suggestion helped resolve your issue.?

Regards,

Oury

PS 396 Reputation points

2023-09-06T01:09:46.3+00:00

Thank you @Oury Ba-MSFT for follow up. I will try your suggestion and post an update sometime soon.
Sign in to comment

Share via

Importing a database into SQL Elastic Pool is taking longer than expected

1 answer