Azure Data Studio Data Migration Process Explained

Author

Reads 874

Abstract Blue Background
Credit: pexels.com, Abstract Blue Background

Azure Data Studio offers a simplified data migration process that streamlines the transfer of data between different databases.

You can use Azure Data Studio's built-in tools to connect to various database systems, including SQL Server, Azure SQL Database, and Oracle.

The process begins by selecting the source and target databases, which can be done through the "Connect to Database" option.

Data types, such as integers and strings, are automatically detected and converted during the migration process.

Azure Data Studio allows for the migration of large datasets, with no limit on the size of the data being transferred.

The tool also supports the migration of database objects, such as tables and stored procedures.

Azure Data Studio's data migration process is a powerful tool for transferring data between different databases.

Prerequisites

To get started with Azure Data Studio data migration, you'll first need to meet some prerequisites. You'll need to download and install Azure Data Studio, which can be bundled with the latest versions of SQL Server Management Studio or downloaded independently.

Credit: youtube.com, How to migrate SQL Server to Azure SQL Database offline using Azure Data Studio | Data Exposed

The next step is to install the Azure SQL Migration extension from Azure Data Studio Marketplace. This will give you access to the necessary tools for migrating your database schema and data.

You'll also need an Azure account, which should be assigned to one of the built-in roles such as the SQL Server Contributor, SQL Security Administrator, or the Contributor role. Alternatively, you can assign a custom role.

To create a target instance of Azure SQL Database, simply follow the instructions provided by Azure. Make sure that the SQL Server login that connects to the source SQL Server instance is a member of the db_datareader role and that the login for the target SQL Server instance is a member of the db_owner role.

If you're using Azure Database Migration Service for the first time, make sure that the Microsoft.DataMigration resource provider is registered in your subscription. This will ensure that you have access to the necessary resources for migrating your database schema and data.

Here's a summary of the prerequisites:

  • Download and install Azure Data Studio.
  • Install the Azure SQL Migration extension from Azure Data Studio Marketplace.
  • Have an Azure account assigned to one of the built-in roles.
  • Create a target instance of Azure SQL Database.
  • Migrate the database schema from source to target by using the SQL Server dacpac extension or the SQL Database Projects extension in Azure Data Studio.
  • Register the Microsoft.DataMigration resource provider in your subscription.

By meeting these prerequisites, you'll be well on your way to successfully migrating your database schema and data using Azure Data Studio.

Installation

Credit: youtube.com, How to Migrate SQL Server Database with Azure Data Studio

To install the Azure SQL migration extension in Azure Data Studio, you have two options: connected mode installation or offline mode.

You can install the extension in connected mode by opening the extensions manager in Azure Data Studio, typing in Azure SQL Migration in the search bar, and selecting Install. This will add the extension to the extension list, allowing you to connect to the SQL Server instance and access the Azure SQL Migration extension landing page.

Alternatively, if you're working on a disconnected computer, you'll need to download the installation files manually. This involves downloading Azure Data Studio, the Azure SQL migration extension VSIX file, and the SQL tool service binaries on a machine connected to the Internet, and then copying the files to the disconnected machine.

To get started with the offline installation, you'll need to download Azure Data Studio for the disconnected machine, and then download the latest Azure SQL migration extension VSIX file from the specified location.

Install

A person working on a laptop with a USB flash drive connected, representing technology and data storage.
Credit: pexels.com, A person working on a laptop with a USB flash drive connected, representing technology and data storage.

Installing the Azure SQL migration extension in Azure Data Studio is a straightforward process. You can choose between connected mode installation and offline mode installation, depending on your internet connection.

To install the extension in connected mode, open the extensions manager in Azure Data Studio and search for "Azure SQL Migration" in the search bar. Select the extension and click on "Install" to begin the installation process.

The installation process involves a few simple steps: open the extensions manager, search for "Azure SQL Migration", select the extension, and click on "Install". You can see the Azure SQL migration extension in the extension list once installed.

In offline mode, you need to download the Azure SQL migration extension VSIX file and the SQL tool service binaries on a machine connected to the internet. You can download Azure Data Studio for the disconnected machine from the official website.

Here are the steps to download the necessary files:

  1. Download Azure Data Studio for the disconnected machine.
  2. Download the latest Azure SQL migration extension VSIX file from the following location: https://sqlopsextensions.blob.core.windows.net/extensions/sql-migration/sql-migration-1.5.4.vsix.
  3. Download the required SQL tool service binaries.

Once you have downloaded the files, you can install them on the disconnected machine by copying them to the machine and installing Azure Data Studio.

Create Instance

Credit: youtube.com, Installing a new SQL Server 2019 instance on existing server

To create a Database Migration Service instance, you can either create a new one or reuse an existing one. If you created the instance earlier using Azure Data Studio, you can reuse it in the migration wizard.

To create a new instance, you'll need to create a new resource group to contain it. This is a straightforward process that involves selecting the "Create new" option under Azure Database Migration Service.

You'll then need to enter a name for your Database Migration Service instance and select "Create". After that, you'll need to set up integration runtime, which involves testing the connection to the newly registered self-hosted integration runtime.

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

  1. Create a new resource group to contain a new instance of Database Migration Service.
  2. Select "Create new" under Azure Database Migration Service.
  3. Enter a name for your Database Migration Service instance and select "Create".
  4. Test connection to validate that the newly created Database Migration Service instance is connected to the newly registered self-hosted integration runtime.

Configure Settings

In Step 3 of the Migrate to Azure SQL wizard, you need to configure the migration settings for your target Azure SQL Database instance.

To do this, select Offline migration in Step 4: Migration mode and then click Next. If no tables are selected or if a username and password aren't entered, the Next button won't be available.

Credit: youtube.com, SQL Server migration to Azure using Azure Data Studio to assess, recommend Sku and migrate.

You can review your table selections in the Data source configuration step. If everything looks good, select Next to move to the next step in the migration wizard.

The Database Migration Service allows you to migrate both database schema and data. Alternatively, you can use tools like the SQL Server dacpac extension or the SQL Database Projects extension in Azure Data Studio to migrate schema before selecting the list of tables to migrate.

Related reading: Migrate Data to Azure

Migration Process

The migration process in Azure Data Studio is quite straightforward. You start by choosing the Azure account under which the migration will run, either by linking a new account or selecting an existing one already linked to Azure Data Studio.

To begin the migration, you need to choose the database or databases you want to migrate. This is done in step two of the migration wizard.

The assessment process uses the Microsoft.SqlServer.TransactSql.ScriptDom namespace to determine which Azure SQL target is available for migration.

Credit: youtube.com, Azure Data Studio vs Data Migration Assistant

You can run your migration online (for migrations that require minimal downtime) or offline (for migrations where downtime persists throughout the migration) depending on your business requirements.

In step seven of the migration wizard, you'll find a summary of the migration. Choose "Done" in the lower right corner to begin the migration.

Here's a step-by-step breakdown of the migration process:

The Database Migration Status section of the Azure SQL Migration tab will indicate the number of migrations that are in progress. You can choose "Database migration in progress" to view migration details on the Migration Status screen.

Once cutover is completed, the Migration Status will indicate "Succeeded". The Database Migration Status section on the Azure SQL Migration screen will also update accordingly.

Architecture and Integration

In Azure Data Studio, a self-hosted integration runtime is recommended for database migrations, especially when dealing with multiple source SQL Server databases. Use a single self-hosted integration runtime for multiple source SQL Server databases.

Credit: youtube.com, Azure Migration Using Data Studio Online and Offline With Integration Run Time and Blob Storage.

To optimize performance, install only one instance of a self-hosted integration runtime on any single computer. This will help prevent resource conflicts and ensure a smooth migration process.

Here are some key considerations for implementing a self-hosted integration runtime:

  • Install the self-hosted integration runtime on a computer separate from your source SQL Server instance, but in close proximity to reduce connection time.
  • Use the self-hosted integration runtime only when your database backups are in an on-premises SMB network share.
  • Configure the self-hosted integration runtime to auto-update and automatically apply new features, bug fixes, and enhancements.

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

Architecture of the

The architecture of a system is crucial for its integration. It's like building a house, you need a solid foundation to support the entire structure.

The architecture of a system typically consists of a combination of hardware and software components. These components work together to provide the necessary functionality and performance.

In a typical system architecture, the hardware components include the central processing unit (CPU), memory, and storage devices. The CPU executes instructions, memory stores data, and storage devices hold the operating system and applications.

If this caught your attention, see: Azure Data Architect

Credit: youtube.com, What is System Architecture? [Intro to Enterprise Architecture, Integration, and BI]

The software components include the operating system, application software, and middleware. The operating system manages the hardware resources, application software provides the functionality, and middleware acts as a bridge between the operating system and application software.

A well-designed system architecture can improve the overall performance, scalability, and maintainability of the system. It's essential to consider the system's requirements, constraints, and goals when designing the architecture.

A good system architecture should be modular, flexible, and adaptable to changing requirements. This allows for easier maintenance, upgrades, and integration with other systems.

In some systems, the architecture may also include a network component, which enables communication between different components and systems. This is particularly important in distributed systems, where components are located on different machines or in different locations.

Expand your knowledge: Azure Data Factory Architecture

Self-Hosted Integration Runtime Recommendations

When you're planning to use a self-hosted integration runtime for database migrations, here are some key recommendations to keep in mind.

Use a single self-hosted integration runtime for multiple source SQL Server databases. This helps simplify the process and reduces the administrative overhead.

Credit: youtube.com, Azure Data Factory Self-hosted Integration Runtime Tutorial | Connect to private on-premises network

The self-hosted integration runtime uses resources like memory and CPU on the computer it's installed on, so it's essential to install it on a separate computer from your source SQL Server instance.

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

If your source database backups are already in the storage blob container, you don't need a self-hosted integration runtime for database migrations.

The recommended limit for concurrent database migrations per self-hosted integration runtime on a single computer is 10.

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

It's a good idea to configure the self-hosted integration runtime to auto-update and automatically apply any new features, bug fixes, and enhancements that are released.

Here are the key recommendations in a concise list:

  • Use a single self-hosted integration runtime for multiple source SQL Server databases.
  • Install the self-hosted integration runtime on a separate computer from your source SQL Server instance.
  • Use the self-hosted integration runtime only when your source database backups are in an on-premises SMB network share.
  • Limit concurrent database migrations to 10 per self-hosted integration runtime on a single computer.
  • Scale out the self-hosted runtime to up to four nodes or create separate instances to increase concurrent database migrations.
  • Configure the self-hosted integration runtime to auto-update and apply new features and bug fixes.

Troubleshooting

Credit: youtube.com, Assess, Get Recommendations & Migrate SQL Server to Azure using Azure Data Studio | Data Exposed

Azure Data Studio provides a robust feature to migrate data from one database to another, but sometimes you may encounter issues during the process. If the migration fails due to a mismatch in database versions, try upgrading the destination database to the same version as the source database.

A common error during data migration is a timeout due to large data volumes. To resolve this, you can increase the timeout period or break the migration into smaller chunks.

However, if the issue persists, verify that the source and destination databases are properly configured and connected. Ensure that the required permissions are granted to the Azure Data Studio user account.

Monitor Progress

To monitor database migration progress, you can use the Azure portal.

In the Azure portal, search for your instance of Database Migration Service by using the resource name. This will help you locate the specific migration you want to track.

To view the details of your database migrations, select Monitor migrations in the Database Migration Service instance overview. This will give you a clear picture of the migration progress.

Known Issues and Limitations

Creative studio portrait of a woman with red hair and unique eyewear against a blue backdrop.
Credit: pexels.com, Creative studio portrait of a woman with red hair and unique eyewear against a blue backdrop.

Database Migration Service has its share of limitations that can impact your migration process. Here are some key issues to be aware of:

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.

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

If you're using SQL Server 2014 or SQL Server 2012, you'll need to store your source database backup files in an Azure storage blob container instead of using a network share. This is because page blobs are required, and block blobs are only supported in SQL Server 2016 and later versions.

Database Migration Service also has limitations when it comes to server objects. For a complete list of metadata and server objects that you need to move, refer to the detailed information available in Manage Metadata When Making a Database Available on Another Server.

Recommended read: Azure Data Sharing

Man in White Dress Shirt Analyzing Data Displayed on Screen
Credit: pexels.com, Man in White Dress Shirt Analyzing Data Displayed on Screen

Here are some specific server objects that aren't supported:

  • Computed columns don't get migrated.
  • Azure SQL Database table names with double-byte characters currently aren't supported for migration.
  • Tables with large blob columns might fail to migrate due to timeout.
  • Database names with SQL Server reserved are currently not supported.
  • Database names that include semicolons are currently not supported.

Lastly, keep in mind that migration speed heavily depends on the target Azure SQL Database SKU and the self-hosted Integration Runtime host.

Pricing and Availability

Azure Database Migration Service is free to use with the Azure SQL Migration extension for Azure Data Studio, allowing you to migrate multiple SQL Server databases at no charge.

No data movement or data ingress costs are assessed when you migrate your databases from an on-premises environment to Azure, making the process even more cost-effective.

However, you might incur bandwidth charges depending on your bandwidth provider and routing scenario if the source database is moved from another region or from an Azure virtual machine.

To take advantage of this free service, you'll need to use a virtual machine or an on-premises server to install Azure Data Studio.

A self-hosted integration runtime is also required to access database backups from your on-premises network share.

Credit: youtube.com, Migrating to SQL: Get Started w/ Azure SQL Readiness Assessments & Migrations from ADS (Ep. 6)

Here are the key facts to keep in mind:

  • Azure Database Migration Service is free to use with the Azure SQL Migration extension for Azure Data Studio.
  • No data movement or data ingress costs are assessed when migrating from an on-premises environment to Azure.
  • Bandwidth charges may apply for migrations from other regions or Azure virtual machines.
  • A virtual machine or on-premises server is required for Azure Data Studio installation.
  • A self-hosted integration runtime is needed for on-premises network share access.

Pricing

Azure Database Migration Service is free to use with the Azure SQL Migration extension for Azure Data Studio, allowing you to migrate multiple SQL Server databases at no charge.

No data movement or data ingress costs are assessed when you migrate your databases from an on-premises environment to Azure. This is a significant cost savings, especially for large databases.

If the source database is moved from another region or from an Azure virtual machine, you might incur bandwidth charges depending on your bandwidth provider and routing scenario.

To use Azure Data Studio, you'll need to install it on a virtual machine or an on-premises server. This might require some technical expertise, but it's a one-time setup process.

A self-hosted integration runtime is required to access database backups from your on-premises network share. This ensures secure and reliable access to your data.

Region Availability

Azure Database Migration Service is available in various regions, but it's essential to check which ones support database migrations.

Credit: youtube.com, Understanding Microsoft Azure Availability Zones!

You can find the list of supported regions on the Azure website under "Azure products available by region."

It's free to use Azure Database Migration Service with the Azure SQL Migration extension for Azure Data Studio, but you should check the pricing details to understand any potential costs associated with data movement or bandwidth charges.

Desiree Feest

Senior Assigning Editor

Desiree Feest is an accomplished Assigning Editor with a passion for uncovering the latest trends and innovations in technology. With a keen eye for detail and a knack for identifying emerging stories, Desiree has successfully curated content across various article categories. Her expertise spans the realm of Azure, where she has covered topics such as Azure Data Studio and Azure Tools and Software.

Love What You Read? Stay Updated!

Join our community for insights, tips, and more.