Azure Managed Instance Replication cannot Connect to On Prem Subscriber

Rich Vieira 41 Reputation points
2022-10-14T12:34:09.57+00:00

Hello,
I am trying to setup Replication from our Azure Managed Instance (Distributor and Publisher) to an On-Prem SQL Server. The Working Directory is an Azure Files Share which the Instance connects too and places the snapshot. When I add the Subscriber though for the Push method the Instance can never connect to the Subscriber and if I do a Pull Method the Subscriber cannot connect to the Azure File Share as it is not a Login and Password method but Access Key. I believe the issue with the Instance connecting to the Subscriber is that the SQLNAME is SERVER1 instead of SERVER1.MYDOMAIN.COM. When I try to add a Linked Server I need to specify the FQDN so the Instance knows how to get to the Subscriber. However, when creating Replication I need to use the Real SERVERNAME (Yes I can change it but was wondering if there is other options). Is there something I did not setup properly in the Azure Networking?

Thanks

Azure SQL Database
{count} votes

Accepted answer
  1. GeethaThatipatri-MSFT 27,987 Reputation points Microsoft Employee
    2022-10-18T11:23:57.473+00:00

    Hi,@Rich Vieira please do setup custom DNS Resolve private domain names - Azure SQL Managed Instance | Microsoft Learn
    Backend nodes that are hosting SQL Managed Instance cannot resolve SERVER1. So, when configuring replication, so need to use FQDN of the SQL Server (e.g. SERVER1.MYDOMAIN.COM) or an IP address of the SQL Server, if it’s a static address.

    Regards
    Geetha

    Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.


1 additional answer

Sort by: Most helpful
  1. GeethaThatipatri-MSFT 27,987 Reputation points Microsoft Employee
    2022-10-14T15:18:43.017+00:00

    @Rich Vieira The Distribution Agent is failing because the Managed Instance cannot resolve the name of the on-premise SQL instance. With Push subscriptions, you need to make sure that the name resolution on MI is able to resolve the FQDN of the on-premise Subscriber.

    Configure a custom DNS solution for your VNET: Check here . This is the preferred solution.
    Create the subscription for the FQDN of the subscriber over a public IP address. You may need to open the inbound traffic on the on-premise firewall to allow the traffic from the Managed Instance. Find the IP address through Check here . This IP address may change over time though.
    You can check that the MI can connect to the subscriber by creating a Linked Server on the MI to the Subscriber.
    Once this is set up correctly, you can then use the public FQDN as the Subscriber name when creating the Push subscription.
    Note: If you had added or changed the DNS server after the Managed Instance had been created, then MI might not have picked up that change. You would need to flush the DNS cache for MI first before it would be able to work with the new DNS server. The easiest way to flush the DNS cache would be to scale the MI. Scaling implies a restart of the Managed Instance and reading fresh settings.

    Regards
    Geetha