How do you check the status of a SQL Import Export Operation in Azure?

Question

I am performing a SQL Database Import using a BacPac file to Azure using the REST management API. I need to be able to check the status of an import, as Azure does not allow for me to send events when the import succeeds.

Here is the documentation for the operation I am performing: https://learn.microsoft.com/en-us/rest/api/sql/databases%20-%20import%20export/import

I have already tried using the Azure's Analysis Services API to check an operation's status by the operation id and the region it occurred in. Calling this endpoint however is returning me a 404 not found.

Here is the documentation for Azure's Analysis Services API endpoint I am attempting to call with my import's operation id and the region it occurred in: https://learn.microsoft.com/en-us/rest/api/analysisservices/servers/listoperationresults

I am expecting the Analysis Services to provide me with the status of an import/export operation I started using the Azure Management API. Doing so is resulting in a 404, so I'm not sure if I'm calling the correct API endpoint (I double checked to make sure there are no typos in the URI I am building to check the operation status).

Muth

Just as a side note, I am aware that a similar answer was posted for the following question; however, it does not provide a solution to this issue: #46346509 – Muth

did you manage to get the status without powershell? – Prelate

No, I opted to write a PowerShell Core based function app, which I then used to incrementally check for the status. During this time, I realized that I could not differentiate between success or failed statuses, as instances which failed to stand up also provided a 404 (logically, as they did not exist). One approach you could take in retrospect would be a logarithmic fallback, that way you progressively back off your checks. Your needs are highly dependent on your use case of course, and in my case this was directly consumed by customers. – Muth

Answer 1

I do know how to track the progress of an import/export operation if you use PowerShell instead but I don't know a way to track the progress using Azure REST API.

If you use PowerShell New-AzureRmSqlDatabaseImport cmdlet to import a database or you use New-Azure​RmSql​Database​Export to export a database then you can use Get-AzureRmSqlDatabaseImportExportStatus to track the progress of the import/export operation.

Driver

My project requires that I be able to import databases to Azure SQL via the REST API, as well as check the status of an import through the API as well. I am currently working with Microsoft on finding a solution, and they did offer the powershell approach; however, like I mentioned, this unfortunately wont work for my use case. – Muth

One way you can consume PowerShell in this case without directly executing the commands from a web app is by creating an Azure Function app with PoweShell Core. Simply write functions to wrap PowerShell commands you want to consume, and call them with an Http client. – Muth

@Alberto - AzureRmSqlDatabaseImportExportStatus, and it's current version Az.Sql.Get-AzSqlDatabaseImportExportStatus both expect an OperationStatusLink parameter which is typically returned by the cmdlet that initiates the export. How do you get the OperationStatusLink for an export which was initiated by let's say the Azure portal, and you want to check the status? – Sheepfold

Answer 2

in the database go to overview -> in the bottom check notifications -> you do make click in tha image

Kowatch

The question specifies that I am trying to obtain the status via the Azure ARM REST API. – Muth

Answer 3

For any api such as BeginX(), there is a corresponding api X() which waits for completion. In this case, instead of BeginImport() use Import().

If you wish to have more direct control over the polling, then you can look inside the definition of Import and directly use the lower layer, i.e. BeginImportWithHttpMessagesAsync() and then GetPostOrDeleteOperationResultAsync():

        public async Task<AzureOperationResponse<ImportExportResponse>> ImportWithHttpMessagesAsync(string resourceGroupName, string serverName, string databaseName, ExportRequest parameters, Dictionary<string, List<string>> customHeaders = null, CancellationToken cancellationToken = default(CancellationToken))
        {
            // Send request
            AzureOperationResponse<ImportExportResponse> _response = await BeginImportWithHttpMessagesAsync(resourceGroupName, serverName, databaseName, parameters, customHeaders, cancellationToken).ConfigureAwait(false);

            // Poll for completion
            return await Client.GetPostOrDeleteOperationResultAsync(_response, customHeaders, cancellationToken).ConfigureAwait(false);
        }

This answer is specifically for .net but for other languages the same principle applies.

Cyclorama

I am using PHP, and Microsoft does not provide a client library for this language. Is there a way to do this through the REST API? – Muth

Yes, the code that I am referring to is just a layer above the REST API. The POST response will contain Location header which specified the operation status URI. You can GET that URI to check the progress. The URI that Location header returns is deliberately not documented in Swagger because it should be opaque to clients, i.e. clients should have no expectation of what the path should be. – Cyclorama

Unfortunately I found out that while it is possible to check the status, you can't differentiate between the status of 'succeeded' and 'failed', as the import operation status URI contains the name of the database being imported in its path. Which means if the import fails, the URI will return a 404 (because the database would have never been created, therefore the path does not point to a real resource). – Muth

Answer 4

To monitor import's progress, open the database's server page, and, under Settings, select Import/Export history. When successful, the import has a Completed status.

https://learn.microsoft.com/bs-latn-ba/azure/sql-database/sql-database-import?view=azureipps&tabs=azure-powershell

Rawlings

I have specified in my original post that I am using the Azure REST API to check this information. – Muth

Answer 5

I find an article Azure SQL Database Export, it talked about how to check the status of an export by using the Microsoft Azure SQL Database Import/Export REST service API.

Summary:

In the Management Portal, click on the database and the dashboard will show you the recent export status. In Screenshot 5, you will see that the AUTOMATED EXPORT status for my database shows as NEVER EXPORTED. This will change once a successful export is done. In the Management Portal, click on the database and the dashboard will show you the recent export status.

After the export completes which was ultimately scheduled at 1AM, completed after 5 minutes and the automated export status in my dashboard shows that the same. (See screenshot 6).

Hope this helps.

Moreover

Answer 6

According to Microsoft:

"We have REST API’s for Import/Export operations into Azure sql but we don’t have any REST API to find Import/Export status. Customer should use Powershell to get status"

I have discovered that it is not possible to know if an import failed using the Powershell command, as a failed import is never created and returns a 404 (because the import failed).

One solution you could implement would be a best guess approach, where a limit of 48 hours is enforced, and any import taking longer than this would be interpreted as having failed. If you know that most SQL databases are imported in a time window of say 10 minutes, then it would be pragmatic to say that any SQL import taking longer than perhaps 48 hours, has failed entirely.

You may also opt to use in combination with the aforementioned time window a logarithmic fallback approach, where you progressively are backing off on when you are checking for the status. This will save you, Microsoft, and the Earth from unnecessary waste 🙂

Muth