SQL Azure Migration Wizard Simplifies Cloud Database Migrations

The SQL Azure Migration Wizard is a game-changer for anyone looking to migrate their database to the cloud. It simplifies the process, making it easier to move your data to SQL Azure without the need for manual scripting.

This wizard is designed to automate the migration process, reducing the risk of errors and minimizing downtime. By automating the migration, you can save time and resources that would otherwise be spent on manual scripting and testing.

The SQL Azure Migration Wizard supports a wide range of database sources, including SQL Server, Oracle, and MySQL. This means you can migrate your data from almost any database system to SQL Azure, making it a versatile tool for cloud migrations.

With the SQL Azure Migration Wizard, you can also perform a comprehensive assessment of your database, identifying potential issues and suggesting fixes before the migration process begins. This helps ensure a smooth and successful migration.

To use the Azure SQL Migration wizard, you'll need to download and install Azure Data Studio. This is a prerequisite for all migration scenarios.

Azure Data Studio is a free, open-source tool that provides a modern development experience for data professionals. It's available for download from the official Azure website.

You'll also need to install the Azure SQL Migration extension from Azure Data Studio Marketplace. This extension provides the necessary tools and features for migrating databases to Azure SQL.

To migrate databases, you'll need an Azure account with one of the following built-in roles: Azure SQL Database Contributor, Azure SQL Database Contributor (Preview), or Azure SQL Database Contributor (Preview) - Role. Alternatively, you can assign a custom role.

You'll also need to create a target instance of Azure SQL Database. This will be the destination for your database migration.

Make sure that the SQL Server login that connects to the source SQL Server instance is a member of the db_datareader role. Similarly, ensure that the login for the target SQL Server instance is a member of the db_owner role.

Here are the specific prerequisites for the Azure SQL Migration wizard:

Note that an Azure account is only required when you configure the migration steps. An Azure account isn't required for the assessment or to view Azure recommendations in the migration wizard in Azure Data Studio.

The SQL Azure Migration Wizard Process is a straightforward and user-friendly interface that guides you through the migration process.

You can access the migration wizard by choosing Azure SQL Migration under the General heading in the server overview tab.

The wizard is comprised of seven steps: six requiring user input and one displaying a summary of the selected migration properties.

To open the Migrate to Azure SQL wizard in Azure Data Studio, you'll need to follow these steps:

The migration wizard will take you through each step of the process, ensuring that you have all the necessary information to complete the migration.

To open the Migrate to Azure SQL wizard in Azure Data Studio, you'll need to follow a series of steps. First, go to Connections and select your on-premises instance of SQL Server, or connect to SQL Server on an Azure virtual machine.

To configure migration settings, navigate to Step 3: Azure SQL target in the Migrate to Azure SQL wizard. Here, you'll need to complete the necessary steps for your target Azure SQL Database instance.

In Step 4: Migration mode, select Offline migration and then select Next. This will allow you to review your table selections, which is crucial to ensure that you're migrating the correct data.

To configure settings for your migration, you'll need to connect to your on-premises SQL Server instance in Azure Data Studio.

Select and connect to your on-premises instance of SQL Server. You can also connect to SQL Server on an Azure virtual machine.

In the server menu under General, select Azure SQL Migration. This will open the Azure SQL Migration dashboard.

From the dashboard, select Migrate to Azure SQL to open the migration wizard.

You can then start a new session or resume a previously saved session on the first page of the wizard.

To configure migration settings, you'll need to complete the following steps:

If no tables are selected or if a username and password aren't entered, the Next button won't be available to select.

You'll need to select the logins you want to migrate from the source SQL server to the Azure SQL target.

In Step 1: Azure SQL target on the New login migration wizard, complete the following steps. You'll need to enter the associated Microsoft Entra domain name for Windows accounts.

To select the logins, go to Step 2: Select login(s) to migrate. Here, you'll find the logins you can choose to migrate. For Windows accounts, you'll be prompted to enter the associated Microsoft Entra domain name.

To proceed, select the logins you want to migrate and then click Migrate to start the login migration process.

To create a Database Migration Service instance, you have two options: create a new instance or reuse an existing one.

If you previously created a Database Migration Service instance using the Azure portal, you can't reuse it in the migration wizard in Azure Data Studio. You can only reuse an instance if you created it using Azure Data Studio.

To create a new instance of Database Migration Service, start by creating a new resource group in the Azure portal. This will contain your new instance of Database Migration Service.

Next, under Azure Database Migration Service, select Create new. In the Create Azure Database Migration Service window, enter a name for your Database Migration Service instance and select Create.

You'll then need to set up integration runtime. This involves testing the connection to your newly created Database Migration Service instance and validating that it's connected to your newly registered self-hosted integration runtime.

Here are the steps to create a new instance of Database Migration Service in more detail:

In the Assessment and Recommendations phase, you'll have the opportunity to review the results of your database assessment and receive personalized recommendations from Azure.

To get started, select the databases you want to assess in the Migrate to Azure SQL wizard. Then, move on to the next step.

The Assessment results and recommendations will provide you with valuable insights into your database's performance and suggest ways to improve it.

To get the most out of your self-hosted integration runtime, consider the following recommendations.

Use a single self-hosted integration runtime for multiple source SQL Server databases, as it can simplify your setup and reduce complexity.

Install only one instance of a self-hosted integration runtime on any single computer, to avoid potential conflicts and issues.

Associate only one self-hosted integration runtime with one instance of Database Migration Service, for streamlined management and maintenance.

Having the self-hosted integration runtime close to the data source can significantly reduce the time it takes for the self-hosted integration runtime to connect to the data source.

Here are some key considerations for installing the self-hosted integration runtime:

We recommend up to 10 concurrent database migrations per self-hosted integration runtime on a single computer, to ensure optimal performance and efficiency.

To increase the number of concurrent database migrations, you can scale out the self-hosted runtime to up to four nodes or create separate instances of the self-hosted integration runtime on different computers.

Configure the self-hosted integration runtime to auto-update and automatically apply any new features, bug fixes, and enhancements that are released, to ensure you have the latest and greatest functionality.

To run an assessment and get recommendations, you'll want to follow these steps. Select the databases you want to assess in the Migrate to Azure SQL wizard, then select Next.

You'll then see the assessment results and recommendations. Here's what you need to do next:

By following these steps, you'll be able to get the most out of your Azure assessment and recommendations.

The migration process using the Azure SQL Migration Wizard is a straightforward and efficient process. It involves six steps, each requiring user input, and a seventh step that displays a summary of the selected migration properties.

The first step is to choose the Azure account under which the migration will run. You can link an Azure account or choose from an existing account linked in Azure Data Studio.

In step two, you choose the database or databases to migrate. An assessment of the database is performed to determine which Azure SQL target is available for migration.

For step three, you select the Azure SQL target. You can choose between Azure SQL Managed Instance and SQL Server on an Azure Virtual Machine.

In step four, you select the Azure SQL Managed Instance target. You can view and select the database or databases for migration.

Step five allows you to specify the location of the database backups. You can use either a local network share (SMB) or an Azure Storage Blob Container.

Here is a breakdown of the required details for a network share:

In step six, you select the Resource Group containing the Azure Database Migration Service as well as the Azure Database Migration Service itself. The Azure Database Migration Service is used to copy backups from the local network share to Azure Blob storage before restoring to Azure SQL Managed Instance.

Step seven provides a summary of the migration. You can choose "Done" to begin the migration and view the migration details on the Migration Status screen.

After completing the SQL Azure migration, you'll want to verify that everything has been successfully transferred. Your target Azure SQL should now have the logins you selected to migrate, in addition to all the server roles from the source SQL Server, the associated user mappings, role memberships, and permissions copied over.

You can verify this by logging into the target Azure SQL using one of the logins migrated, and entering the same password as it had on the source SQL Server instance.

To ensure a smooth login process, check the option of Microsoft Entra ID - Password while logging into the target managed instance using the same password that the Windows account had on the source SQL Server. The username should be in the format of [email protected] (the Microsoft Entra domain name provided in Step 2 of the login migration wizard).

If you encounter issues during the SQL Azure Migration Wizard process, don't worry, it's not uncommon. The wizard may fail to connect to your SQL Server instance due to a firewall or antivirus software blocking the connection.

Make sure to check your SQL Server instance's firewall rules to allow incoming connections from the SQL Azure Migration Wizard. Also, temporarily disable your antivirus software to ensure a smooth migration process.

The wizard may also fail to transfer data due to a mismatch in database schema or data types. Double-check your database schema and data types to ensure they match the requirements of the SQL Azure Migration Wizard.

Monitoring the migration process is crucial to ensure a smooth transition. You can monitor the process for each login by selecting the link under the login's Migration Status.

The migration details page displays the different stages involved in the login migration process. This is where you can track the progress of your migration.

To get a clear picture, let's take a look at the different stages involved in the login migration process.

By monitoring these stages, you can identify any potential issues and take corrective action.

Database migrations can be a complex process, and it's essential to be aware of the known issues that might arise. Here are some key limitations to keep in mind.

Database Migration Service doesn't support overwriting existing databases in your target instance of Azure SQL Managed Instance, Azure SQL Database, or SQL Server on Azure Virtual Machines.

If you're planning to migrate to SQL Server on Azure Virtual Machines, you should be aware that SQL Server 2008 and earlier as target versions aren't supported. This means you'll need to upgrade your SQL Server version before migrating.

Database Migration Service also doesn't support configuring high availability and disaster recovery on your target to match the source topology. This is something you'll need to set up separately after the migration is complete.

You can't use an existing self-hosted integration runtime that was created in Azure Data Factory for database migrations with Database Migration Service. Instead, you'll need to create a new self-hosted integration runtime using the Azure SQL Migration extension for Azure Data Studio.

Here are some additional limitations to be aware of: