S
smartura
When migrating large databases to Azure SQL DB, an issue often encountered is slower than desired SQL DB migration performance. This blog details how to investigate and pinpoint performance issues to improve the speed of your Azure SQL DB migration. Since this article focuses on migration performance, it is assumed that you are familiar with the process of successfully migrating using Azure Database Migration Service (DMS).
A SQL DB migration consists of three separate pieces that each play a role in impacting migration performance:
The first step to improving performance is determining which piece (or pieces) are causing a bottleneck in your migration pipeline.
Source SQL Server
A) Index columns
A SQL DB migration using DMS typically copies data one row at a time. However, we have a trick up our sleeve: we can parallelize tables with indexes. This means that we can copy different sections of the same table at the same time. Parallelization will automatically be enabled on your table if it has a clustered index and the index column is a supported data type. Behind the scenes, Database Migration Service (DMS) uses Azure Data Factory's "Copy" activity, meaning that at the time of writing this article only the following index column data types are supported for parallelization:
You can read more about the ADF copy activity at the following link: Configure Azure SQL Database in a copy activity - Microsoft Fabric | Microsoft Learn. You can view whether a table is being parallelized in the Azure portal by clicking the "Monitor Migrations" button and selecting your ongoing migration. In the screenshot below, you can see tables with parallel copy type of "Dynamic range", this means they can be parallelized.
Keep in mind that a table without indexes cannot be parallelized. Instead, it will be copied one row at a time, linearly.
If your source table does not have any indexes, you can improve migration performance by the following:
It is completely understandable if you cannot or do not wish to modify your source database, but it is important to note the role that indexes play in migration performance.
B) Hardware limitations
The hardware of the source server will of course also play a role in the migration's performance. Since you are migrating away from the source server, it is unlikely you'd want to upgrade the hardware. However, it can be helpful to open task manager on the source server and analyze the performance of the CPU, memory, disk IO latency and network. If you find that source server is reaching its limits, that may in fact be your bottleneck. After all, you can't copy data faster than the source will allow you.
Self-Hosted Integration Runtime
The integration runtime (IR) is the compute infrastructure used by Azure Data Factory and provides us the data movement capabilities to migrate to Azure. It can be downloaded, installed on a machine and registered with your Database Migration Service. The integration runtime service is now being hosted on the machine, hence where the term "self-hosted integration runtime" (SHIR) comes from. All of the data being copied throughout the migration will flow through your SHIR. This is considered an SHIR "node" and is the engine driving your data copying activities.
A) SHIR node
It may seem intuitive to download the integration runtime software onto your source SQL server machine, however this is not recommended. An IR on the same machine as SQL server will mean that both components will be competing for hardware resources, in effect limiting your migration speed. On that same note, it is not recommended that you install the integration runtime software on your local machine that you use for development. This is especially discouraged if your local computer is connected to the internet wirelessly.
To achieve optimal migration performance, it is highly recommended that you dedicate a machine exclusively to running the SHIR. This machine should have a fast and reliable connection to your source and target database. Therefore, it is recommended that you use a machine on your local network connected via ethernet or a Virtual Machine on Azure.
B) Monitoring SHIR node performance
You can monitor SHIR performance from Azure portal by navigating to your DMS instance and clicking "Integration runtime" on the lefthand side. This will list every SHIR node registered with your DMS instance and metrics such as concurrent jobs, available memory, CPU utilization, and network speeds.
C) Registering multiple SHIR nodes.
If you notice that:
You will likely benefit from registering more than one SHIR node. You can do this by installing the integration runtime software on a separate machine from your existing SHIR. When running the SHIR software for the first time, register the machine with an IR key from your DMS instance. You can find your IR keys by navigating to your DMS instance and clicking "Integration runtime" on the lefthand side, then clicking "Configure integration runtime".
It is important to note that each node must be on its own, individual machine. You may not host two nodes from the same machine. At this point in time, you may register up to 4 IR nodes at once.
Multiple registered IR nodes can improve copy speed in the following ways:
As with the other components of the migration, a slow target Azure SQL DB will cause a bottleneck and contribute to a slower overall migration. On the flip side, an underutilized target Azure SQL DB will mean you are migrating data at a slower speed than you could potentially achieve.
During the migration, you may wish to monitor Azure SQL DB from the Azure portal to ensure it’s operating efficiently. You can do so by navigating to your Azure SQL DB in the Azure portal, clicking on "Metrics" on the lefthand side, then adding metrics to analyze (shown below). You may notice periods of high data/log IO, and periods of high CPU usage (both average and max metrics). Periods of high IO are caused by data being copied to target database, meanwhile indexing contributes to periods of high CPU usage.
SQL DB Metrics in Azure portal:
In the example showing above, the average log IO percentage peaks at 21.25% and its max reaches 66%, meaning that the SQL DB is more than enough to run this migration. If your target SQL DB is reaching its limits of CPU, data/log IO or other metrics, you may wish to upgrade the SQL DB to a higher SKU/service tier. To determine which SQL DB SKU/service tier to upgrade to, refer to the following document: Single database vCore resource limits - Azure SQL Database | Microsoft Learn. In many cases, users scale up to a higher SKU of SQL DB during the migration, then scale down to a more reasonable SKU after the migration has completed.
Conclusion
In summary, your migration will only be as fast as the slowest component. Knowing where to look can help you ensure your migration is running as efficiently as possible. In the event a bottleneck is found, we can use things such as index columns, multiple, more reliable integration runtimes and a higher target SQL DB SKU to help alleviate some of these bottlenecks.
Helpful links
To troubleshoot DMS migration failing, visit Known issues, limitations, and troubleshooting - Azure Database Migration Service.
To learn more about DMS, visit Azure Database Migration Service | Microsoft Azure.
Continue reading...
A SQL DB migration consists of three separate pieces that each play a role in impacting migration performance:
- Source SQL Server
- Self-hosted integration runtime
- Target SQL DB
The first step to improving performance is determining which piece (or pieces) are causing a bottleneck in your migration pipeline.
Source SQL Server
A) Index columns
A SQL DB migration using DMS typically copies data one row at a time. However, we have a trick up our sleeve: we can parallelize tables with indexes. This means that we can copy different sections of the same table at the same time. Parallelization will automatically be enabled on your table if it has a clustered index and the index column is a supported data type. Behind the scenes, Database Migration Service (DMS) uses Azure Data Factory's "Copy" activity, meaning that at the time of writing this article only the following index column data types are supported for parallelization:
- int
- smallint
- bigint
- date
- smalldatetime
- datetime
- datetime2
- datetimeoffset.
You can read more about the ADF copy activity at the following link: Configure Azure SQL Database in a copy activity - Microsoft Fabric | Microsoft Learn. You can view whether a table is being parallelized in the Azure portal by clicking the "Monitor Migrations" button and selecting your ongoing migration. In the screenshot below, you can see tables with parallel copy type of "Dynamic range", this means they can be parallelized.
Keep in mind that a table without indexes cannot be parallelized. Instead, it will be copied one row at a time, linearly.
If your source table does not have any indexes, you can improve migration performance by the following:
- Creating a clustered index on a column that is already in the table.
- Creating a temporary column that will be used as a clustered index only for the purposes of the migration. This temporary column can simply contain a unique number.
It is completely understandable if you cannot or do not wish to modify your source database, but it is important to note the role that indexes play in migration performance.
B) Hardware limitations
The hardware of the source server will of course also play a role in the migration's performance. Since you are migrating away from the source server, it is unlikely you'd want to upgrade the hardware. However, it can be helpful to open task manager on the source server and analyze the performance of the CPU, memory, disk IO latency and network. If you find that source server is reaching its limits, that may in fact be your bottleneck. After all, you can't copy data faster than the source will allow you.
Self-Hosted Integration Runtime
The integration runtime (IR) is the compute infrastructure used by Azure Data Factory and provides us the data movement capabilities to migrate to Azure. It can be downloaded, installed on a machine and registered with your Database Migration Service. The integration runtime service is now being hosted on the machine, hence where the term "self-hosted integration runtime" (SHIR) comes from. All of the data being copied throughout the migration will flow through your SHIR. This is considered an SHIR "node" and is the engine driving your data copying activities.
A) SHIR node
It may seem intuitive to download the integration runtime software onto your source SQL server machine, however this is not recommended. An IR on the same machine as SQL server will mean that both components will be competing for hardware resources, in effect limiting your migration speed. On that same note, it is not recommended that you install the integration runtime software on your local machine that you use for development. This is especially discouraged if your local computer is connected to the internet wirelessly.
To achieve optimal migration performance, it is highly recommended that you dedicate a machine exclusively to running the SHIR. This machine should have a fast and reliable connection to your source and target database. Therefore, it is recommended that you use a machine on your local network connected via ethernet or a Virtual Machine on Azure.
B) Monitoring SHIR node performance
You can monitor SHIR performance from Azure portal by navigating to your DMS instance and clicking "Integration runtime" on the lefthand side. This will list every SHIR node registered with your DMS instance and metrics such as concurrent jobs, available memory, CPU utilization, and network speeds.
C) Registering multiple SHIR nodes.
If you notice that:
- Your current IR hardware usage is nearing its limits.
- Source and target hardware are not operating at full capacity.
You will likely benefit from registering more than one SHIR node. You can do this by installing the integration runtime software on a separate machine from your existing SHIR. When running the SHIR software for the first time, register the machine with an IR key from your DMS instance. You can find your IR keys by navigating to your DMS instance and clicking "Integration runtime" on the lefthand side, then clicking "Configure integration runtime".
It is important to note that each node must be on its own, individual machine. You may not host two nodes from the same machine. At this point in time, you may register up to 4 IR nodes at once.
Multiple registered IR nodes can improve copy speed in the following ways:
- A table with indexes can be split up and each IR will copy its own piece of the same table.
- Assuming we have multiple tables (with indexes or not), each IR node can copy one table, in effect copying multiple different tables simultaneously.
As with the other components of the migration, a slow target Azure SQL DB will cause a bottleneck and contribute to a slower overall migration. On the flip side, an underutilized target Azure SQL DB will mean you are migrating data at a slower speed than you could potentially achieve.
During the migration, you may wish to monitor Azure SQL DB from the Azure portal to ensure it’s operating efficiently. You can do so by navigating to your Azure SQL DB in the Azure portal, clicking on "Metrics" on the lefthand side, then adding metrics to analyze (shown below). You may notice periods of high data/log IO, and periods of high CPU usage (both average and max metrics). Periods of high IO are caused by data being copied to target database, meanwhile indexing contributes to periods of high CPU usage.
SQL DB Metrics in Azure portal:
In the example showing above, the average log IO percentage peaks at 21.25% and its max reaches 66%, meaning that the SQL DB is more than enough to run this migration. If your target SQL DB is reaching its limits of CPU, data/log IO or other metrics, you may wish to upgrade the SQL DB to a higher SKU/service tier. To determine which SQL DB SKU/service tier to upgrade to, refer to the following document: Single database vCore resource limits - Azure SQL Database | Microsoft Learn. In many cases, users scale up to a higher SKU of SQL DB during the migration, then scale down to a more reasonable SKU after the migration has completed.
Conclusion
In summary, your migration will only be as fast as the slowest component. Knowing where to look can help you ensure your migration is running as efficiently as possible. In the event a bottleneck is found, we can use things such as index columns, multiple, more reliable integration runtimes and a higher target SQL DB SKU to help alleviate some of these bottlenecks.
Helpful links
To troubleshoot DMS migration failing, visit Known issues, limitations, and troubleshooting - Azure Database Migration Service.
To learn more about DMS, visit Azure Database Migration Service | Microsoft Azure.
Continue reading...