CODEWITHSUNDEEP
sql-serverazureazure-sql-managed-instance
Amommy
Amommy

Reputation: 47

Copy data from Azure SQL Managed Instance DB to Azure SQL Server DB

We are needing to create a copy of all data that is on an Azure SQL Managed instance to an Azure SQL Server. The data will be updated/refreshed regularly. ideally some sort of job on a schedule would help as well but manual is fine. We just cannot find a solution to get the data from MI DB to SQL DB without manually creating a bacpac of MI data and then restoring to SQL DB every time. Any ideas? Has anyone successfully done this?

EDIT: Methods besides pipline/DF method. This is what we were looking at https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-two-instances-and-sql-server-configure-tutorial but minus SQL VM part. Cannot get it to work.

Thanks

Upvotes: 0

Views: 2085

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

You can use the Azure Data Factory pipeline to copy data from Azure SQL Managed Instance DB to Azure SQL Server DB.

  1. Create Azure data factoryresource.

  2. Create Azure SQL Managed Instance linked service for source dataset connection.

enter image description here

  1. Provide Azure subscription and SQL Managed instance details in linked service connection.
  2. You can select different types of authentication methods in the connection.

enter image description here

  1. Create Azure SQL Database linked service for Sink (destination) dataset connection.

enter image description here

  1. Provide your Azure SQL database details in the connection.

enter image description here

  1. Create a new pipeline and select Copy data activity from Activities.
  2. Create Source and sink datasets with database table details in Copy data activity and select source linked service and sink linked service respectively in Source and Sink settings.

enter image description here

You can go through these MS documents for more details on Source and Sink dataset.

Upvotes: 1

Related Questions