none
Azure Data Sync with Azure SQL Managed instance RRS feed

  • Question

  • Hello,

    In my current hosted SQL environment, we use Azure Data Sync to sync data to a Azure SQL DB in another subscription.

    Now moving to Azure SQL Managed Instance, I know this is not supported yet. Is there a way to got around this? Basically i need to be able to sync some of the table to another Azure SQL DB (Single DB).

    Please advice

    Thanks,

    Aloysius


    Wednesday, October 17, 2018 11:51 PM

All replies

  • Hi Aloysius,

    A very simple solution (for a one time event...if you need to move some data) is to back-up the desired tables to Azure Blob Storage and then import that back-up into your Managed Instance: Database migration

    Please let us know if you have any additional questions.

    Regards,

    Mike


    Thursday, October 18, 2018 12:25 AM
    Moderator
  • Hello,


    If you need to replicate data from a Managed Instance to an Azure SQL Database, have you considered using replication?

    https://docs.microsoft.com/en-us/azure/sql-database/replication-with-sql-database-managed-instance


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    Thursday, October 18, 2018 12:32 AM
  • thanks Alberto,

    Sorry for the delay in getting back. I tried using replication but the log reader agent fails with the following error:

    Named Pipes Provider: Could not open a connection to SQL Server [53]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Windows logins are not supported in this version of SQL Server.'.
    2018-11-19 23:34:57.766 Named Pipes Provider: Could not open a connection to SQL Server [53]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Windows logins are not supported in this version of SQL Server.

    If I add the subscriber and publisher to the same vnet, I get the below error:

    2018-11-20 02:40:35.097 Copyright (c) 2016 Microsoft Corporation
    2018-11-20 02:40:35.097 Microsoft SQL Server Replication Agent: logread
    2018-11-20 02:40:35.097
    2018-11-20 02:40:35.097 The timestamps prepended to the output lines are expressed in terms of UTC time.
    2018-11-20 02:40:35.097 User-specified agent parameter values:
       -Publisher SQLMANAGEDINSTANCE-DEV.XXXXXXX.DATABASE.WINDOWS.NET
       -PublisherDB DevPubDB
       -Distributor tcp:sqlmanagedinstance-dev.XXXXXXX.database.windows.net
       -DistributorSecurityMode 1
       -Continuous
       -LoginTimeout 150
       -XJOBID 0x569888C1A0D6984292F24CF9889BC478
       -XJOBNAME SQLMANAGEDINSTANCE-DEV.XXXXXXX.DA-DevPubDB-4
       -XSTEPID 2
       -XSUBSYSTEM LogReader
       -XSERVER SQLMANAGEDINSTANCE-DEV.XXXXXXX.DATABASE.WINDOWS.NET
       -XCMDLINE 1
       -XDB
       -XHOST np:\\.\pipe\DB.1-C8F56400E373\sql\query
       -XCancelEventHandle 0000000000001D2C
       -XParentProcessHandle 00000000000016D8
    2018-11-20 02:40:35.143 Status: 0, code: 20015, text: 'Windows logins are not supported in this version of SQL Server.'.
    2018-11-20 02:40:35.143 Windows logins are not supported in this version of SQL Server.

    Any help would be appriciated?

    Thanks,

    Aloysius



    Monday, November 19, 2018 11:45 PM
  • Hello,

    Please note you need to use SQL Server account for connectivity between replication participants.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    Tuesday, November 20, 2018 6:59 PM