Migrating Databases to Azure SQL Database
Updated: October 14, 2014
This topic describes how to migrate a relational database to Microsoft Azure SQL Database or between servers in Azure. We will explore migration techniques and tools that can be used to migrate the database schema and data.
Migrating a database requires transferring both data and schema to a new location. Migration projects can be classified based on size and complexity:
Size: the amount of data and number of schema objects to be transferred. The more data there is, the longer it takes to transfer the database, and the more likely it is for Azure SQL Database to throttle the migration process. For large migrations it is important to select a migration process that can perform optimizations such as launching concurrent data load operations, or separating operations into smaller batches that are less likely to be throttled. A migration tool that can automatically retry throttled operations is more important for a large migration. A database usually crosses the threshold from small or large at around 200 MB of data. A database with a very large number of objects, such as 1,000 or more, would also be classified as a large database. If the migration process performs the schema transfer as a single transaction, that transfer is likely to be throttled due to the amount of log space used by the transaction.
Complexity: the scope of engineering changes needed to the database and associated applications. Having more objects with complicated structures increases the probability that the database contains syntax elements not supported in Azure SQL Database, which drives more development work as part of the migration project. A simple migration project would include a database that requires no schema changes to run in Azure SQL Database, coupled with applications that only need connection string changes. A complex migration project could result from either a database that requires schema changes to address elements not supported on Azure SQL Database, or applications that need changes to work effectively with a remote database.
Migrating a database requires careful planning in order to ensure the application continues to run well. Consider the following:
If your database contains objects that are not supported by Azure SQL Database, application changes may be required.
Dependencies on features that are not supported in Azure SQL Database, such as SQL Server replication, will need to be removed.
Dependencies on Transact-SQL syntax that is not supported in Azure SQL Database, such as distributed queries, will need to be removed.
Understand your workload. This will be useful when selecting an appropriate service tier and performance level for your database.
Use the information in Upgrade SQL Database Web/Business Databases to New Service Tiers and Azure SQL Database Service Tiers and Performance Levels to determine the appropriate service tier and performance level for your database.
Application changes may be required to operate effectively when moving your database from on-premises into an external datacenter. For example, network latency can be affected, which may require modifying your application to minimize the amount of data transmitted across the network.
There are many tools available to successfully migrate your database. These tools can be used to migrate an Azure SQL Database, as well as SQL Server on-premises and non-SQL Server databases. Some tools can be used to migrate only schema or data, while others are designed to migrate both. The best tool for your scenario depends on the type, size, and complexity of the database being migrated. This section describes some of the available tools.
You can migrate both schema and data of an existing Azure SQL Database by exporting the database, storing the export file in an Azure Blob Storage Account, then importing as a new Azure SQL Database. The file that created when this export is referred to as a BACPAC (.bacpac) file. For more information, see How to: Import and Export a Database (Azure SQL Database).
The database copy feature creates a new database in Azure that is a transactionally consistent copy of an existing Azure SQL Database. For more information, see Copying Databases in Azure SQL Database.
A data-tier application (DAC) is a self-contained unit for developing, deploying, and managing data-tier objects. There are two options for migrating with DAC:
DAC BACPAC – Migrate both schema and data by exporting a BACPAC from an existing on-premises SQL Server, storing in an Azure Blob Storage Account, and then import into a new Azure SQL Database database. The BACPAC can be created using the Export Data-tier Application Wizard from SQL Server Management Studio. For more information, see How to: Use a DAC BACPAC to Migrate a Database to Azure SQL Database
DAC Package – Create DAC packages by building a SQL Server Data Tools database project or extract a DAC package using the Extract Data-tier Application Wizard in SQL Server Management Studio. For more information, see How to: Use a DAC Package to Migrate a Database to Azure SQL Database.
SQL Server Integration Services (SSIS) can be used when complex transformations of data are needed. SSIS can be used to move data into and out of Azure SQL Database. For more information, see How to: Use Integration Services to Migrate a Database to Azure SQL Database.
The SQL Server Import and Export Wizard is an easy way to create an SSIS package to migrate data. After configuring the source and destination, you can specify basic data transformations. These packages can be saved, modified, executed, and scheduled as a job. For more information, see How to: Use the Import and Export Wizard to Migrate a Database to Azure SQL Database.
The SQL Database Migration Wizard is a tool that helps migrate both schema and data between on-premises SQL Server and Azure SQL Database, as well as between Azure SQL Database servers. The tool also analyzes trace files and scripts for compatibility issues with Azure SQL Database. For more information, see How to: Use the SQL Database Migration Wizard.
SQL Server Migration Assistant (SSMA) is a suite products that can be used to migrate databases from other products such as Access, MySQL, Oracle, or Sybase to Azure SQL Database. For more information, see How to: Use a SQL Server Migration Assistant with Azure SQL Database.
If your database supports scripting schemas, you can generate the database schema and then execute the script against a new Azure SQL Database. Keep in mind that the script may need to be modified to use supported features and syntax before executing.
You can also use the Generate and Publish Scripts Wizard in SQL Server to create Transact-SQL scripts that can be used to transfer schema, data, or both. This wizard has an option to generate the script specifically for Azure SQL Database. For more information, see How to: Use Generate and Publish Scripts Wizard to Migrate a Database to Azure SQL Database
The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. For more information, see How to: Use bcp to Migrate a Database to Azure SQL Database.
SQL Server Data Tools allows you to create database projects inside a Visual Studio environment. These projects can be used to develop, test, and deploy database migration projects. For more information, see How to: Use SQL Server Data Tools to Migrate a Database to Azure SQL Database.
SQL Data Sync (Preview) enables creating and scheduling regular synchronizations between Azure SQL Database and databases hosted in SQL Server or Azure SQL Database. For more information, see SQL Data Sync.
Microsoft Sync Framework 2.1 provides synchronization capabilities between on-premises and Azure SQL Database servers, as well as between two or more Azure SQL Databases in the same or different datacenters. Using Sync Framework 2.1, you can extend the schema and data within your SQL Server database or Azure SQL Database to Azure SQL Database datacenters around the world to provide geo-available data access. For more information, see Microsoft Sync Framework 2.1 Software Development Kit (SDK).