Azure Data Studio - Integration runtime pre-requisites

Mohammed Thahif BK 341 Reputation points
2023-10-05T02:10:09.09+00:00

Hello Everyone,

We are migrating SQL server 2016 from on-prem to Azure SQL MI(Business Critical tier). Due to lack of private connectivity between the source and the destination, we have decided to use Azure Data Studio tool with SQL migration extension installed.

The source backups are stored on the same server as data studio/runtime integration server. Whilst we have sorted out most of the pre-requisites around integration server, we were not successful in registering them.

We have followed below guides to meet the pre-requisites interm of ports, we still have issues.

https://video2.skills-academy.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online-ads#prerequisites

So far we have whitelisted below URLs in proxy at source location.

*.frontend.clouddatahub.net

download.microsoft.com

*.core.windows.net

Some other guide talks about whitelisting entire Azure CIDR range for that region also couple of other URLs such as below.

*.servicebus.windows.net

*.core.windows.net

*.database.windows.net

Can someone please help us to get the correct port & firewall requirements for ADS & integration runtime in such restrictive environment? Your help is highly appreciated. User's image

Azure SQL Database
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,502 questions
Azure Data Studio
Azure Data Studio
A cross-platform database tool for data professionals using on-premises and cloud data platforms on Windows, macOS, and Linux.
106 questions
Accepted answer
  1. ShaktiSingh-MSFT 14,276 Reputation points Microsoft Employee
    2023-10-05T08:18:52.8266667+00:00

    Hi
    Mohammed Thahif BK     •,

    Welcome to Microsoft Q&A forum and thanks for using Azure Services.

    As I understand, you are unable to perform migration of SQL server 2016 from on-prem to Azure SQL MI.

    Firstly, if you're using the Azure Database Migration Service for the first time, ensure that Microsoft.DataMigration resource provider is registered in your subscription. You can follow the steps to register the resource provider.

    Next, when you're using a self-hosted integration runtime, make sure that the machine where the runtime is installed can connect to the source SQL Server instance and the network file share where backup files are located. Outbound port 445 should be enabled to allow access to the network file share. Also see recommendations for using a self-hosted integration runtime

    Recommendations for using a self-hosted integration runtime for database migrations

    • Use a single self-hosted integration runtime for multiple source SQL Server databases.
    • Install only one instance of a self-hosted integration runtime on any single computer.
    • Associate only one self-hosted integration runtime with one instance of Database Migration Service.
    • The self-hosted integration runtime uses resources (memory and CPU) on the computer it's installed on. Install the self-hosted integration runtime on a computer that's separate from your source SQL Server instance. But the two computers should be in close proximity. 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.
    • Use the self-hosted integration runtime only when you have your database backups in an on-premises SMB network share. A self-hosted integration runtime isn't required for database migrations if your source database backups are already in the storage blob container.
    • We recommend up to 10 concurrent database migrations per self-hosted integration runtime on a single computer. To increase the number of concurrent database migrations, 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. For more information, see Self-hosted integration runtime auto-update.

    The Azure SQL migration extension for Azure Data Studio no longer requires specific configurations on your Azure Storage account network settings to migrate your SQL Server databases to Azure. However, depending on your database backup location and desired storage account network settings, there are a few steps needed to ensure your resources can access the Azure Storage account. See the following table for the various migration scenarios and network configurations:
    Enabled from all networks No extra steps No extra steps
    Enabled from selected virtual networks and IP addresses See 1a See 2a
    Enabled from selected virtual networks and IP addresses + private endpoint See 1b See 2b

    Hope this information helps. Let us know if we have missed anything.

    Thanks

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mohammed Thahif BK 341 Reputation points
    2023-10-23T06:13:50.6066667+00:00

    *.servicebus.windows.net was the culprit which was not properly called out. Thanks for the help anyways. Thread can be closed.

    0 comments
  2. ShaktiSingh-MSFT 14,276 Reputation points Microsoft Employee
    2023-10-25T05:05:29.3866667+00:00

    Hi
    Mohammed Thahif BK     •,

    I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

    Solution:

    *.servicebus.windows.net was not called out properly and was the reason of issue.

    Thanks for your support.

    0 comments