Exporteren (0) Afdrukken
Alles uitvouwen
EN
Deze inhoud is niet beschikbaar in uw taal, maar wel in het Engels.

How to: Use the SQL Database Migration Wizard

Updated: October 16, 2014

The SQL Database Migration Wizard is a shared source UI tool that helps migrating SQL Server databases to Microsoft Azure SQL Database. Other than migrating data, it can also be used to identify compatibility issues, fix them where possible, and notify you of the issues it finds.

Before You Begin

The SQL Database Migration Wizard is a flexible and easy tool for migrating simple SQL Server databases to Azure SQL Database. For more complex databases, the wizard is a good tool for identifying the changes needed to meet Azure SQL Database requirements.

Recommendations

The SQL Database Migration Wizard supports these main tasks:

  • Migrate both the schema and data of a simple database to Azure SQL Database. The wizard can be configured to perform multiple concurrent bulk copy operations when loading large amounts of data.

  • Help analyze larger, more complex databases during the envisioning or planning stages:

    • Analyze the database for objects not supported by Azure SQL Database.

    • Review a Transact-SQL file for syntax not supported by Azure SQL Database. The wizard can analyze either a Transact-SQL script file or a SQL Server Profiler trace file.

Before running the Wizard, download and review the manual from the Documentation tab in the Codeplex project.

The wizard can be run in a graphical wizard mode or a command prompt utility mode. The wizard uses two configuration files that you can modify to tailor the operation of the wizard. The configuration files establish the default behavior when running in the graphical mode, and controls the behavior when running in the command-prompt mode.

  • The file NotSupportedByAzureFile.config contains Regex entries that define the objects not supported by Azure SQL Database. You can tailor the configuration file to look for additional patterns you want to exclude from any of the databases you plan to host in Azure SQL Database.

  • The file SQLAzureMW.exe.config controls utility behaviors, such as how to make connections, which Transact-SQL file to analyze, or data copy behaviors. You can modify the file to tailor the default wizard behaviors for your site.

The SQL Database Migration Wizard combines three features to support more reliable bulk copy operations at higher rates than other options, such as using a Data-tier Application (DAC) BACPAC.

  • The SQL Database Migration Wizard has built-in logic for handling connection loss. It divides the schema updates into individual batches, where each batch is managed as a separate transaction. The wizard runs until Microsoft Azure SQL Database terminates the connection. If the wizard encounters a connection error before the schema updates are complete, it reestablishes a new connection with Microsoft Azure SQL Database and picks up processing after the last successfully committed transaction. In the same manner, when using bcp to upload the data to Microsoft Azure SQL Database, the wizard chunks the data into individual batches and uses retry logic to figure out the last successful record uploaded before the connection was closed. Then it has bcp restart the data upload with the next set of records.

  • You can configure the wizard to use multiple, concurrent bulk copy processes to speed the loading of large amounts of data. The wizard cannot perform multiple concurrent bulk copy operations against a single table, but can schedule concurrent bulk copy operations against different tables.

  • You can reduce the chance of Microsoft Azure SQL Database throttling the wizard by specifying a wait period between bulk copy batches and configuring a small batch size. You must balance batch size against the number of batches. If the batch size is too small, it may result in a large number of batches that have to be transmitted individually across the network, creating network latency issues. Do some experiments to find a batch size that is small enough to avoid throttling but large enough to reduce network latency.

[Top]

Limitations and Restrictions

noteNote
The SQL Database Migration Wizard is a shared source tool built and supported by the community.

The SQL Database Migration Wizard does not include a Transact-SQL parser, it does pattern matching based on Regex definitions in the file NotSupportedByAzureFile.config. Some pattern matches might be false positives. Also, the config file supplied with the wizard is not guaranteed to have patterns for all items not supported by Azure SQL Database. To keep the migration project moving, you can update the configuration file to add patterns as you find them in. For general issues, you can also consider submitting them to the CodePlex project to be incorporated in future versions of the wizard. When your project needs a more rigorous analysis of a database, consider extracting a DAC package file and importing that into a SQL Server Data Tools project where you can set SQL Database as the project target. While the SQL Server Data Tool will analyze the project using a Transact-SQL parser, it may not find all Azure SQL Database incompatibilities in a database.

The most reliable way to determine whether all schema issues have been addressed is to perform a test deployment of the new database schema to Azure SQL Database. The most reliable way to determine if all Transact-SQL issues have been addressed in application code is to perform a functional test of the application running against a copy of the database deployed to Azure SQL Database.

The wizard is a good tool for the initial analysis of any database. Other tools, however, are better for managing development work on complex databases that require many changes before they can run on Azure SQL Database. For example, in the Regex pattern definitions you can specify replacements for the patterns found by the wizard, but this functionality is limited. To manage more complex changes, consider using another tool, such as extracting a DAC package file and importing that into a SQL Server Data Tools project.

Generating a profiler trace from a production system may slow performance too much. It is better to generate a trace from a test system. If you must profile a production system, minimize the impact by tracing only statement completed events.

[Top]

Prerequisites

The SQL Database Migration Wizard can be downloaded from the SQL Database Migration Wizard project on Codeplex. Unzip the package to your local computer, and run SQLAzureMW.exe.

[Top]

Migrate a Database by Using the SQL Database Migration Wizard

To migrate a database:

  1. Select the process you want the wizard to guide you through.

  2. Select the source you want to script.

  3. Select database objects to script.

  4. Generate the script. You have the option to modify the script afterwards.

  5. Enter information for connecting to target server. You have the option to create the destination database on Microsoft Azure SQL Database.

  6. Run the script against destination server.

[Top]

Analyze a Database by Using the SQL Database Migration Wizard

To analyze a database for migration issues:

  1. Select the process you want the wizard to guide you through.

  2. Select the source you want to analyze.

  3. Select database objects to analyze.

  4. Generate the script.

  5. Review the Summary Results pane for issues reported by the Wizard.

[Top]

Analyze a Transact-SQL File by Using the SQL Database Migration Wizard

To analyze a database for migration issues:

  1. Select the process you want the wizard to guide you through.

  2. Select the Transact-SQL file you want to analyze as the source.

  3. Generate the script.

  4. Review the Summary Results pane for issues reported by the Wizard.

[Top]

Analyze a Trace File by Using the SQL Database Migration Wizard

To analyze a database for migration issues:

  1. Select the process you want the wizard to guide you through.

  2. Select the trace file you want to analyze as the source.

  3. Generate the script.

  4. Review the Summary Results pane for issues reported by the Wizard.

[Top]

Resources

Community-inhoud

Toevoegen
Weergeven:
© 2014 Microsoft