Replication from Managed Instance back to on-prem SQL 2017 - Detailed tutorial including networking? RRS feed

  • Question

  • Hi All

    I've been fighting with this for a couple of days & can't get it working due to the SQL MI not able to see the on-prem SQL host when its running the Push Subscription. I suspect this is really a networking/VNet issue but am getting tied in knots... 


    I've successfully set up Transactional Replication with my on-prem SQL2017 DB being the Distributor & Publisher with a Push Subscription to a DB on my SQL MI and everything worked fine.

    I wanted to reverse this and make the SQL MI DB the Distributor & Publisher with a Push Subscription to my on-prem DB but just can't get the Subscription to work. 

    I can configure the SQL MI  successfully as the Distributor & Publisher and the initial snapshot lands in the Azure Storage Account File Share as configured but when the Push Subscription on the SQLMI runs it just fails unable to connect to the on-prem SQL host.

    When I go through the "create subscription" wizard in SSMS (connected to the SQLMI) I can set up the push subscription & successfully connect to the target on-prem server but when I look at the details of the linked server it creates I can tell it'll never work because it's created as an OLE DB Provider linked server with no provider string/network addressing details just the target instance name in the format Machine\InstanceName so it's obvious the SQL MI won't be able to locate the on-prem instance across the internet/VPN connection.

    Sure enough when the Push Subscription tries to initialize it fails with the generic "SQL Server Error 2: ...Server is not found or not accessible..." error.

    I have tried creating a stand alone linked server in the SQL MI back to my on-prem instance to see if I can work out how it should be configured and have thus tried a FQDN in the format "Machine\Instance.<corporate domain>.com"; "<public ip address>\Instance" but am aware I'm probably picking up the public IP address of our internet gateway.

    Any pointer on how to address on-prem hosts much appreciated!

    Thanks in advance.


    Thursday, July 18, 2019 10:46 AM

All replies

  • May be a basic question but how are you connecting to Azure MI from on-premise ? And when you said you reversed the replication how did you do that, did you created the replication from MI to on-prem from scratch ?



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles


    Thursday, July 18, 2019 5:15 PM
  • Hi Morgan,

    I am providing you some resources to help you as requested. Are you using a private IP Address or Public?

    • Azure SQL MI Replication – New Possibilities for Hybrid Environments (link).
    • Configure replication in an Azure SQL Database managed instance database (link)
    • Transactional replication with single, pooled, and instance databases in Azure SQL Database (link)
    • Connectivity architecture for a managed instance in Azure SQL Database (link)

    Additionally, did you create a VPN Tunnel between your on-premise environment to the Azure VNet where the Managed Instance is deployed? (link) This is potentially required if you want to access the Private IP service endpoint of the Managed Instance, if it is not being published publicly.



    Monday, July 22, 2019 11:52 PM
  • Want to follow up on this to see if you have additional questions, or need additional assistance? Please let us know and we can continue the dialog. 



    Monday, July 29, 2019 8:56 PM