question

ChrisCapon-2260 avatar image
0 Votes"
ChrisCapon-2260 asked ChrisCapon-2260 edited

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

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.



azure-sql-databaseazure-virtual-network
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ChrisCapon-2260

Were you able to resolve your issue or need further help. Please do get back to us for any help or question.

Thanks
Navtej S

0 Votes 0 ·
NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered NavtejSaini-MSFT commented

@ChrisCapon-2260

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ChrisCapon-2260

Please let us know if you need any further help regarding this.

Thanks
Navtej S

0 Votes 0 ·
ChrisCapon-2260 avatar image
0 Votes"
ChrisCapon-2260 answered NavtejSaini-MSFT commented

@NavtejSaini-MSFT - 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)

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sigh Ok. I will submit a ticket for both issues. I really hate having to deal with Microsoft Support though. It always feels like I have to teach them how to use Azure.

1 Vote 1 ·

@ChrisCapon-2260

Create the tickets and share it with me and I will keep an eye on the same and will intervene if needed. In meantime will try to find answer to any other approach.

Thanks
Navtej S

0 Votes 0 ·

Thank you @navtejsaini-msft. I appreciate your time.

I have created two issues:
120093024004109
120093024004238

0 Votes 0 ·
Show more comments

@ChrisCapon-2260

We will certainly research from our end to find a method but for the config issue regarding your export, have you thot about raising a support issue. Currently do you have a support plan or already raised a ticket. Please convey the same regarding both the scenarios.

If you have raised a ticket please share the ticket number as well so we can check and expedite it if needed.

I appreciate your patience in this regard.

Thanks
Navtej S

0 Votes 0 ·
ChrisCapon-2260 avatar image
0 Votes"
ChrisCapon-2260 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered

@ChrisCapon-2260

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://docs.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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChrisCapon-2260 avatar image
0 Votes"
ChrisCapon-2260 answered ChrisCapon-2260 edited

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://docs.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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.