Export Azure SQL Database to Storage

Azure SQL Database Export to Storage

Overview of Azure SQL Database Export

Learn how to export your Azure SQL Database using Private Link, a method currently in preview. This tutorial covers the process of importing a database from a BACPAC file to Azure storage via the Azure portal, PowerShell, or REST API.

Prerequisites

Before starting the export process, ensure 'Allow Access to Azure Services' is set to ON. Understand that during the preview, the database and storage blob must reside on the same Azure Cloud type. Note that only database imports from BACPAC files are supported in this tutorial.

Configuring Private Link for Import/Export

Private Link for Azure SQL Database import/export is a service managed private endpoint, essential for secure communication during the process. Manually approve the private endpoint, which is exclusively used for import/export operations, involving both Azure SQL Database and Azure Storage services.

Limitations and Considerations

Be aware of the preview limitations; Import using Private Link does not allow specifying backup storage redundancy, instead defaulting to geo-redundant backup storage redundancy. The import/export process may not support certain backup configurations.

Best Practices for Exporting

To handle larger BACPAC files efficiently, export to local storage with SqlPackage. Avoid special characters and limit file names to under 128 characters for Azure Storage. Temporarily increasing compute size can enhance performance, as can pausing all read/write activities during the export. Implement clustered indexes on large tables for a performance boost.

Handling Large Databases

For databases over 150GB, use SQLPackage to mitigate local disk space issues and to improve scale and performance. Be mindful that exports exceeding 20 hours may be canceled. Consider running multiple SqlPackage commands in parallel for faster operations.

Frequently Asked Questions

Can I use the BCP utility to export my database directly to Azure Blob storage?

No, the BCP utility does not support direct export to Azure storage. Data must be exported locally and then transferred to Azure.

What is the maximum size for a BACPAC file when exporting to Azure Blob storage?

The maximum size of a BACPAC file is 200 GB if it is being exported to blob storage.

Can I use SSMS to export my Azure SQL Database if it's larger than 200 GB?

No, SSMS can be used to export if the database size is below 200 GB. For databases larger than 200 GB, it is recommended to use the SQLPackage utility.

What should I do to ensure a smooth export of my Azure SQL Database to a BACPAC file?

To ensure a smooth export, you should cease any write activity and ensure that no write activity is occurring during the export. Exporting from a transactionally consistent copy of the database is also recommended for maintaining consistency.

How can I optimize the performance of exporting a BACPAC file?

You can increase performance by increasing the compute size temporarily, ceasing read and write activity, and ensuring that large tables have a clustered index with non-null values. Running DBCC SHOW_STATISTICS may also help determine if tables are optimized for export.

Common Use Cases

• 
  Scheduled backups of Azure SQL databases to Azure Blob Storage
• 
  Migrating data from Azure SQL Database to another cloud environment
• 
  Archiving historical data for compliance and auditing
• 
  Enabling data analysis by exporting to Azure Data Lake for big data analytics
• 
  Facilitating disaster recovery by having off-site storage of database backups

Discover Sourcetable: A Seamless Azure SQL Database Alternative

Explore the simplicity of Sourcetable, a powerful spreadsheet solution transforming data integration and real-time analysis. It stands out as a prime alternative to Azure SQL Database export by centralizing data from multiple sources into one accessible interface.

With Sourcetable, you bypass traditional export complexities, facilitating direct, real-time queries within a familiar spreadsheet environment. No more waiting for data exports; Sourcetable streamlines your workflow, enhancing data interaction and decision-making efficiency.

Maximize productivity by manipulating and analyzing your Azure SQL Database data live, without the need for external storage exports. Sourcetable's spreadsheet interface offers an intuitive, user-friendly experience, making it an exceptional tool for dynamic data management.