Sync new records in Prod to QA in Azure SQL Managed Instance

asked 2020-09-17T16:19:36.093+00:00
Kman 41 Reputation points

We have two Azure SQL Managed Instance QA and Prod they are in the same Subscription. Any new records in Prod (minus the identity column) we would like to include it into the QA as we don't want to replicate the table as the Prod has unique identity columns. We just want to append the any new data/records into the Prod (minus the identity column). Link server is not an option for us.

SQL Server on Azure Virtual Machines
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,470 questions
{count} votes

4 answers

Sort by: Most helpful
  1. answered 2020-09-17T21:47:03.917+00:00
    Erland Sommarskog 67,481 Reputation points Microsoft MVP

    I don't have access to Azure Managed Instance, but is Transactional Replication available?


  2. answered 2020-09-21T00:57:08.397+00:00
    AmeliaGu-MSFT 13,876 Reputation points

    Hi @KaisMalique-9406,

    Please refer to the following articles which might help:
    Publish data changes from Azure SQL Managed Instance using transactional replication
    Transactional replication with Azure SQL Managed Instance
    Best Regards,
    Amelia


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. answered 2020-09-23T21:22:21.35+00:00
    Erland Sommarskog 67,481 Reputation points Microsoft MVP

    If Transactional Replication is out, I guess ADF is the thing to try next. I did not suggest it of the simple reason I have very little clue about it myself. There is a tag for azure-data-factory, I think you should try there, https://learn.microsoft.com/answers/topics/azure-data-factory.html.

    No comments

  4. answered 2020-09-23T23:04:17.49+00:00
    JRStern 1 Reputation point

    This is very difficult, it is a problem everyone has, and there are no simple solutions.

    An alternative approach is to take a fresh copy of production every day and make that available for QA or other development purposes. This is reasonably quick and easy in Azure.

    If you want to go further, then you are probably better off coming up with a way to export your QA components to this fresh copy every day, than vice-versa.

    J.

    No comments