Import-Export overview
With Azure SQL Import-Export users can import a SQL Server database into Azure SQL Database using a BACPAC file or export from an Azure SQL Database into a BACPAC file. BACPAC files can be stored in Azure Blob storage(standard storage only) or local storage in an on-premises location. Importing a database into Azure SQL DB using Import-Export creates the database and imports schema and data into the database from the BACPAC file provided. Here is an overview of Import Export works:
To successfully run Import Export operations in Azure SQL Database, users must set “Allow Access to Azure Services” parameter under Firewall settings to ON. Otherwise, the Import Export operations fail with errors. But in many scenarios users would not want to give such a broad privilege for Import/Export operations and require a more precise and controlled way to perform the operation.
Import-Export Private Link
To overcome this problem we are introducing a new option to perform Import Export using Private Link(in Preview). With this option users can perform Import or Export via a Private end point, without allowing all Azure services access to SQL Server. Private Link in Azure SQL Database allows you to connect to various PaaS services in Azure via a private endpoint. For Import/Export a Service Managed Private Endpoint is created and managed by Microsoft and is exclusively used by the Import-Export service for a given import or export operation. Since Import-Export service interacts with SQL Server and Storage, two end points will be created by this feature one for SQL Server and another for Storage account. Here is an overview of how Private link works:
Once the service managed private endpoints are approved by user, all communication between import/export, user database and storage will happen over the service managed private link. Note that, user must manually approve the service managed private endpoints via Azure Portal (for both SQL Server & Storage) before they can start using it. The Import-Export operation will wait until the endpoints are approved by the user.
Configuring Import-Export Private Link
Import-Export Private Link can be configured via Azure portal, PowerShell or using REST API. Here is a step by step guide to configure Private link in Portal and PowerShell
Configure Import-Export Private link using Azure portal
Here are detailed instructions on how to enable Private link for Import and Export operations
Create Private Link for Import
- Go to server into which you would like to import database. Select Import database from toolbar in Overview page.
- In Import Database page, select Use Private Link option
- Enter the storage account, server credentials, Database details and select on Ok
- This step will create two Private End points: One for SQL Server and another for Storage.
Create Private Link for Export
- Go to the database that you would like to export. Select Export database from toolbar in Overview page
- In Export Database page, select Use Private Link option
- Enter the storage account, server sign-in credentials, Database details and select Ok
- This step will create two Private End points: One for SQL Server and another for Storage.
Approve Private End Points
Two endpoints one SQL Server and another Storage will be created. Users can either approve both of them in Private Link Center or approve them individually in Private end point section of respective services.
Approve Private Endpoints in Private Link Center
- Go to Private Link Center
- Navigate to Private endpoints section
- Approve the private endpoints you created using Import/Export service
If for some reason user cannot approve the endpoints in Private Link Center, they can approve them in SQL Server and Storage by following instructions below.
Approve Private End Point connection on Azure SQL Database
- Go to the server that hosts the database.
- Open the ‘Private endpoint connections’ page in security section on the left.
- Select the private endpoint you want to approve.
- Select Approve to approve the connection.
Approve Private End Point connection on Azure Storage
- Go to the storage account that hosts the BACPAC file.
- Open the ‘Private endpoint connections’ page in security section on the left.
- Select the Import-Export private endpoints you want to approve.
- Select Approve to approve the connection.
After the Private End points are approved both in Azure SQL Server and Storage account, Import or Export jobs will be kicked off. Until then, the jobs will be on hold.
You can check the status of Import or Export jobs in Import-Export History page under Data Management section in Azure SQL Server page.
Configure Import-Export Private Link using PowerShell
Below are instructions and a sample script to configure Import-Export Private link using PowerShell
Import a Database using Private link in PowerShell
Use the New-AzSqlDatabaseImport cmdlet to submit an import database request to Azure. Depending on database size, the import may take some time to complete. The DTU based provisioning model supports select database max size values for each tier. When importing a database use one of these supported values. Here is a sample PowerShell import command:
$importRequest = New-AzSqlDatabaseImport -ResourceGroupName "<resourceGroupName>" ` -ServerName "<serverName>" -DatabaseName "<databaseName>" ` -DatabaseMaxSizeBytes "<databaseSizeInBytes>" -StorageKeyType "StorageAccessKey" ` -StorageKey $(Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName ` -StorageAccountName "<storageAccountName>").Value[0] -StorageUri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" ` -Edition "Standard" -ServiceObjectiveName "P6" ` -UseNetworkIsolation $true ` -StorageAccountResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourcegroups/<resource_group_name>/providers/Microsoft.Storage/storageAccounts/<storage_account_name>" ` -SqlServerResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourceGroups/<resource_group_name>/providers/Microsoft.Sql/servers/<server_name>" ` -AdministratorLogin "<userID>" ` -AdministratorLoginPassword $(ConvertTo-SecureString -String "<password>" -AsPlainText -Force)
Export a Database using Private Link in PowerShell
Use the New-AzSqlDatabaseExport cmdlet to submit an export database request to the Azure SQL Database service. Depending on the size of your database, the export operation may take some time to complete. Here is a sample PowerShell export command:
$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName "<resourceGroupName>" ` -ServerName "<serverName>" -DatabaseName "<databaseName>" ` -DatabaseMaxSizeBytes "<databaseSizeInBytes>" -StorageKeyType "StorageAccessKey" ` -StorageKey $(Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName ` -StorageAccountName "<storageAccountName>").Value[0] -StorageUri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" ` -Edition "Standard" -ServiceObjectiveName "P6" ` -UseNetworkIsolation $true ` -StorageAccountResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourcegroups/<resource_group_name>/providers/Microsoft.Storage/storageAccounts/<storage_account_name>" ` -SqlServerResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourceGroups/<resource_group_name>/providers/Microsoft.Sql/servers/<server_name>" ` -AdministratorLogin "<userID>" ` -AdministratorLoginPassword $(ConvertTo-SecureString -String "<password>" -AsPlainText -Force)
Here is a sample end to end PowerShell script to perform Export operation using Private Link:
$storageResourceForPrivateLink = "/subscriptions/<<Subscription ID>>/resourcegroups/<<Resource Group >>/providers/Microsoft.Storage/storageAccounts/<<Storage Account>>"
$sqlResourceForPrivateLink = "/subscriptions/<<Subscription ID>>/resourcegroups/<<Resource Group >>/providers/Microsoft.Sql/servers/<<SQL Server>>"
$storageKey = (Get-AzureStorageKey -StorageAccountName "<<Storage Account>>").Primary
$storageURI = "https://<<Storage Account>>/dbexportsour/SampleExportFile_PS4.bacpac"
$adminPWD = $(ConvertTo-SecureString -String "<<SQLAdminPassword>>" -AsPlainText -Force)
$adminLogin = "<<SQL Admin Account>>"
$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName "<<Resource Group >>" -ServerName "<<SQL Server>>" -DatabaseName "<<Database Name>>" -StorageKeyType "StorageAccessKey" -StorageKey $storageKey `
-StorageUri $storageURI -UseNetworkIsolation $true -StorageAccountResourceIdForPrivateLink $storageResourceForPrivateLink -SqlServerResourceIdForPrivateLink $sqlResourceForPrivateLink `
-AdministratorLogin $adminLogin -AdministratorLoginPassword $adminPWD
Start-Sleep -Seconds 30
## Approve the Private Endpoint Connection for the Storage Account
$storagePrivate_Endpoint_Connection_Resource = Get-AzPrivateEndpointConnection -PrivateLinkResourceId $storageResourceForPrivateLink | `
Where-Object {($_.PrivateEndpoint.Id -like "*ImportExportPrivateLink_Storage*" -and $_.PrivateLinkServiceConnectionState.status -eq "Pending")}
Approve-AzPrivateEndpointConnection -ResourceId $storagePrivate_Endpoint_Connection_Resource.Id
## Approve the Private Endpoint Connection for the SQL Account
$sqlPrivate_Endpoint_Connection_Resource = Get-AzPrivateEndpointConnection -PrivateLinkResourceId $sqlResourceForPrivateLink | `
Where-Object {($_.PrivateEndpoint.Id -like "*ImportExportPrivateLink_SQL*" -and $_.PrivateLinkServiceConnectionState.status -eq "Pending")}
Approve-AzPrivateEndpointConnection -ResourceId $sqlPrivate_Endpoint_Connection_Resource.Id
Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink | Where-Object {($_.Status -eq "InProgress")}
Limitations
- Import using Private Link does not support specifying a backup storage redundancy while creating a new database and creates with the default geo-redundant backup storage redundancy. As a work around, first create an empty database with desired backup storage redundancy using Azure portal or PowerShell and then import the BACPAC into this empty database.
- Import and Export operations are not supported in Azure SQL DB Hyperscale tier yet.
- Import using REST API with private link can only be done to existing database since the API uses database extensions. To workaround this create an empty database with desired name and call Import REST API with Private link.
More information about the feature can be found in Import-Export Private Link documentation