SPRZEDAŻ: 1-800-867-1389
EN
Ta zawartość nie jest dostępna w wymaganym języku. Wersja w języku angielskim znajduje się tutaj.

Choosing Tools to Migrate a Database to Azure SQL Database

Updated: April 23, 2014

This topic discusses how to choose the best set of tools for a project to migrate a non-SQL Server database, Microsoft Azure SQL Databasedatabase, or a SQL Server database to Microsoft Azure SQL Database. In general, database migration involves the transfer of both the schema and data. Some migration tools handle both schema and data; other tools only handle one or the other.

There are several processes and tools you can use to migrate a database to Microsoft Azure SQL Database. Choosing a tool depends on the type, size, and complexity of the database being migrated:

Author: Shaun Tinline-Jones
Contributor: Steve Howard
Reviewer: Shawn Hernan

Tools to Migrate a non-SQL Server Database

A SQL Server Migration Assistant can be used to migrate database from other products such as Access, MySQL, Oracle, or Sybase to Microsoft Azure SQL Database. For more information, see How to: Use a SQL Server Migration Assistant with Azure SQL Database.

Microsoft Codename “Data Transfer” can transfer data in a CSV or Excel file to Microsoft Azure SQL Database. For more information, see Windows Azure SQL Database LabsMicrosoft Codename “Data Transfer” Tutorial.

Tools to Migrate Between SQL Database Services

To migrate data from one database in Microsoft Azure SQL Database to another database, you can use SQL Database copy and SQL Data Sync.

Microsoft Azure SQL Database supports a database copy feature. The feature creates a database in Microsoft Azure SQL Database which is a transactionally consistent copy of an existing database. To copy a database, you must be connected to the master database of the Microsoft Azure SQL Database service where the new database will be created, and use the CREATE DATABASE command:

CREATE DATABASE destination_database_name AS COPY OF 
[source_server_name.]source_database_name

The new database can be on the same service, or on a different service. The user running this statement must be in the dbmanager role on the destination service (to create a new database) and must be dbowner in the source database. For more information, see Copying Databases in Azure SQL Database.

SQL Data Sync (Preview) enables creating and scheduling regular synchronizations between Microsoft Azure SQL Database and databases hosed on either SQL Server or Microsoft Azure SQL Database. For more information, see SQL Data Sync.

Tools to Migrate a SQL Server Database

SQL Server to Microsoft Azure SQL Database migration projects can be broadly classified based on size and complexity:

  1. 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 into Microsoft Azure SQL Database, and the more likely it is for Microsoft 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.

  2. 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 Microsoft 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 Microsoft 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 Microsoft Azure SQL Database, or applications that need changes to work effectively with a remote database.

Databases can be classed into four categories, or quadrants:

Project size and complexity quadrants

A migration project that is originally assessed as small and simple can move another quadrant as more information is found during later research. Some of the reasons include:

  • The migration of a relatively small database may need data transfer optimizations if the cutover window is short.

  • The database requires schema changes that impact code in the applications that use the database.

  • The applications need changes required to operate effectively in a Azure SQL Database environment, such as robust retry logic or code changes to reduce network latency.

The most common reason for classifying a database migration as complex is when changes are needed to the database schema, the applications, or both. For example, schema changes typically require changes to the applications using the database. These changes mean incorporating development work as part of the project, and coordinating the deployment of both the new database and new versions of the applications that use the database. For these projects, pick a migration tool that supports development project work. For more information about the kinds of schema and application changes that might be required, see Planning an Azure SQL Database Migration Project.

Migrating a database to Microsoft Azure SQL Database requires transferring both the schema and data. Some migration tools can be used by themselves because they transfer both schema and data, such as the Azure SQL Database Migration Wizard and the Data-tier Application (DAC) BACPAC files. Other tools only transfer the schema, such as DAC packages, or data, such as bcp. If a migration requirement leads to using a tool that transfers only data or schema, such deciding to use bcp to transfer the data for a large project, then pair the use of that tool with one that transfers the other parts of the database.

While tools such as the Azure SQL Database Migration Wizard and the SQL Server Data Tools (SSDT) find most objects and syntax not supported on Microsoft Azure SQL Database, the current versions of the tools do not find all such issues. The tools are good for initial analysis and finding most issues during database development. The most reliable way to test that all schema issues have been addressed is to perform a test deployment of the database to Microsoft Azure SQL Database. The most reliable way to test that all Transact-SQL syntax issues have been addressed in application code is to perform a functional test of the application running against a copy of the database in a test Microsoft Azure SQL Database system.

It is common for complex migration projects that require many schema changes to incorporate several tasks and use multiple tools, such as:

  • Extract the schema into a database project in SSDT.

  • Set the project target to Microsoft Azure SQL Database to do the first analysis of objects not supported by Microsoft Azure SQL Database. Leave the target set to Microsoft Azure SQL Database so that future database development work can benefit from real-time flagging of syntax not supported on Microsoft Azure SQL Database.

  • Run a database development task to make all required schema changes. Once SSDT reports that no unsupported objects remain, perform a test deployment to Microsoft Azure SQL Database to ensure that the objects remaining in the database are supported by Microsoft Azure SQL Database.

  • Run a concurrent application development task to make all code changes driven by the schema changes. Generate traces of the Transact-SQL statements generated by the applications, and use the Azure SQL Database Migration Wizard to scan for syntax not supported on Microsoft Azure SQL Database.

  • Build a data transfer process that will perform any transformations needed to get the data from the old schema structures into the new schema. This may be most easily accomplished by using SQL Server Integration Services.

  • Perform integrated database and application testing. It is important to do fairly comprehensive functional testing on a database after it is running on Microsoft Azure SQL Database to ensure that Transact-SQL statements generated by the applications work on Microsoft Azure SQL Database.

  • Build deployment packages for the database schema and applications. Build the scripts required to run the data transfer process against the production systems.

  • Perform an integrated production deployment of the database schema, applications, and run the data transfer process.

For more information about running a Microsoft Azure SQL Database project, see Planning an Azure SQL Database Migration Project.

The Azure SQL Database Migration Wizard requires the least amount of overhead to run, and can be configured to capture gaps where the tool may have not picked up an issue, or be modified to no longer consider something as an issue. The tool is applicable in any of the quadrants, and is probably the best tool during the envisioning phase of a migration project. A feature of the tool that no other tool offers is the ability to analyze SQL Profiler Trace files. This provides significant coverage in its ability to include dynamic Transact-SQL. Trace analysis can also improve quality of the end-to-end functional testing. The wizard can break data and schema transfers into multiple operations, and will retry operations that fail. These features improve its ability to migrate large databases.

The SQL Server Data Tools (SSDT) provides useful practical functionality in all the quadrants. Its tight integration with Visual Studio makes it particularly useful in migrations that are considered complex. The analysis occurs when the objects are imported into the tool and warning and errors are raised during the build process. SSDT has powerful features and can accommodate complex solutions. SSDT is Microsoft's recommended tool for developing against Microsoft Azure SQL Databases, as well as on-premises SQL Server databases.

Comparing SQL Server Migration Tools

This table summarizes the characteristics of the tools and processes that can be used to migrate a SQL Server database to Microsoft Azure SQL Database:

 

Tools

Schema

Azure SQL Database Compatibility Check

Data

Data Transfer Efficiency

Note

SQL Database Migration Wizard

Yes

Yes

Yes

Good

  • Great capabilities, e.g. evaluate trace files

  • Open source on CodePlex

  • Not supported by Microsoft

SQL Server Data Tools

Yes

Yes

No

N/A

  • Good for managing migration development work

  • Handles complex schema changes

  • Full Azure SQL Database support

DAC Package

Yes

Yes

No

N/A

  • Entity containing all database objects, but no data

  • Full Azure SQL Database support

DAC BACPAC Import Export

Yes

Yes

Yes

Good

  • Export/import of DAC plus data with DAC framework

  • Service for cloud-only support available

  • SQL DAC Examples available on CodePlex

Generate Scripts Wizard

Yes

Some

Yes

Poor

  • Has explicit option for Azure SQL Database scripts generation

  • Good for smaller database

bcp

No

N/A

Yes

Good

  • Efficient transfer of data to existing table

  • Each bcp command transfer one database

SQL Server Integration Services

No

N/A

Yes

Good

  • Most flexibility

SQL Server Import and Export Wizard

No

N/A

Yes

Good

  • Simple UI on top of SSIS; also available in SQL Server Management Studio

Czy oceniasz te materiały jako pomocne?
(Pozostało znaków: 1500)
Dziękujemy za opinię.

Zawartość społeczności

Dodaj
Pokaż:
© 2014 Microsoft