Export database fails with "The gateway did not receive a response from 'Microsoft.Sql'"

Question

Using the Azure portal, I am exporting an Azure SQL Database to an Azure Storage Account Container and receive the following error message:

Failed to export the database: sqldb-MyDatabase-145.
ErrorCode: 504
ErrorMessage: The gateway did not receive a response from 'Microsoft.Sql' within the specified time period.

Does anyone know what this error message means? I get it every time I export.

Details:

1. Both the Azure SQL Server and Azure Storage Container are located in Canada Central.
2. The database is configured as a Serverless Gen5 with auto-pause set to 1 hour.
3. The SQL Server and Storage Container are on separate virtual networks.
4. The firewall for the SQL Server is set to:
   Allow Azure services and resources to access this server: Yes
   Virtual networks list shows both virtual networks with Endpoint status "Enabled".
5. The firewall for the Storage account is set to:
   Allow access from: Selected networks
   Virtual networks list shows both virtual networks with Endpoint Status "Enabled".
   Exceptions: enabled is "Allow trusted Microsoft services to access this storage account"
6. Both virtual networks are configured with:
   only a single "default" subnet.
   No firewall exists (the default)
   Service endpoints: Microsoft.Sql - status "Succeeded", Microsoft.Storage - status "Succeeded"

I've tried various combinations of the above options with no success.
If you know what the error message means, I'd appreciate the feedback.
Thanks.

Answer 1

@Chris Capon

If you need import/ export the Azure SQL database, please consider using the SSMS or SqlPackage instead of the Azure portal to reduce the waiting time. Because 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. Here is the online document for your reference.

What causes delays in the process?

Please download the .bacpac file to local computer and try to use the SSMS or SqlPackage to import the .bacpac file.

Using SqlPackage

SSMS: Import a BACPAC File to Create a New User Database

Here is the download link for the SSMS

Hope this helps. And let us know if you have any further issue.

Regards
Navtej S

Answer 2

@Navtej Singh Saini - Thanks for the reply. It doesn't answer my question though.

Let me explain:

The problem is, we have several large client databases in Azure that see infrequent use over a month. To save costs, they are configured as Serverless with Auto-Pause Enabled. This minimises up-time, but the database then has a maximum point-in-time recovery of 35 days. This is inadequate for our needs. Long-Term retention is not allowed for databases that are Serverless with Auto-Pause.

So, is there some other way to automate the backups of an Azure SQL Database to some sort of Azure storage?

Export functionality either works or it doesn't. In my case, it doesn't. Exports always fail after a 2 minute delay and the job never shows up on the server's Import/Export History. It doesn't matter what time of day I launch the job, nor if it's run from the web Portal or a PowerShell script, both fail identically. So, I imagine there is something wrong with my configuration - but the error message does not give me enough info to figure it out.

I've thought about using SSMS and SqlPackage as you suggest.

However, if I use either of those tools, then the 100 GB database will be transferred directly down to my local computer over the internet, then uploaded to the Azure Storage Blob again through the internet - an oddly vulnerable way to do an Azure-to-Azure backup. (or I can install the tools on a VM but it seems strange to incur the cost of a VM just to run Azure database backups).

Perhaps you have some other ideas for me?

(sorry for being ignorant - thank you for reaching out and helping)

Answer 3

Issue # 120093024004238 has concluded with the following statement (my words):

There is no practical way to Export an Azure SQL database to Azure Storage without enabling firewall access to the entire internet.

Answer 4

@Chris Capon

I can understand your frustration in this regard and I do want to mention that Microsoft is working to remove these barriers for customers to use Azure. I also thought to post the answer from support here so that others can get the help from this thread raised by you.

"Based on current available feature options, alternate solution for the safe way to back up an Azure SQL Database (configured as Serverless with Auto-Pause enabled) to Azure storage, in a way such that security is not also compromised is using SqlPackage on Azure VM as documented here: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import-export-azure-services-off. Otherwise, if possible you can make a new storage account and only use it for the export result, and then copy the file from there to your more secure storage account. In addition, I like to inform you that our engineering team is working on a private link feature which will enable security concern and it's coming very soon.
I can totally understand your frustration and concern on this issue. We at Microsoft, we do appreciate you as a customer and we understand that you have tied the success of your product to the success of Azure platform. This is not something we take lightly and we take every such incident seriously and seek avenues to improve our service(s) for our customers. Please find our feedback link related to security concern: https://feedback.azure.com/forums/217321-sql-database/suggestions/41442409-sql-export-to-support-storage-account-with-firewal and kindly provide your valuable inputs and reviews. Our company strives to improve our services based on these reviews. This is a public platform for our customers where they can share their ideas or suggestions with product team.
In addition, as we understand your business and concerns, this is not something we take lightly and we take every review or concern seriously and seek avenues to improve our service(s) for our customers. As I had forwarded earlier the feedback link regarding your ask/concern for LTR feature for Serverless with autopause enabled: https://feedback.azure.com/forums/217321-sql-database and once again, I kindly request you to provide your valuable inputs and reviews as our company strives to improve our services based on these reviews."

Also I want to mention the option provided by other ticket: "To try spinning up a VM and use that for export, you will need to open the storage account firewall only to that VM"

Please do try and provide your experience regarding the same.

Thanks
Navtej S

Answer 5

Issue # 120093024004109 has concluded with the following statement (also my words):

The only way to make long-term backups of Azure SQL Databases configured as Serverless with Auto-Pause Enabled is by using the Export process.

(The SqlPackage.exe is essentially a tool for running the Export process locally from within a VM - in other words, to do an Export, launch a VM)

For those who follow, I have raised the following issues with using Export as a backup process:

1. The database can not actively be in-use while an Export takes place. (ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export)
2. Export requires a full copy of the database to be made every time. This takes longer to run, puts a heavier load on the network, and is more costly in terms of storage.

Currently, it is possible to configure automated Long-Term-Retention for databases that run 24/7, but there is no way for users to trigger LTR backups on demand. This is preventing Auto-Pause databases from being properly backed up. A better solution would be if users could also launch actual Long-Term-Retention backups at a time of their choosing. Users could then script backups to launch at appropriate times for intermittent databases.