[Azure] Troubleshoot Import/Export/migration issues for Azure SQL Database

There are multiple options to export/import BACPAC files in Azure SQL Database.

General instructions: [Export to a BACPAC file ](https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export?view=azuresql) / [Import a BACPAC file](https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import?view=azuresql&tabs=azure-powershell)

- Azure portal. (limitations: for database size less than 150GB) | [Import or Export a Database Using Private Link - Azure SQL Database](https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import-export-private-link?view=azuresql#export-a-database-using-private-link-in-powershell)
> 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.
Additionally, as the Import/Export service performs a logical backup of the database the time to complete is more dependent on the number of objects in the database than a traditional physical database backup.
- (GUI) SSMS, Azure Data Studio
	- [Download SQL Server Management Studio](https://learn.microsoft.com/en-us/ssms/install/install)
	- Instructions: [Export a BACPAC File - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/data-tier-applications/export-bacpac-file?view=sql-server-ver17#use-the-export-data-tier-application-wizard)
- SqlPackage (most recommended for troubleshooting)
	- [Download and Install SqlPackage - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver17)
	- [Using SQLPackage to import or export SQL Server and Azure SQL DB | Microsoft Community Hub](https://techcommunity.microsoft.com/blog/azuredbsupport/using-sqlpackage-to-import-or-export-sql-server-and-azure-sql-db/368861)
	
	
### Troublleshoot with SqlPackage

As other tools especially Azure portal provide less error details, enabling diagnostics with SqlPackage typically offers deeper insights into the underlying issues. Please note this approach needs downloading the BACPAC file to local machine and run the command.

Usually we recommend using an Azure VM to proceed with this approach.
The following tips are specific to running import or export against Azure SQL Database from an Azure virtual machine (VM):
	- Use Business Critical or Premium tier database for best I/O performance. You can scale down to lower pricing tier later.
	- Use SSD storage on the VM.
	- Ensure there's enough disk to unzip the bacpac.
	- Execute SqlPackage from a VM in the same region as the database.
	- Enable accelerated networking in the VM.
	
Reference: [Troubleshooting Issues and Performance with SqlPackage](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/troubleshooting-issues-and-performance-with-sqlpackage?view=sql-server-ver17)

- [Download and Install SqlPackage - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver17)
- [Using SQLPackage to import or export SQL Server and Azure SQL DB | Microsoft Community Hub](https://techcommunity.microsoft.com/blog/azuredbsupport/using-sqlpackage-to-import-or-export-sql-server-and-azure-sql-db/368861)

```powershell
sqlpackage.exe /Action:Export /ssn:tcp:MyOwnServer.database.windows.net,1433 /sdn:AdventureWorks /su:AdminUser /sp:AdminPassword1 /tf:C:\Temp\AW.bacpac /df:C:\Temp\AWExport.log  /d
```
Guidance for SqlPackage troubleshooting: [Troubleshooting Issues and Performance with SqlPackage - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/troubleshooting-issues-and-performance-with-sqlpackage?view=sql-server-ver17)

### Known Export/Import errors

Import errors:
- System.OutOfMemoryException: Switched to SqlPackage with `/p:Storage=File` to mitigate memory issues. Increase VM size and SQL DB tier for better performance.
- StackOverflowException: adjusted SqlPackage parameters: `/ThreadMaxStackSize:50`
- Schema-related: Object dependency errors: drop invalid objects.

Export errors:

- `Error SQL71501: Error validating element [dss]: schema: [dss] has an unresolved reference to object [##MS_syncAccount##]`

[Exporting a database that is/was used as SQL Data Sync metadata database
](https://techcommunity.microsoft.com/blog/azuredbsupport/exporting-a-database-that-iswas-used-as-sql-data-sync-metadata-database/369062)

- `CreateIndexStatementInterpretationVisitor: IndexedObject should not be null` during export

Schema related issues. ` /p:VerifyExtraction=False` was set in SqlPackage command but it didn't work.
Tracking the methods sequence in call stack we found the root is a stored procedure: `CreateProcedureStatement->eateIndexStatemen ->InterpretCreateIndex->Exception`. To address this, used  the SSMS "Generate Script" option to export only the stored procedure definitions. Upon reviewing all the definitions,  identified the problematic one, which includes an index creation statement.

```sql
-- table schema check
SELECT c.name AS ColumnName, c.is_computed, t.name AS DataType, c.max_length, c.precision, c.scale, c.is_nullable FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = 'XXXX';
-- check index
SELECT i.name AS IndexName, i.type_desc AS IndexType, i.is_disabled, OBJECT_NAME(i.object_id) AS TableName, c.name AS ColumnName FROM sys.indexes i LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id LEFT JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE OBJECT_NAME(i.object_id) = 'XXXX';
```
- `The operation was not allowed because of the outbound firewall rule configuration for SERVER.database.windows.net`:

Navigate to target SQL Server > Networking > Connectivity > Outbound networking to review and add proper settings from Azure portal.

### Bypass errors during migration from other platform

```powershell
sqlpackage.exe /Action:Publish /tsn:XXXX.database.windows.net /tdn:DBNAME /tu:ADMINUSER /tp:PASSWORD /sf:C:\temp\XXX.dacpac /p:AllowIncompatiblePlatform=True /p:ExcludeObjectTypes=Users,Logins /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P4 /df:C:\temp\Publish.log /d
```

-  A project which specifies SQL Server 2016 as the target platform cannot be published to Microsoft Azure SQL Database v12.

Add  `/p:AllowIncompatiblePlatform=True` parameter

-  Error SQL72045: Script execution error.  The executed script: CREATE LOGIN;

Add  `/p:ExcludeObjectTypes=Users,Logins,Permissions,DatabaseRoles,RoleMembership,ServerRoleMembership,ServerRoles` and `/p:AllowIncompatiblePlatform=True` to bypass

-  Could not connect to server to retrieve default data paths

Add `/p:IgnoreFileAndLogFilePath=True`

### Performance

The following tips are specific to running import or export against Azure SQL Database from an Azure virtual machine (VM):
* Use Business Critical or Premium tier database for best performance.
* Use SSD storage on the VM.
* Ensure there's enough room to unzip the bacpac.
* Execute SqlPackage from a VM in the same region as the database.
* Enable accelerated networking in the VM.
* For more information on utilizing a PowerShell script to collect more information about an import operation, [see Lesson Learned #211: Monitoring SQLPackage Import Process](https://techcommunity.microsoft.com/blog/azuredbsupport/lesson-learned-211-monitoring-sqlpackage-import-process/3556382).

Reference:  [Troubleshooting Issues and Performance with SqlPackage - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/troubleshooting-issues-and-performance-with-sqlpackage?view=sql-server-ver17#azure-sql-database)

Actually we see import process takes hours to days with SqlPackage.
For migration between Azure SQL Databases, we highly recommend using database copy instead.
SqlPackage performs a logical data movement, not a physical copy. It reads the BACPAC, creates schema, and inserts data row-by-row using bulk operations. This is inherently slower than database copy.
[Copy a Database - Azure SQL Database | Microsoft Learn](https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?view=azuresql&tabs=azure-powershell)

Currently the SqlPackage with data in Parquet files is in preview which offer improved performance over import/export operations. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-with-data-in-parquet-files?view=sql-server-ver17

### Other data sync/migration scenario

[Azure SQL Database Data Sync Retirement: Migration Scenarios and Recommended Alternatives | Microsoft Community Hub](https://techcommunity.microsoft.com/blog/azuredbsupport/azure-sql-database-data-sync-retirement-migration-scenarios-and-recommended-alte/4481967)